Ensight – Jeremy Wright

Security wiseman Anil John summarizes, links to and then adds to a great article on protecting against SQL Injection attacks.

You prevent SQL Injection using the following tactics:

* Constrain the input by validating it for type, length, format and range. Remember, ALL INPUT IS EVIL, until proved otherwise!

* Use type safe SQL parameters. The parameter collection in SQL provides type checking and length validation. So if you use the Parameters collection, input is treated as a literal value and SQL does not treat it as executable code. Another point is that the Parameters collection can be used to enforce type and length checks so that values outside of the range trigger exceptions. You can use the Parameters collection with both sprocs as well as dynamic SQL.

* Use filter routines that sanitize the code by adding escape characters to characters that have special meaning to SQL. An example would be adding an escape character to the single apostrophe character. Keep in mind that these type of filter routines can be bypassed by an attacker that uses ASCII hex characters. So they should be used as just another part of your defense in depth strategy.