I want to be sure I am using good techniques for protecting myself from SQL attacks. I have read several documents on the topic (SQL injection attacks), but am confused mostly as to what protection stored procedures are giving me. I am using SQL Server. Almost all my database access is done through stored procedures, but I am confused whether I still need to be concerned with filtering things like apostrophes and double dashes and other characters that can be used to hack into my db.

What is a good (enough) protection scheme?

-------------------------
Beware of programmers with screwdrivers...

__________________
-------------------------
Beware of programmers with screwdrivers...

Stored procedures generally provide an extra layer of defense.

If you use SQL text generated by your code, then you make it easy for an injection attack. For example, if you have code like:
and then execute the string, an attacker who fills in the textbox with the string "ProductID; DELETE Products;" will wreak havoc. Using a stored procedure presumably would assign the textbox value to a parameter. The stored procedure code would then be:
and the same text will simply result in an error rather than sending you scrambling for a backup.

While this is a measure of defense, the best is scrupulous editing of the input data. If special characters don't belong in your database, then they don't belong in the input to it. Remove them by filtering such junk out.

"good (enough)" is a relative term...



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

If I use a stored procedure like you say, and the @parameter I pass to it has a value of "ProductID; DELETE Products;"...would that not have the same result as the text generated attack? Would the sp not execute SELECT * FROM Products WHERE ProductID = ProductID; DELETE Products; ???

Unfortunately I need to allow most special characters. I thought of using a scheme whereby I replace any special characters with an HTML code equivalent. For example, an "=" sign would be replaced with "#61;"...then when I need to read that text back I reverse the process. This works OK, but I'm just not sure if this is overkill...and this has also caused problems with simple data binding...



-------------------------
Beware of programmers with screwdrivers...

No, because you have defined a datatype for the parameter (presumably varchar() or some such). Parameterized queries are essentially a value substitution, so the code executed would be:
and the value of the parameter would be the string 'ProductID; DELETE Products;' which most likely won't match anything. If the parameter was defined as say an INTEGER, then the WHERE clause would fail with a type conversion error.

But it would be better for that sort of error to occur on the client, hence the idea of editing the data before you ship it off to the database.

My humble opinion is that is overkill. Depends on your environment and your level of paranoia, I guess. Presumably your data follows some sort of business rules - that is it's not totally free form. Given that, apply those rules to data that a user inputs (or that you obtain from anywhere "outside"), use parameterized queries, and you should be OK.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks Jeff! That helps immensely!

-------------------------
Beware of programmers with screwdrivers...