Tech Insight: SQL Injection Demystified

The purpose behind the first two defensive approaches is to stop using dynamic SQL queries that accept user input. Instead, developers use prepared SQL statements that take the user's input as parameters, preventing an attacker from modifying the functionality of the SQL statement. The difference between parametrized queries and stored procedures is that the first is part of the application, while the latter lives in the database and is called by the application.

The third primary defense is to escape all user input, and according to the OWASP guide, this approach can be implemented within existing applications "with almost no effect on the structure of the code." In other words, if rewriting existing SQL statements as prepared statements or stored procedures is scary to your developers, escaping user input is the easy way out while still providing protection.

As added layers of defense that keep with good secure application coding practices, least privilege access should be enforced and all user input should be validated using a whitelist of valid input. For example, if the application only needs to read data from the database and only looks at fields containing numbers 1 through 9, then the database user account used by the application should only have read access to the tables necessary for the application to function. User input should be filtered to only allow numbers 1 through 9. While that sounds like common sense, it's a often common mistake by application developers.

Meanwhile, when you investigate a SQL injection attack, the most important thing is to have logging in place for the Web, application, and database servers. Having logs that show the attack request can help you understand exactly what the attacker was doing, while logs from the database server can indicate whether or not the attack was successful.

And having someone available who knows the application inside and out is very important. They can answer questions about what would happen if this query were successful, and whether or not the account used by the database would have privileges to do whatever the attacker was attempting. This is where having a good relationship between the security team and the developers is extremely important.

It is an easy vulnerability to exploit and tools exist that make it easy enough that practically anyone can do it, but with a little effort, companies can prevent the attacks if they follow the basic defense options recommended by OWASP.

Have a comment on this story? Please click "Discuss" below. If you'd like to contact Dark Reading's editors directly, send us a message.