MySQL

Don’t Quote Your Numbers

It’s a fairly simple rule, and something that should be obeyed for your health and sanity.

There are a couple of bugs which you could run into, when quoting large numbers. First of all, Bug #34384. This is concerning quoting large INTs in the WHERE condition of an UPDATE or DELETE. It seems that this will cause a table scan, which is going to be slooooow on big tables.

Similarly, there is the more recently discovered Bug #43319. You can run into this if you quote large INTs in the IN clause of a SELECT … WHERE. For example:

mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM a WHERE a IN('9999999999999999999999', '9999999999999999999999')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 655360
        Extra: Using where
1 row in set (0.00 sec)

Note that you only run into it when you quote multiple large numbers.

Anyway, the long and the short of this post is: if at all possible, don’t quote numbers. MySQL will love you for it.

Standard

6 thoughts on “Don’t Quote Your Numbers

  1. strcmp says:

    i.e. supply all data types explicitly — which is just a nuicance — or don’t use parameterized queries with DBD::mysql? cruel world.

  2. Really, does this matter? According to the bug reports it only occurs when you cite a nonsense value.

    I can see some opportunities for DOS’ing, but if you are building SQL via concatenation, simply blindly quoting and escaping reduces the chances of injection. That’s not to say people shouldn’t use something other than that, but I see that technique as a sound way to mitigate the risk.

  3. Alejandro Moreno says:

    the bug can be (will be) repeared. Don’t using ‘ in your numbers, or in general in your querys means that you are vulnerable to DOS attacks, which it is worst because is also a bug… but in your software :-).

    Best regards.

  4. You shouldn’t be quoting numbers under any circumstances – it is not a protection against DoS attacks, or any other attacks.

    Every language that connects to MySQL has the ability to generate prepared statements, escaping values as needed. You should be doing this for every query.

  5. Update says:

    A similar bug in 5.5:

    http://bugs.mysql.com/bug.php?id=67097

    ‘Mysql optimizer usually detects impossible WHERE clauses like “`a` 10″ or when `a` is unsigned int and “`a` < -4". These type of queries are not very common but happen from time to time.
    The problem is the optimizer fails to detect impossible WHEREs in some cases when quoted negative values are compared against unsigned int columns.'

Leave a Reply