Prevent SQL injection

Question

One of the security principles is sanitize string and variables passed from client to server; SQL injection is one of the way to hack a website. in plain PHP there are some functions to prevent SQL injection:

• mysql_real_escape_string()
• stripslashes()

What is Drupal 8 strategy to prevent SQL injection? How can I clean up client data post in my Controller to protect my site from SQL injection?

Answer 1

Drupal 8 (like Drupal 7) uses PDO to communicate with databases.

With PDO, the DB API makes use of prepared statements, specifically to avoid SQL injection attacks.

Practically, this means that you should use the database API methods. If you use the select, update, delete methods directly, you have a better chance of avoiding SQL injection as everything will be parameterised.

If you use query, you need to be more careful, as the API can only protect you so much. For example, if you do this:

$sql = "SELECT uid FROM users WHERE name = '$_GET[name]'";
\Drupal::database->query($sql);

Then you deserve what you get :) You should always use this instead:

$sql = "SELECT uid FROM users WHERE name = :name";
\Drupal::database->query($sql, [':name' => $_GET['name']]);

Aside: In reality, you'll only see the 2 functions you mentioned being used in very old, practically obsolete code these days. The MySQL extension has been removed now, and stripslashes was traditionally used with magic quotes, which were deprecated in PHP 5.3. SQL injection is still very much a problem, but those 2 perhaps aren't the best to use for comparison in this day and age.