SQL Injection Prevention
The team over at Dark Reading was kind enough to invite me to blog on their Database Security portal. This week I started a mini-series on threat detection and prevention by leveraging native database features. This week’s post is on using stored procedures to combat SQL injection attacks. But those posts are fairly short and written for a different audience. Here, I will be cross-posting additional points and advanced content I left out of those articles. My goal was to demystify how stored procedures can help combat SQL injection. There are other options to detect and block SQL injection attacks, many of which have been in use with limited success for some time now. What can you do about SQL injection? You can patch your database to block known threats. You can buy firewalls to try to intercept these rogue statements, but the application and general network firewalls have shown only limited effectiveness. You need to have a very clear signature for the threat, as well as a written a policy that does not break your application. Many Database Activity Monitoring vendors can block queries before they arrive. Early DAM versions detected SQL injection based on exact pattern matching that was easy for attackers to avoid, back when DAM policy management could not accommodate business policy issues; this resulted in too many false negatives, too many false positives, and deadlocked applications. These platforms are now much better at policy management and enforcement. There are memory scanners to examine statement execution and parameters, as well as lexical and content analyzers to detect and block (with fair success). Some employ a hybrid approach, with assessment to detect known vulnerabilities, and database/application monitoring to provide ‘virtual patching’ as a complement. I have witnessed many presentations at conferences during the last two years demonstrating how a SQL injection attack works. Many vendors have also posted examples on their web sites and show how easy it is to compromise and unsecured database with SQL injection. At the end of the session, “how to fix” is left dangling. “Buy our product and we will fix this problem for you” is often their implication. That may be true or false, but you do not necessarily need a product to do this, and a bolt-on product is not always the best way. Most are reactive and not 100% effective. As an application developer and database designer, I always took SQL injection attacks personally. The only reason the SQL injection attack succeeded was a flaw in my code, and probably a bad one. The applications I produced in the late 90s and early 2000s were immune to this form of attack (unless someone snuck an ad-hoc query into the code somewhere without validating the inputs) because of stored procedures. Some of you might say note this was really before SQL injection was fashionable, but as part of my testing efforts, I adopted early forms of fuzzing scripts to do range testing and try everything possible to get the stored procedures to crash. Binary inputs and obtuse ‘where’ clauses were two such variations. I used to write a lot of code in stored procedures and packages. And I used to curse and swear a lot as packages (Oracle’s version, anyway) are syntactically challenging. Demanding. Downright rigorous in enforcing data type requirements, making it very difficult to transition data to and from Java applications. But it was worth it. Stored procedures are incredibly effective at stopping SQL injection, but they can be a pain in the ass for more complex objects. But from the programmer and DBA perspectives, they are incredibly effective for controlling the behavior of queries in your database. And if you have ever had a junior programmer put a three-table cartesian product select statement into a production database, you understand why having only certified queries stored in your database as part of quality control is a very good thing (you don’t need a botnet to DDoS a database, just an exuberant young programmer writing the query to end all queries). And don’t get me started on the performance gains stored procedures offer, or this would be a five-page post … If you like waiting around for your next SQL injection 0-day patch, keep doing what you have been doing. Share:
