Ulf Wendel

Not only SQL injection: I don’t trust you!

| 6 Comments

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 Follow me on Twitter

6 Comments

  1. If the value should be an integer, just cast it to int. That’s easier than filtering.

  2. Well, if you don’t care about your data, then you can cast. If you care and want build on a strenght of relational databases ([optional] strict types – "valid" data), then you better go for the filter. If you look down a bit, the filter does more than a cast. It does a validation and range check. A simple cast cannot do the same.

  3. Why not just use parametrised queries?

  4. Petah,

    valid question. Parameterised questions are your second choice. The answer for now is “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.”

    That must be an unsatisfying answer! How about this. I wait for the webinar to appear and then I blog about the rest including the contents of a slide that we dropped off the webinar because of time constraints.

    More in a couple of days!

  5. I regularly use filter to (sanitize input first and then validate, if input is url, int, float, email etc) and then use PDO Prepared Statements when inserting the input into the MySQL Database. While I am interested to see your slides, I find 404 in mysql website for the webinar link. Please provide the link to webinar if it is hosted elsewhere, thank you

  6. Pingback: MySQL-Injection mal anders | David Müller: Webarchitektur

Leave a Reply

Required fields are marked *.


This site uses Akismet to reduce spam. Learn how your comment data is processed.