Input Validation Is Not The Answer
I just sent a piece of e-mail to my team about input validation and SQL injection and it occurred to me that I've been meaning to get into this here, too:
If you're trying to solve a SQL injection problem, input validation is NOT the answer!
There, I've said it. I keep seeing blog posts, forum posts, e-mail, etc, that say "Oh, you got hax0red by SQL injection, you should have been doing input validation". I'm sorry, but y'all are wrong, wrong, wrong, wrong. Let me copy-and-paste my e-mail to explain why:
Your customer is failing to stop SQL injection because they don’t understand the problem (or, by extension, the solution).
It sounds like the customer is trying to do input validation. What input validation does is to check input coming from an untrusted user to make sure that it doesn’t contain any blacklisted characters/phrases. Depending on the implementation, it either replaces items on the blacklist with something innocuous or it blocks the input entirely.
This is the wrong way to stop SQL injection, period. Input validation is sometimes useful as part of a defense-in-depth strategy but that’s it. There are several major problems with input validation:
- It only works for as long as you’re smarter than your attackers because you have to anticipate every potential attack.
- It doesn’t solve your real problem, which is that SQL can potentially execute something in input you get from your untrusted user.
- You can end up with a lot of false positives if you’re not careful — if you’re blocking “exec”, what happens when one of your users has the title “Executive Assistant”?
To use an analogy, using input validation to stop SQL injection is like using anti-virus software to stop malware. It might work, it might not, but you’d be far better off if you actually resolved the vulnerability instead of just trying to mitigate it.
How, then, should the customer fix their vulnerabilities? Parameterizing queries is the single best step. Instead of simply mitigating the vulnerability, this actually resolves it. At a high level, I think of parameterized queries as DEP for SQL — it separates the executable code from the data and prevents anything in the data from executing.
Bala Neeumalla wrote a great MSDN article on how to code in ASP to prevent SQL injection (https://msdn.microsoft.com/en-us/library/cc676512.aspx) that covers this in detail. His article should be considered definitive. I wrote a few blog entries (https://blogs.technet.com/neilcar/archive/2008/05/21/sql-injection-mitigation-using-parameterized-queries.aspx, https://blogs.technet.com/neilcar/archive/2008/05/23/sql-injection-mitigation-using-parameterized-queries-part-2-types-and-recordsets.aspx) that have additional examples that might be helpful to the customer.
Michael Howard also wrote a great blog entry regarding SQL injection and the SDL (https://blogs.msdn.com/sdl/archive/2008/05/15/giving-sql-injection-the-respect-it-deserves.aspx). Besides using parameterized queries, there are two additional steps that can be taken to further protect an application:
- Use properly designed stored procedures
- Use SQL Execute-only permission so that the application can only execute the stored procedures and cannot execute other statements.
This doesn't mean that input validation isn't useful and it doesn't mean it isn't appropriate mitigation in some cases. It's still not the way to prevent SQL injection.
And I'm not just talking about ASP, either. The same thing holds true for ASP.Net, the same thing holds true for Cold Fusion (look up CFQueryParam), Java, etc, etc. Wherever you query, there shall ye parameterize.
Comments
Anonymous
January 01, 2003
After writing the post SQL Injection, the threat beyond the perimeter I received some emails with thisAnonymous
January 01, 2003
Pojawił się ciekawy post: Input Validation Is Not The Answer. Jego głównym przesłaniem jest zdanie If you're trying to solve a SQL injection problem, input validation is NOT the answer!. I to jest prawda, walidacja danych wejściowych jest bardzo ist