You may have read recently about a large number of Web servers that were compromised through a SQL injection attack. The malicious SQL payload is very well designed, somewhat database schema agnostic and generic so it could compromise as many database servers as possible. While the attack was a SQL injection attack that attacked and compromised back-end databases courtesy of vulnerable Web pages, from a user’s perspective the real attack was compromised Web pages that serve up malware to attack user’s through their browsers. In essence, there were two sets of victims: the Web site operators and the users who visited the affected Web sites. In this post, I want to focus on what the first set of users, the Web site operators, can do to protect themselves.
The fact that the malicious payload was so generic shows that the science of SQL injection has not taken a back seat to research in other vulnerability types, such as buffer overflows or cross-site scripting issues.
I think the first lesson from this attack is this:
If you have a Web server (doesn’t matter what type), and it’s hooked up to a database (doesn’t matter what type) you need to go in and review your code that performs the database work.
So now that you’ve determined the database access code, now what? The SDL is very specific about what do here, there are three requirements – they are requirements not recommendations, which means you must do the following coding requirements and defenses
- Use SQL Parameterized Queries
- Use Stored Procedures
- Use SQL Execute-only Permission
Use SQL Parameterized Queries
From the SDL documentation:
“Applications accessing a database must do so only using parameterized queries.
Creating dynamic queries using string concatenation potentially allows an attacker to execute an arbitrary query through the application. This vulnerability allows for unauthorized, interactive, logon to a SQL server which may result in the execution of malicious commands leading to the possible modification (or deletion) of Operating System or user data.
Combining the use of parameterized queries and stored procedures helps to mitigate the risk of successful exploitation of user input which is not correctly verified.”
This defense has been known about forever; heck, David and I discussed this in detail in the first edition of Writing Secure Code in 2002:
From page 320, “Another way to perform this kind of processing is to use placeholders which are often referred to as parameterized commands.”
Just about every database access technology supports parameterized queries; work out what they are for your DB technology and use them: the defense for a PHP/MySQL combo will not be the same as a C#/SQL Server combo.
The most likely cause of these recent compromises is using string concatenation to build SQL statements. Just don’t do it, even if you think you’re safe, just don’t use string concatenation to build SQL statements! There are some very specialized cases where string concatenation is valid, but they are rare, especially for Web apps. In my opinion, any use of string concatenation in a Web application is a high-priority bug.
Use Stored Procedures
From the SDL documentation:
“Applications accessing databases should do so only using stored procedures. ”
-and-
“Do not use “exec @sql” construct in your stored procedures.
Using stored procedures helps to mitigate the SQL injection threat to a great extent since type checking is available for parameters. If the attacker supplies input that does not match the type constraints the stored procedures will throw an exception. In the vast majority of the cases, this should be properly handled within the application.
However, if the stored procedures perform string manipulation in their code and then execute that query using the “exec @sql” construct incorrect handling of user input can produce the same SQL injection vulnerability as would be seen at the application layer.”
Note the words “help mitigate,” by themselves stored procedures do not remove SQL injection vulnerabilities; they just raise the bar on the attacker by hiding much of the underlying database schema from the attacker.
Use SQL Execute-only Permission
This next defense is interesting in that it is a defense in depth method; in this case it assumes the attacker has successfully found a SQL injection bug in your code. Now what? Thankfully, this defense will stop most every attack dead in its tracks.
From the SDL documentation:
“Only grant ‘execute’ permission on all stored procedures, and grant that permission only for the application domain group.
Ensure that this group is granted execute permissions only on your stored procedures. Do not grant any other permission on your database to any other user or group.”
This is a great defense, because if the attacker attempts to access any other database object other than through a stored procedure (you can use views also), the underlying database permissions model prevents the attack by denying access to the attacker.
It’s interesting that the SDL offers three SQL injection requirements; only one actually remedies the problem (secure by design) and the other two offer mores defenses assuming failure (secure by default.)
Of course, a simple set of rules is not a substitute for careful design, implementation, and test. The SDL is a holistic process that covers the software lifecycle end-to-end, so don’t mistake these simple rules as a guarantee that you will avoid SQL injection problems. You need to understand the situations in which the rules apply. You may find, for example, that string concatenation is the best – or perhaps only – solution to a particular problem and these rules may not guard against SQL injection in those situations. Follow secure development practice throughout the lifecycle of your project – including things we left out of this blog, like testing and security response, for best results.
