Edit report at http://bugs.php.net/bug.php?id=44639&edit=1
ID: 44639 Comment by: riksoft at gmail dot com Reported by: jgauld at blueyonder dot co dot uk Summary: PDO quotes integers in prepared statement Status: Open Type: Bug Package: PDO related Operating System: * PHP Version: 5.* Block user comment: N Private report: N New Comment: Php-cli V. 5.3.1: I see that if no PDO::PARAM_* is used PDO use variable type. E.g. $sth->bindValue(6, $checksum, PDO::PARAM_INT); or $sth->bindValue(6, $checksum); correctly treat the value $checksum (CRC32 int) as a number >>>> BUT <<<< there is a problem with unsigned integer: according to http://it.php.net/manual/en/pdo.constants.php there isn't any useful costant for larger numeric value. Real case: The only way to store a crc32 value among different DBs and platforms (32/64 bit) is converting CRC32 to unsigned int this way sprintf("%u",crc32). But PDO::PARAM_INT is not able to handle values larger than 2147483647. Solution: security of inputs by floatval() (at least when bindvalue dont write as a string which is not my case). Previous Comments: ------------------------------------------------------------------------ [2010-06-30 22:05:44] stuart at tase dot co dot uk As noted earlier, even when specifying an explicit type using the constants e.g. PDO::PARAM_INT it treats them according to their native type, so a string containing a numerical value is treated as a string instead of an integer. It makes a complete mockery of the data type constants, they are just ignored. One of two things should happen, numerical strings should be cast to integers by PDO when PARAM_INT is used or PDO should throw an error/exception when it's expecting an int and receives a string. It should NOT try to be clever and ignore the explicit data type, sending potentially malformed SQL to the database. This violates one of the major reasons for using prepared statements to begin with, data and database integrity. ------------------------------------------------------------------------ [2010-02-19 18:47:54] php at javierparra dot com dot mx @ nathan dot h at makemeheal dot com It's because you're using trim() trim returns a string, try casting it directly as an int: (int) trim($_GET['skip']) In 5.3 still doesn't work using the ? syntax. ------------------------------------------------------------------------ [2010-02-16 00:05:44] nathan dot h at makemeheal dot com Has this been fixed? Interestingly, if you put value directly: $fetchPictures->bindValue(':skip', 0, PDO::PARAM_INT); this works. But if I do $fetchPictures->bindValue(':skip', trim($_GET['skip']), PDO::PARAM_INT); It doesn't work. Also, for some reason I don't have issues with this one: $fetchPictures->bindValue(':max', $max, PDO::PARAM_INT); ------------------------------------------------------------------------ [2009-11-19 22:04:21] j...@php.net Fixed invalid version. Please don't invent your own strings there. It needs to start always with the major version number.. ------------------------------------------------------------------------ [2009-10-25 19:29:28] mailmichael at yahoo dot com Ran into same issue. Relatively new to php and mysql. It seems PDOStatement wants to bind values as strings, even overriding the explicit data type! It seems the safest way to use PDOStatements with integers is to use bindValue, intval() for value and PDO::PARAM_INT for explicit data type: $pdoStmt->bindValue(':limit', intval($limit_value), PDO::PARAM_INT); Here is my test code: <? echo phpversion(); $pdoh = new PDO('mysql:host=localhost'); $pdoh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING); $pdoStmt = $pdoh->prepare('SELECT 1 LIMIT :limit'); $pdoStmt->execute(Array(':limit' => 0)); $pdoStmt->bindValue(':limit', 0); $pdoStmt->execute(); $pdoStmt->bindValue(':limit', '0', PDO::PARAM_INT); $pdoStmt->execute(); ?> 5.2.6-3ubuntu4.2 ( ! ) Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0'' at line 1 in pdotest.php on line 6 ( ! ) Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0'' at line 1 in pdotest.php on line 8 ( ! ) Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''0'' at line 1 in pdotest.php on line 10 ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/bug.php?id=44639 -- Edit this bug report at http://bugs.php.net/bug.php?id=44639&edit=1
