As part of the series of blog posts on the top 10 OWASP web application security risks and how to defend against them in Drupal 7, here is the first post in the series. This post deals with the top security hole - classified as “injection”.
From the OWASP top 10 security risks:
Injection flaws, such as SQL, OS, and LDAP injection occur when untrusted data is sent to an interpreter as part of a command or query.
For this post I will cover what SQL injection is and how Drupal 7’s built in tools can help you avoid this.
What is SQL injection
Incorrectly filtered escape characters
This form of SQL injection occurs when the user supplied input does not have the escape characters filtered from the query. This is easily demonstrated with this SQL query example (which you should never write), imagine the query:
What happens when the query parameter ‘nid’ is:
Or even worse, imagine this is the query parameter ‘nid’:
Incorrect type handling
This occurs when user supplied input is not strongly typed. For instance you may be expecting an integer but as a developer make no effort to enforce this. The above example shows this in action as well.
Blind SQL injection
This is used when an application is vulnerable to SQL but the results are not visible to the attacker. A crafted query parameter ‘nid’ (for the above SQL query) might something like:
If you received a non-error page it would indicate that the server was running MySQL 4.
For more information see the wikipedia page on SQL injection.
Database abstraction layer
The easiest way is to use the database abstraction layer effectively. By writing your database queries in this manner not only are you defending against injection from unclean inputs, but you are also ensuring your query will execute on all supported databases (MySQL, PostgreSQL, SQLite and other contributed types e.g. Oracle). So this method is great for portability and security.
Example insert query showing the abstraction layer:
Dynamic select queries are possible by adding to the query object:
Adding tags to your queries allows you (or another module) to alter the query before executed. The best example of this is the node_access tag.
By adding that simple tag onto any SELECT query, it ensures that all returned node IDs node access restrictions placed on it. All queries that retrieve a list of nodes (or node IDs) for display to users should have this tag (this touches on a few other OWASP security risks).
Static or custom queries with db_query()
If you do need to write static and fast (no placeholders) or extremely custom (multiple complex joins, sub-queries, temporary tables) SQL you can elect for the less standardised method db_query().
N.B. this should never be the first choice for dynamic (with placeholders) database queries - db_select() should be used where possible.
Example static query that is perfect for db_query():
With db_query() and dynamic queries you need to perform any sanitisation of the query yourself, luckily there are built in methods you can take advantage of here.
Example select query showing the raw SQL:
With the above example you can see the uid parameter is escaped as it is passed in as an argument. In general if you are creating raw SQL with any form of string concatenation you are most likely doing it wrong.
Update 13 November
- I have added a section on what SQL injection is, and the common attacks
- I have made clearer that db_select() is the preferred method of querying the database over db_query()
- Removed the section on db_escape_table() as it was seen as being not helpful.
Want to read more about this topic, here are a few resources that can help you:
- Drupal security whitepaper (a little old 2010, but still worth a read)
- Static queries using db_query() in Drupal
- Dynamic queries using db_select() in Drupal
If I have missed any techniques, or you know of any modules or automated tools that may help, let me know in the comments. I am also after better ‘further reading’ material, so if there are other blog posts that deal with SQL injection let me know.