Search code examples
phpmysqlpear

MySQL select from char column using int - leading zero causes unexpected result

I have the following data:

stockcode (CHAR) | description (VARCHAR)
----------------------------------------------
1234               | some product
01234              | some other product

I run the following code:

$sql = "SELECT * FROM stock WHERE stockcode = " . $db->quoteSmart($stockcode)
$res = $db->query($sql);

If I pass the stock code "01234" in, I get the second row as expected. If I pass in "1234" I get both rows, when I expect to only get the first.

By echoing out the sql statements, it seems to be because only the 01234 stockcode gets quotes around it, the 1234 one doesn't (presumably quoteSmart thinks as it is an integer it doesn't need quoting?).

Mysql is then comparing the int 1234 to the CHAR column, and deciding they both match (presumably it is doing the comparison as ints?)

There are a lot of places in the code which build queries like this, and 99% of the time, the stockcode variable will be alphanumeric, it's only occasionaly entirely numeric, and very rarely contains a leading zero, this is just a fluke occurrence I stumbled across today.

Can anyone recomend an easy solution?

Solution

  • Try explicitly casting your variables to strings:

    $db->quoteSmart((string)$stockcode);
// instead of:
// $db->quoteSmart($stockcode);

    Edit:

    If this does not work, I imagine that quoteSmart() isn't really that smart (or it's trying to be too smart for it's own good), so you'll very likely need to stop using it and stick to bound parameters (which I suggest over this method anyway).