Does calling query() on a container prevent SQL Injection?

Question

According to this link, db_query is now deprecated, and should be replaced by calling query() on a container containing a service injected with a database connection.

How do you prevent SQL Injection when using query()? The docs only cover how to do so when using db_query(), which is deprecated.

Thanks.

Answer 1

What reported there for db_query() is still valid for the method that you should use. In short, use placeholders when building the query, instead of concatenating the query string with unsafe input (e.g. the input obtained from from the user).

In Drupal 8 procedural code, you would use \Drupal::database()->query() instead of db_query(), but you would still avoid using code similar to the following one.

$count = \Drupal::database()->query('SELECT COUNT(DISTINCT foo) FROM {table} t WHERE t.name = '. $_GET['user'])->fetchField();

The correct code would be the following one.

$count = \Drupal::database()->query('SELECT COUNT(DISTINCT foo) FROM {table} t WHERE t.name = :name', [':name' => $_GET['user']])->fetchField();

Answer 2

If you take a look at the one significant line of code in db_query:

return Database::getConnection($options['target'])->query($query, $args, $options);

you can see it just proxies the arguments straight through to the connection's query method; if db_query is safe, the connection's query method is guaranteed to be safe too, because db_query($sql, $args); and $connection->query($sql, $args); are identical operations.

So all you need to do is maintain a combination of safe query string and parameters, and just pass them to the connection's query method instead. Your queries will be safe.

As an aside, db_query() only helps to prevent SQL injection if it's used properly. You can't save people from themselves. Case in point, these are perfectly valid:

db_query("SELECT * FROM foo WHERE var = '$_GET[baz]'");

Database::getConnection($options['target'])->query("SELECT * FROM foo WHERE var = '$_GET[baz]'");

but potentially deadly. Little Bobby will be on his way in no time.

Same goes for any other method. If you use parameters, and don't do crazy things like string interpolation/substitution to put user-supplied input into your SQL string, you're as safe as you can be.