How do parameterized queries help against SQL injection?

Question

In both queries 1 and 2, the text from the textbox is inserted into the database. What's the significance of the parameterized query here?

1. Passing txtTagNumber as a query parameter

   SqlCommand cmd = new SqlCommand("INSERT INTO dbo.Cars " +"VALUES(@TagNbr);" , conn);
   cmd.Parameters.Add("@TagNbr", SqlDbType.Int);
   cmd.Parameters["@TagNbr"].Value = txtTagNumber.Text;
   

2. Converting txtTagNumber to an integer before constructing the query

   int tagnumber = txtTagNumber.Text.ToInt16(); /* EDITED */
   INSERT into Cars values(tagnumber.Text); /* then is it the same? */
   

Also, here I would use Regular Expression validation to stop insertion of illegal characters.

Answer 1

Parameterized queries do proper substitution of arguments prior to running the SQL query. It completely removes the possibility of "dirty" input changing the meaning of your query. That is, if the input contains SQL, it can't become part of what is executed because the SQL is never injected into the resulting statement.

Answer 2

Imagine a dynamic SQL query

sqlQuery='SELECT * FROM custTable WHERE User=' + Username + ' AND
Pass=' + password

so a simple sql injection would be just to put the Username in as ' OR 1=1-- This would effectively make the sql query:

sqlQuery='SELECT * FROM custTable WHERE User='' OR 1=1-- ' AND PASS='
+ password

This says select all customers where they're username is blank ('') or 1=1, which is a boolean, equating to true. Then it uses -- to comment out the rest of the query. So this will just print out all the customer table, or do whatever you want with it, if logging in, it will log in with the first user's privileges, which can often be the administrator.

Now parameterized queries do it differently, with code like:

sqlQuery='SELECT * FROM custTable WHERE User=? AND Pass=?'

parameters.add("User", username) parameters.add("Pass", password)

where username and password are variables pointing to the associated inputted username and password

Now at this point, you may be thinking, this doesn't change anything at all. Surely you could still just put into the username field something like Nobody OR 1=1'--, effectively making the query:

sqlQuery='SELECT * FROM custTable WHERE User=Nobody OR 1=1'-- AND
Pass=?'

And this would seem like a valid argument. But, you would be wrong.

The way parameterized queries work, is that the sqlQuery is sent as a query, and the database knows exactly what this query will do, and only then will it insert the username and passwords merely as values. This means they cannot effect the query, because the database already knows what the query will do. So in this case it would look for a username of "Nobody OR 1=1'--" and a blank password, which should come up false.

Source: lavamunky.com; Nov 2011

Answer 3

SQL injection happens when a possible parameter has SQL within it and the strings are not handled as it should be

eg:

var sqlquerywithoutcommand = "select * from mytable where rowname =  '" + condition+''";

and the condition is a string coming from the user in the request. If condition is malicious say eg:

var sqlquerywithoutcommand = "select * from mytable where rowname =  '" + "a' ;drop table  mytable where '1=1"+"'";

you could end up running malicious scripts.

However, using parameters the input will be cleaned of any characters which might escape string characters, which means you can be ensured no matter what comes in it will not be able to run inject scripts.

Using the command object with parameters the SQL actually executed would look like this:

select * from mytable where rowname = 'a'';drop table mytable where 1=1'''

in essence it will be looking for a row with rowname = a';drop table mytable where 1=1' and not running the remaining script.

Answer 4

Parameterized queries handles everything - why go to the trouble?

With parametrized queries, in addition to general injection, you get all the data types handled, numbers (int and float), strings (with embedded quotes), dates and times (no formatting problems or localization issues when .ToString() is not called with the invariant culture and your client moves to a machine with and unexpected date format).

Answer 5

It is quite understandable why one would feel so.

sqlQuery = "select * from users where username='+username+';"

vs

sqlQuery = "select * from users where username=@username;"

Both the above queries seem to do the same thing.But they actually don't.

The former uses input to for a query, the latter decides on the query but only substitutes the inputs as it is during the execution of the query.

To be more clear, the parameters' values are located some where on the stack where the variables' memory is stored and is used for search when needed.

So if we were to give ' OR '1'='1 as the input in username, the former would dynamically construct a new queries or queries as part of the sql query string sqlQuery which is then executed.

While on the same input, latter would search for ' OR '1'=' in the username field of the users table with the statically specified query in the query string sqlQuery

Just to consolidate it, this is how you use parameters to make query:

SqlCommand command = new SqlCommand(sqlQuery,yourSqlConnection);

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@username";
parameter.Value = "xyz";

command.Parameters.Add(parameter);

Answer 6

Parameterized queries allow the client to pass the data separately form the query text. Where on most free from text you would do validation + escaping. Of course Parameterization don't help against other kind of injection, but as the parameter are passed separately, they are not use as execution text query.

A good analogy would be the "recent" execution bit used with most of the modern processor and Operating system to protect from buffer overflow. It still allows the buffer overflow but prevent the execution of the injected data.