SQL Injection License Plate

HimanshuJoshi

:laugh: :laugh: :laugh:

"I'm willing to admit that I may not always be right, but I am never wrong." - Samuel Goldwyn

OriginalGriff

An SQL Injection attack does not require a database insert - a SELECT ... FROM query will do. Since the ";" is SQL for end-of-statement, the code after it is considered to be a new instruction and executed separately. So "SELECT * FROM tab WHERE number = 123;DROP TABLES tab" would perform two separate operations: Query the table, then delete it. This is why parameterised queries are so important!

You should never use standby on an elephant. It always crashes when you lift the ears. - Mark Wallace C/C++ (I dont see a huge difference between them, and the 'benefits' of C++ are questionable, who needs inheritance when you have copy and paste) - fat_boy

Lost User

Perhaps not the camera itself, but the image is at some point processed with some kind of OCR and then probably the result is inserted into a database.

A while ago he asked me what he should have printed on my business cards. I said 'Wizard'. I read books which nobody else understand. Then I do something which nobody understands. After that the computer does something which nobody understands. When asked, I say things about the results which nobody understand. But everybody expects miracles from me on a regular basis. Looks to me like the classical definition of a wizard.

LucianPopescu

OriginalGriff wrote:

This is why parameterised queries are so important!

By parameterized, you mean stored procedures or what ?

OriginalGriff

pLucian wrote:

By parameterized, you mean stored procedures or what ?

No! :laugh: Instead of the SQL

SqlCommand cmd = SqlCommand("SELECT * FROM tab WHERE name = " + tbName.Text);

Use

SqlCommand cmd = SqlCommand("SELECT * FROM tab WHERE name = @NAME");
cmd.AddWithValue("@NAME", tbName.Text);

This way the content of tbName can be any characters without it being accepted as a new SQL command. (Yes, yes, I know - "don't use SELECT * FROM" because it... yada yada yada! Don't be so picky!)

You should never use standby on an elephant. It always crashes when you lift the ears. - Mark Wallace C/C++ (I dont see a huge difference between them, and the 'benefits' of C++ are questionable, who needs inheritance when you have copy and paste) - fat_boy

dan sh

I don't think this would harm the database. Do they store the entire image or extract just the number and then store it? Even if they store the number, there has to be some sort of encryption happening before insert.

LucianPopescu

very nice, i didn't knew about this ;) good to know.

Corporal Agarn

OriginalGriff wrote:

(Yes, yes, I know - "don't use SELECT * FROM" because it... yada yada yada! Don't be so picky!)

:laugh: :laugh: :laugh:

Gareth H

It wouldnt work in the UK, since they pay someone to look through every image and verify that its correct. But it would be nice if it did...

Regards, Gareth. (FKA gareth111)

Electron Shepherd

OriginalGriff wrote:

This is why parameterised queries are so important!

Or just escape out the string. A big limitation with parameterised queries is they are rather tricky to use with "IN" clauses.

Server and Network Monitoring

AspDotNetDev

private SqlDataReader GetSomeStuff(SqlConnection conn, List<string> vals)
{

// Create query.
StringBuilder sb = new StringBuilder();
for (int i = 0; i < vals.Count; i++)
{
    if (i != 0) sb.Append(", ");
    sb.Append("@param" + i.ToString());
}
string query = string.Format("SELECT \* FROM TheTable WHERE TheColumn IN ({0})", sb.ToString());


// Add each parameter to command.
SqlCommand cmd = new SqlCommand(query, conn);
for(int i = 0; i < vals.Count; i++)
{
    cmd.Parameters.Add(new SqlParameter("param" + i.ToString(), vals\[i\]));
}


// Execute.
return cmd.ExecuteReader();

}

Now, was that so hard? Though, with SQL Server 2008 (and I think 2005), entire tables can be passed as parameters, which removes the need to dynamically construct the query. The above technique won't work very well with stored procedures though. In cases like that, one can create an intermediate table to bulk insert data into, generate a unique batch ID from that, then pass that ID to the stored procedure so it knows which records in the batch table to use (after which, it can delete those records).

[Forum Guidelines]

Electron Shepherd

aspdotnetdev wrote:

Now, was that so hard?

Err, yes. You quote a simple example (consider the extra logic to handle the case where there are additional parameters to be added), when all you needed to do was double up any single quotes in each parameter, and put single quotes at either side of it - a very simple string manipulation operation.

aspdotnetdev wrote:

one can create an intermediate table to bulk insert data into, generate a unique batch ID from that, then pass that ID to the stored procedure

Sounds very efficient, easy to debug and highly portable...

Server and Network Monitoring

AspDotNetDev

Electron Shepherd wrote:

all you needed to do was double up any single quotes in each parameter

How about for NULL (no quotes, though that wouldn't be much use in the specific case of an IN operator)? And for date times ("3/6/2010" is March 6th or June 3rd, and that doesn't even take into account millisecond precision)? And for numbers (no quoting necessary)? And when C# uses scientific notation to represent numbers but SQL Server does not ("100000000.0" vs "1.0E8")? Or when a different culture processes certain data types (e.g., date times) differently? Not so simple after all, now is it? And I think it wasn't very hard to create a list of strings in the format "@param0, @param1, ..., @paramN". As far as bulk inserts... that is quite efficient. But different strokes for different folks.

[Forum Guidelines]