How do parameterized queries help against SQL injection?

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?

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;

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.

Edit Report

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.

answered 2011-03-29T06:11:13.737

How do parameterized queries help against SQL injection?

1 Answer

Your Answer