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?
4 Solutions collect form web for “How does SQLParameter prevent SQL Injection?”
Basically, when you perform a
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 (
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=?'
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.
“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.”
When using parameterized queries, the attack surface is reduced to monkeying around with the parameters.
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)