Never trust user input! Injection is a threat . You are the new web developer, aren’t you?. Never trust user input is the first rule I had to learn as a web developer in anchient times. Injection can happen whenever user input is interpreted or used to compose new data. A quick recap of the #3 mistake from todays Top 10 MySQL Tips and Mistakes for PHP Developers web presentation. A webinar recording should be available in a couple of days.

Don’t!

Your probation period as a PHP developer may come to an immediate end if you write code like this:

mysqli_query(
  $link, "SELECT actor, rating FROM movies WHERE name = '" .
  $_GET['movie_name'] . "'"
);

mysql> SELECT actor, rating FROM movies 
    WHERE name = '
      Self-made Sauerkraut' UNION ALL 
           SELECT user, password FROM mysql.user WHERE '' = '';
+--------+--------+
| actor  | rating |
+--------+--------+
| Andrey | 8      |
| root   |        |
+--------+--------+
2 rows in set (0,01 sec)

It does not take an expert to exploit a web site running such code. Any search engine of your trust will list numerous tools to try SQL injection attacks against web sites. Those tools are not evil. They help you finding security issues. To keep your job, make yourself familiar with them. However, its even better to learn how to avoid injections in the first place.

Half of one way… still: don’t!

The above attack becomes more difficult when escaping input values before composing a SQL string dynamically out of the input. The input value of Self-made Sauerkraut' UNION ALL SELECT user, password FROM mysql.user WHERE '' = ' is escaped, meaning ' (and other characters) are replaced with \'. Injection fails.

mysqli_query(
  $link, "SELECT actor, rating FROM movies WHERE name = '" .
  mysqli_real_escape_string($_GET['movie_name'])  .  "'"
);

mysql> SELECT actor, rating FROM movies 
  WHERE name = '
   Self-made Sauerkraut\' UNION ALL 
   SELECT user, password FROM mysql.user WHERE \'\' = \'';
Empty set (0,00 sec)

Half way only thus far…: denial of service attack

Do not blindly escape everything and believe its safe. Injection is still possible like so. Escaping certain characters does not help if the input is used as a numeric constant in a SQL WHERE clause.

mysqli_query(
  $link, "SELECT name, rating FROM movies WHERE rating > " .
  mysqli_real_escape_string($_GET['rating'])
);

mysql> SELECT name, rating FROM movies WHERE rating > 3 AND 0 = IF(1, BENCHMARK(1000000, MD5(REPEAT(CONVERT(1, CHAR), 1000))), 0);
+----------------------+--------+
| name                 | rating |
+----------------------+--------+
| The north sea        |     10 |
| Self-made Sauerkraut |      8 |
| Bavarbike            |     10 |
+----------------------+--------+
3 rows in set (18,38 sec)

One way: do filter and escape

One way to keep your job and to save your company a fortune is to combine filtering and escaping: never trust and user input. Filter it, validate it, sanitize it before you use it. PHP 5.2 and newer comes with a handy set of built-in filter and validation functions. Use them.

$rating = filter_var($_GET['rating'], FILTER_VALIDATE_INT);
if (false === $rating) {
   die("Are you fooling me?");
}

$options = array(
  'options' => array(
     'min_range' => 0,
     'max_range' => 100,
     'default' => 5,
));
$rating = filter_var($_GET['rating'], FILTER_VALIDATE_INT, $options);
if (false === $rating) {
   die("Are you fooling me?");
}

Sorry, this is just a teaser…

… the rest is in the webinar. If you really want to keep your job, then this is not everything you need to know. At some point someone may say "use PDO – it is safe by default" to you. After listening to the webinar recording (available in a few days) you will be able to explain why this is a bit too much as a general statement.

BTW, don’t let your boss fool you with the question whether there is SQL injection with NoSQL stores. Just because something is named NoSQL does not mean injection as such is not possible…

Happy hacking!

@Ulf_Wendel