Khaled Afiouni (po**@afiouni.com) writes:
I am trying to find an ultimate solution to the SQL injection issues.
In addition to verifying, validating and checking on the data entry
fields, I would like to prevent compound statements from running and
only allowing the first SQL statement to run.
To do that you would have to add some middleware and have all your
clients talk to that middleware, and this middleware would pass the
code to SQL Server after validation and then pass the data back.
Not for the faint of heart. And it would be a reduction in usability,
since there sometimes be very good reason for an application to submit
two commands one go.
And you would not even be safe. You could intercept dynamic SQL created
client side, but not dynamic SQL created in stored procedures.
First step, is to let the users run the application with as few permissions
as possible. Ideally, all access should be through stored procedures, and
there should not be any dynamic SQL in the SPs as well. The users only
needs EXEC permission to the procedures. Now, this may hamper usability,
since some functions are easier to implement with dynamic SQL, not the
least if you want performance. (Typically this is search functions where
the users can search on a number of criterias.) But if you restrict
access to SELECT on the table, an intruder cannot wreck your database.
Next step is to write the SQL code properly. If you are constructing
SQL code client-side, use prepared statements with placeholds for
the parameters. Never build the entire string with values and all.
You can also call sp_executesql directly through RPC methods, *not*
as EXEC statements!
If you use dynamic SQL in stored procedures, use sp_executesql to run
your dynamic SQL, not EXEC().
For dynamic SQL on the client side, I have some articles on my web site:
http://www.sommarskog.se/dynamic_sql.html http://www.sommarskog.se/dyn-search.html
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
