Parameterized queries not subject to Sql injection?

mhildner

Active member
Joined
Dec 24, 2003
Messages
29
Location
Albuquerque, NM
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
 
I think he was assuming that the code module you use to create the query checks each parameter to make sure it is valid.
 
Hi,
Ill make a quick try...
SQL injection is when you insert a string that completely changes the sql statement.
Consider...
Code:
sSQL="Select * from Employee where manager=" & sYourID & ""
Normally sYourID would contain your employee ID (say... ab123) and in a form or webpage youre supposed to enter it to see who youre 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.

Its 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 thats 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 wouldnt 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). Its a lot safer but you cant see the actual SQL anywhere in your IDE or when you print the SQL

HTH
/Kejpa
 
Thanks for the replies. I can see via MS Sql Servers profiler tool the actual sql that gets sent to the db using paramaterized queries or not. Im probably just ignorant, but I still dont 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 cant the hacker tack on a semi-colon and execute more sql? Or change the contents of your original sql? Like I said, I just dont get it.
 
The problem with injection isnt that they are tacking on extra code; they are convincing you to do this for them

Imagine the following code
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
Code:
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
Code:
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 its 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.
 
Kejpa, why would a parameter value add escapes to single quotes? Where did you get this information from?
 
Diesel said:
Kejpa, why would a parameter value add escapes to single quotes? Where did you get this information from?
Im 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 havent looked at the final resulting statement when using parameters so Im not sure how its handled but for my imagination escape charactes work ;)

/Kejpa
 
Back
Top