With the recent SQL injection attacks on ASP pages. A lot of our readers are scrambling to find fixes for their applications. ASP is an older generation Web scripting language would require a bit more work to prevent SQL injection from happening. One of our reader Brian Erman has written a function to filter out the SQL keywords and also escape some the metacharacters in SQL to prevent SQL injection. from happening.
I have been asked a few times recently just how safe is escaping data before passing to SQL server. The answer is safe but it's not fool-proof, some of the issues have been documented by Chris Anley in his Advanced SQL Injection In SQL Server Application paper. Essentially, escaping input is making bad input less bad, so it's not ideal.
To stop SQL injection at the root, we have to understand that SQL injection happens because the database cannot effectively distinguish between static portion of the SQL statement and the user input. If there is a way we can tell the database - this is static SQL statement and this is user input, SQL injection could be stopped easily.
In actual fact, such mechanism exists, it is called parameterized query. The user input are passed to the SQL server as an argument (sort of like calling a function in programming language), the SQL server during query execution have a way to identify what part of the statement is static control, and which part is user input.
Parameterized queries have been widely publicized, examples are here and here. In classic ASP, parameterized query is possible if you use ADO command object, an example is here. Parameterized query is available on most other web scripting platforms, now is the time to review all your web app before the automated SQL injection exploitation spreads to other language platforms (PHP, CFM, PL)
Want to learn more about SQL injection mitigations? At SANSFIRE,, we will debut our new class, SEC522 "Defending Web Applications". Its an updated version of SEC519 ("Web Application Security") and now covers web services and other new topics.
Jun 23rd 2008
1 decade ago