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.

Solution 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 becase the SQL is never injected into the resulting statement.

Solution 2

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.

but using parameters the input will be cleaned of any characters which might escape string characters...

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 essense it will be looking for a row with rowname = a';drop table mytable where 1=1' and not running the remaining script

Solution 3

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).

Solution 4

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.

Solution 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);

View more solutions