RE: SQL Injection

["Imperva Application Defense Center" <adc () imperva com>]

Hi,

> What if their name was O'Henry? Security must be paramount to 
> the developer, but invisible to the client. Best choice: 
> parameterized queries. Second best: have a stored procedure 
> make the modification.
> Third: filter IN good characters. Forth: filter OUT bad characters.

Agreed (with one reservation about the stored procedures - I'll explain
below).

If you go back to the original reference in the question to our Evasion
Techniques paper, you will see that trying to predict how an SQL
injection attack looks like can be very very risky. I do agree that if
you decide to disallow all special characters, and allow only
alphanumerical characters, then the solution is safe. However, it is a
big functionality issue, and usually requires being developed per field,
as some fields will HAVE to allow some strings (such as St. John's
Street, which required both a quote and a period).

Using parameterized queries or prepared statements is the easiest way to
take all that off your head. It's spending, one time, a few hours on
learning how to use this, and changing your habits so that all SQL
queries will be done like that and not with string queries. And once you
are done with this, SQL Injection is simply no longer an issue.

About Stored Procedures - using stored procedures CAN be safe and can be
very dangerous. Many people call stored procedure as part of a string
query:

        Sqlstr = "exec sp_mysecurelogin (" & Username & "," & Password &
");"

If I can inject characters in the password, I can naturally terminate
the call for the SP myself, and start doing other things (like ;
shutdown -- ). Moreover, I have seen programmers who use stored
procedures, and do string concatenation inside the stored procedure
itself. 
So the call to the SP is safe, but then inside it, there is the same old
problem where you can put OR 1=1 (or UNION SELECT in other places...)

I have therefore stopped recommending my customers to use stored
procedure - they can be badly implemented as well. Using prepared
statements, however, proves itself as the right solution.

---
Ofer Maor
Application Defense Center Manager
Imperva(tm) Inc.
http://www.imperva.com/adc/