Parameterized queries not subject to Sql injection?

[mhildner]

I recently read this

A good Dynamic SQL engine creates parametrized queries ... they are also not open for SQL injection attacks due to the parameters

in this article.

 

Can anyone shed some light on why parameterized queries are not subject to Sql injection?

 

Thanks,

Mike

[Diesel]

I think he was assuming that the code module you use to create the query checks each parameter to make sure it is valid.

[kejpa]

Hi,

I'll make a quick try...

SQL injection is when you insert a string that completely changes the sql statement.

Consider...

sSQL="Select * from Employee where manager='" & sYourID & "'"

Normally sYourID would contain your employee ID (say... ab123) and in a form or webpage you're supposed to enter it to see who you're supposed to manage (when logging in or somehow else)

Now, if you change the variable sYourID to contain

ab123' or 'a'='a

the SQL statement suddenly becomes

Select * from Employee where manager='ab123' or 'a'='a'

which returns all rows since 'a' always equals 'a'.

 

It's even more interesting to use db specific functions in what you inject....

If you could have sYourID to contain

ab123' or drop table Login or 'a'='a

the table Login might get dropped. And that's not too fun.

 

If you use parametrized queries you assign the string to a variable that encapsulates (and escapes) the string into one string which means that the first injection statement would get parsed as

Select * from Employee where manager='ab123\' or \'a\'=\'a'

(assuming \ is the escape characted) the databse would then look for that manager ID and it wouldn't be found and no rows would be returned.

 

Soooo, the bottom line.

If you have direct user interaction with your SQL, use parameters (or check terribly THOROUGHLY). It's a lot safer but you can't see the actual SQL anywhere in your IDE or when you print the SQL

 

HTH

/Kejpa

[mhildner]

Thanks for the replies. I can see via MS Sql Server's profiler tool the actual sql that gets sent to the db using paramaterized queries or not. I'm probably just ignorant, but I still don't get it.

 

I mean, I understand Sql injection to be something like your app sends "SELECT * FROM MyTable", someone tacks on ";DROP DATABASE MASTER" or whatever. I see that paramaterized queries end up as "EXEC ...@param1...@param1='blah'" (not checking syntax here, but you get the idea).

 

So why can't the hacker tack on a semi-colon and execute more sql? Or change the contents of your original sql? Like I said, I just don't get it.

[PlausiblyDamp]

The problem with injection isn't that they are tacking on extra code; they are convincing you to do this for them

 

Imagine the following code

Dim s as string = "SELECT * FROM Customers WHERE CustomerID = '" & TextBox1.Text & "'"

and the user enters 'abcd' into the textbox the resultant SQL looks like

SELECT * FROM Customers WHERE CustomerID = 'abcd'

and is safe (if bad because of the *) code.

Now imagine the scenario where the user enters a value similar to the following in the textbox

xxxxx' DELETE Customers --

The new SQL is

SELECT * FROM Customers WHERE CustomerID = 'xxxxx' DELETE Customers --'

This is now a select followed by an inefficient delete (dangerous and performance hurting all in one!). That is the main problem with just concatenating strings together - the server has no idea what your real intention is and just parses the string as if it had been entered into Query Analyzer or similar; sees two separate sql statements and runs both....

 

If you use parameters you are firstly stating your intention that the string is a parameter to piece of code and not a piece of code in it's own right, and secondly you are able to provide further information in how the data should be handled (data type, size etc) - which can be a godsend when working with things like dates.

 

Without wishing to get into the whole stored proc or not religious war I personally prefer using stored procs, but would always use parameterised queries over string concatenation.

[Diesel]

Kejpa, why would a parameter value add escapes to single quotes? Where did you get this information from?

[Diesel]

hildner, you have to catch the Sql before it hits the server.

[mhildner]

Cool, now that makes sense. Explanation is much appreciated, thanks PlausiblyDamp.

[kejpa]

Kejpa' date=' why would a parameter value add escapes to single quotes? Where did you get this information from?[/quote']

I'm not sure that it actually adds escapes but somehow it has to do something like it in order to get parsed. You can have any characters in a parameter value including start/end string characters.

I haven't looked at the final resulting statement when using parameters so I'm not sure how it's handled but for my imagination escape charactes work ;)

 

/Kejpa

[Diesel]

Computers don't have an imagination.