2

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.

4

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();
| improve this answer | |
  • 1
    Yeah, probably would avoid using $_GET in general without some additional checks or protection from input, even though this is just an example. – Kevin Jul 26 '17 at 17:53
  • Indeed there are other considerations to do, apart SQL injection. For example, the code should check the users are not getting information they should not get, such as the email used from other users. That is a matter of checking the permissions the logged-in user has, which is different from preventing SQL injection. – kiamlaluno Jul 26 '17 at 18:05
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.