1

I have encountered a problem in PHP where if I use an integer as a parameter via prepared statements, it doesn't work - but if it is just pasted into the SQL string, it works perfectly.

For example, this will return an incorrect count (3):

$query = $pdo->prepare("SELECT count(*) FROM table WHERE x = :num");

$num = 1556555454;
$query->bindParam(":num", $num, PDO::PARAM_INT);

But this will return it perfectly fine, with the correct row count (2):

$query = $pdo->prepare("SELECT count(*) FROM table WHERE x = 1556555454");

As far as I'm aware, they should both work the exact same - but both queries return different results (ironically, the correct way is returning the incorrect answer).

GROVER.
  • 4,071
  • 2
  • 19
  • 66
  • Is this the exact number causing the issue? It almost just so fits a 32 bit int, else it might become a float. If so, supply a string. MySQL can typecast on its own. – mario Apr 30 '19 at 18:20
  • 2
    What data type is "x" in your database table? Prepared qureis are very smart and will cast as a string or integer as needed. Also consider typcasting the variable in your `bindParam(":num", (int)$num,...` – danielson317 Apr 30 '19 at 18:21
  • 3
    Except in very special cases, you don't need to do this at all. Skip the binding and just do `$query->execute([":num" => 1556555454]);` – miken32 Apr 30 '19 at 18:23
  • @mario that number is unix timestamp :) and was just the one I happened to be testing with at the time, but yes - all unix timestamps seem to be returning the same value when done through prepared statements – GROVER. Apr 30 '19 at 18:24
  • @danielson317 It's an integer with a length of 11. I've tried doing `(int)`, but unfortunately it doesn't change anything :( – GROVER. Apr 30 '19 at 18:25
  • @GROVER. Integer data types have a fixed length of 32 or 64 bit based on your underlying OS. by telling me the length is 11 I would assume it is actually a string. – danielson317 Apr 30 '19 at 18:37
  • 1
    In `INT(11)`, the 11 means almost nothing. It's not a length limit. It doesn't change the max value of the `INT`. `INT` is always 4 bytes, `BIGINT` is always 8 bytes. See https://stackoverflow.com/questions/3135804/types-in-mysql-bigint20-vs-int20/3135854#3135854 – Bill Karwin Apr 30 '19 at 18:42
  • Use `UNSIGNED INT`, that will be big enough for a Unix timestamp. – Barmar Apr 30 '19 at 18:45
  • 1
    @GROVER. Any chance that `$num` is changed after the prepare but before the execute? When using `bindParam()` the bound value will change as the variable changes until the query is executed. As opposed to `bindValue()` which only uses the value of the variable at the time of the binding. – Patrick Q Apr 30 '19 at 18:58

0 Answers0