How does SQLParameter prevent SQL Injection?

What exactly is going on in the background that makes it so SQLParameter prevents SQL Inection attacks in a .NET Parameterized query? Is it just stripping out any suspect characters or is there something more to it?

Has anyone out there checked to see what actually gets to SQL Server when you pass malicious input?

Related: Can you use a SQLParameter in the SQL FROM statement?

Basically, when you perform a SQLCommand using SQLParameters, the parameters are never inserted directly into the statement. Instead, a system stored procedure called sp_executesql is called and given the SQL string and the array of parameters.

When used as such, the parameters are isolated and treated as data, instead of having to be parsed out of the statement (and thus possibly changing it), so what the parameters contain can never be "executed". You'll just get a big fat error that the parameter value is invalid in some way.

A easier-to-understand, and a more general answer goes like this:

Imagine a dynamic SQL query:

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

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 their username is blank ('') or 1=1, which is a boolean, equating to true. It then uses -- to comment out the rest of the query. So this will print out the entire customer table, or enable you to do whatever you want with it.

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 inputed username and password.

Now at this point, you may think, 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 SQL query 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 affect 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.

This isn't a complete solution though, and input validation will still need to be done, since this won't affect other problems, such as xss attacks, as you could still put javascript into the database. Then if this is read out onto a page, it would display it as normal javascript, depending on any output validation. So really the best thing to do is still use input validation, but using parameterized queries or stored procedures to stop any SQL attacks.

Source: http://www.lavamunky.com/2011/11/why-parameterized-queries-stop-sql.html

"Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth."

http://msdn.microsoft.com/en-us/library/ff648339.aspx

When using parameterized queries, the attack surface is reduced to monkeying around with the parameters.

Do use SqlParameters, but don't forget about overflow, underflow and unvalidated parameters. For example, if the method is "proc buy_book (@price money)", a malicious attacker would attempt to trick the application to running with @price set to 0.01, or attempting to get the application to do something interesting by submitting something that causes an overflow. Sql Overflows tend not to be interesting (i.e. they just cause exceptions, you are unlikely to be able to write to adjacent memory)