Don’t Quote Your Numbers

March 3, 2009 · Posted in MySQL 

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.

Tags: , ,

Comments

3 Responses to “Don’t Quote Your Numbers”

  1. strcmp on March 3rd, 2009 10:52 pm

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

  2. Gary Pendergast on March 4th, 2009 12:01 am

    You make a good point. I’ve submitted a bug report to DBD::mysql to not quote numbers:
    https://rt.cpan.org/Ticket/Display.html?id=43822

  3. Jaimie Sirovich on October 30th, 2010 12:05 am

    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.

Leave a Reply