||SQL Server Tips by Gama and Naughter
SQL injection attacks are very dangerous and
need not only to be fully contained but also to be logged. A few
basic rules for containing the attacks:
1. Replace each single quote with two single
2. If the input reached the maximum length
of the field, check for a single quote on the right, if it exists,
then remove all the single quotes on the right side.
3. Removing TSQL comments -- and /* */ is
not a good practice because it might result in data corruption.
These should be detected and logged, though.
4. Detecting TSQL keywords such as UNION
SELECT, SHUTDOWN, etc… and logging the input data and user info is
5. Having validation in the client side is
ok but all the input must be validated again on the server side.
6. Using more elaborate SQL constructs might
help in some cases. For example using IF EXISTS before the SELECT
statement could cause an error that would prevent the injected code
to execute. This is recommended when the extra overhead is
7. Checking the number of records in the
working recordset might detect some attacks. For example, when
getting the user name from the log name and password, there should
be zero records if it does not match or one record if it does. Any
other number could very well be an attack that caused the SELECT
statement to return all records instead one just one.
8. Bad policies are more dangerous than any
attacker because they are always there, like a time bomb waiting for
a party crasher to play with it. A simple rule of thumb is: if the
input will not modify any data (like the user authentication or the
Northwind query examples) then the login should have read only
permissions; if data will be modified then it should have write
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter