How do parameterized queries help against SQL injection?

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.

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

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.

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