This is second part of the blog series about the Drupal 8 security features. We already covered Cross-site scripting and Drupal community’s general approach to security. I strongly recommend you to read the first part if you didn’t do that yet.
SQL injection is a vulnerability that allows attackers to inject custom SQL queries into the entry field (on a form or anything similar). If this kind of input is not correctly escaped the query might end up being executed on the underlying database. Something that we definitely don’t want to happen!
Drupal comes with the database API, which provides solid protection against SQL injection attacks when used correctly. It uses the parameterized queries approach where any attributes that are used with the database query are never concatenated directly to it. Instead they are sent separately to the API where they are correctly escaped before the query string is built.
Database API is just the first level of abstraction in Drupal 8. There is also Entity API with its Entity query API, which is another layer on top of the database API.
As we usually don’t need to define any custom database schemas in Drupal 8 (everything uses entities in Drupal 8 era) we also usually don’t need to use the Database API directly. Most interaction with the database happens through the Entity API, which essentially provides two levels of protection.
We run a web development shop that is very successful in building great Drupal-based websites for the clients. But success comes with downsides too. The owner of the competing web agency in the same town is extremely envious and it comes up with the evil plan. He will hack our website to make it essentially unavailable, which will ruin our reputation for good.
There is a blog section on our site that has commenting enabled. He plans to use SQL injection on the comment form to delete all nodes from our database. Here is what he does:
Let’s explain what is going on here. Imagine the insert query behind the comment for. It could look something like this
INSERT INTO comment_field_data (subject) VALUES ('Harmless comment');
I agree… this is overly simplified. But it will work to explain Joe’s bad intentions. And you can actually run this query on a Drupal 8 database and it will actually execute and save the row. Now let’s see what kind of query we end up if the user-input wouldn’t be correctly escaped:
INSERT INTO comment_field_data (subject) VALUES (''; DROP TABLE node; --');
We’re facing two queries at this point. First query ends after the first semicolon and has syntax errors, which will cause it to fail. But look at the second one. It is perfectly valid and assuming the user Drupal is using to connect to the database has permissions to drop a table it will execute. Don’t believe me? You can try to run it, but please don’t do it on a production website!
Drupal 8 is obviously smarter than Joe. It escapes the comment subject which prevents the evil plan from succeeding. The result is, again, a bit ugly, but our node table survived.
Drupal: 1, Hackers: 0
Can you see where this is going?....