#44639 [Opn]: PDO quotes integers in prepared statement
ID: 44639 Updated by: j...@php.net Reported By: jgauld at blueyonder dot co dot uk Status: Open Bug Type: PDO related Operating System: All -PHP Version: Any > 5.1.x +PHP Version: 5.* New Comment: Fixed invalid version. Please don't invent your own strings there. It needs to start always with the major version number.. Previous Comments: [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: 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 [2009-09-24 09:03:55] u...@php.net Related reports: http://bugs.php.net/bug.php?id=49651 , http://bugs.php.net/49381 [2009-09-22 21:18:23] sjo...@php.net The third parameter to bindValue defaults to PDO::PARAM_STR. This is different than the original bug reporter said, so it must be changed in the meantime. Because the datatype is PDO::PARAM_STR, the int is cast to a string and quoted. It would make sense to use the PHP type to determine the most logical PDO::PARAM_* type. See also bug #44597, which is essentially the same problem but with booleans and execute() instead of bindValue(). [2009-08-17 17:38:15] j...@php.net But you didn't bother updating the version field. Done now.. [2009-07-21 00:30:49] whistl0r+php at googlemail dot com Hi, this bug is still present in the current PHP version. Tested with PHP 5.2.10 stable and snapshot on Windows. MySQL client api: 5.1.35 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/44639 -- Edit this bug report at http://bugs.php.net/?id=44639&edit=1
#44639 [Opn]: PDO quotes integers in prepared statement
ID: 44639 Updated by: u...@php.net Reported By: jgauld at blueyonder dot co dot uk Status: Open Bug Type: PDO related -Operating System: WinXP +Operating System: All -PHP Version: 5.2.10 +PHP Version: Any > 5.1.x New Comment: Related reports: http://bugs.php.net/bug.php?id=49651 , http://bugs.php.net/49381 Previous Comments: [2009-09-22 21:18:23] sjo...@php.net The third parameter to bindValue defaults to PDO::PARAM_STR. This is different than the original bug reporter said, so it must be changed in the meantime. Because the datatype is PDO::PARAM_STR, the int is cast to a string and quoted. It would make sense to use the PHP type to determine the most logical PDO::PARAM_* type. See also bug #44597, which is essentially the same problem but with booleans and execute() instead of bindValue(). [2009-08-17 17:38:15] j...@php.net But you didn't bother updating the version field. Done now.. [2009-07-21 00:30:49] whistl0r+php at googlemail dot com Hi, this bug is still present in the current PHP version. Tested with PHP 5.2.10 stable and snapshot on Windows. MySQL client api: 5.1.35 [2008-10-21 11:59:03] jgauld at blueyonder dot co dot uk Tried windows snapshot as suggested (5.2.7RC2-dev), but no joy. Result is same, ie: select * from my_table where id>'13' If it helps, phpinfo() reports: PDO Driver for MySQL, client library version5.0.51a [2008-07-03 15:17:35] u...@php.net This is not a driver issue. Its the PDO SQL driver messing up SQL statements. 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/44639 -- Edit this bug report at http://bugs.php.net/?id=44639&edit=1
#44639 [Opn]: PDO quotes integers in prepared statement
ID: 44639 Updated by: sjo...@php.net Reported By: jgauld at blueyonder dot co dot uk Status: Open Bug Type: PDO related Operating System: WinXP PHP Version: 5.2.10 New Comment: The third parameter to bindValue defaults to PDO::PARAM_STR. This is different than the original bug reporter said, so it must be changed in the meantime. Because the datatype is PDO::PARAM_STR, the int is cast to a string and quoted. It would make sense to use the PHP type to determine the most logical PDO::PARAM_* type. See also bug #44597, which is essentially the same problem but with booleans and execute() instead of bindValue(). Previous Comments: [2009-08-17 17:38:15] j...@php.net But you didn't bother updating the version field. Done now.. [2009-07-21 00:30:49] whistl0r+php at googlemail dot com Hi, this bug is still present in the current PHP version. Tested with PHP 5.2.10 stable and snapshot on Windows. MySQL client api: 5.1.35 [2008-10-21 11:59:03] jgauld at blueyonder dot co dot uk Tried windows snapshot as suggested (5.2.7RC2-dev), but no joy. Result is same, ie: select * from my_table where id>'13' If it helps, phpinfo() reports: PDO Driver for MySQL, client library version5.0.51a [2008-07-03 15:17:35] u...@php.net This is not a driver issue. Its the PDO SQL driver messing up SQL statements. [2008-06-12 13:42:35] dobamail at gmail dot com Hi. PDO::MySQL The code: $stmt = $db->prepare(' SELECT id, hu_name, ord FROMproducts ORDER BY ord DESC, hu_name LIMIT :offset, :limit '); $stmt->bindValue(':offset', ($offset*$limit)); $stmt->bindValue(':limit', $limit); $stmt->execute(); It is work on: - PHP Version 5.2.0-8+etch11; - PDO Driver for MySQL, client library version 5.0.32 - MySQL version: 5.0.32-Debian_7etch5-log Not work on: - PHP Version 5.2.3-1ubuntu6.3 - PDO Driver for MySQL, client library version 5.0.45 - 5.0.45-Debian_1ubuntu3.3 I hope this help you. Best regards. 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/44639 -- Edit this bug report at http://bugs.php.net/?id=44639&edit=1
#44639 [Opn]: PDO quotes integers in prepared statement
ID: 44639 Updated by: j...@php.net Reported By: jgauld at blueyonder dot co dot uk Status: Open Bug Type: PDO related Operating System: WinXP -PHP Version: 5.2.5 +PHP Version: 5.2.10 New Comment: But you didn't bother updating the version field. Done now.. Previous Comments: [2009-07-21 00:30:49] whistl0r+php at googlemail dot com Hi, this bug is still present in the current PHP version. Tested with PHP 5.2.10 stable and snapshot on Windows. MySQL client api: 5.1.35 [2008-10-21 11:59:03] jgauld at blueyonder dot co dot uk Tried windows snapshot as suggested (5.2.7RC2-dev), but no joy. Result is same, ie: select * from my_table where id>'13' If it helps, phpinfo() reports: PDO Driver for MySQL, client library version5.0.51a [2008-10-21 11:27:05] j...@php.net Please try using this CVS snapshot: http://snaps.php.net/php5.2-latest.tar.gz For Windows: http://windows.php.net/snapshots/ [2008-07-03 15:17:35] u...@php.net This is not a driver issue. Its the PDO SQL driver messing up SQL statements. [2008-06-12 13:42:35] dobamail at gmail dot com Hi. PDO::MySQL The code: $stmt = $db->prepare(' SELECT id, hu_name, ord FROMproducts ORDER BY ord DESC, hu_name LIMIT :offset, :limit '); $stmt->bindValue(':offset', ($offset*$limit)); $stmt->bindValue(':limit', $limit); $stmt->execute(); It is work on: - PHP Version 5.2.0-8+etch11; - PDO Driver for MySQL, client library version 5.0.32 - MySQL version: 5.0.32-Debian_7etch5-log Not work on: - PHP Version 5.2.3-1ubuntu6.3 - PDO Driver for MySQL, client library version 5.0.45 - 5.0.45-Debian_1ubuntu3.3 I hope this help you. Best regards. 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/44639 -- Edit this bug report at http://bugs.php.net/?id=44639&edit=1
#44639 [Opn]: PDO quotes integers in prepared statement
ID: 44639 Updated by: [EMAIL PROTECTED] Reported By: jgauld at blueyonder dot co dot uk Status: Open Bug Type: PDO related Operating System: WinXP PHP Version: 5.2.5 New Comment: This is not a driver issue. Its the PDO SQL driver messing up SQL statements. Previous Comments: [2008-06-12 13:42:35] dobamail at gmail dot com Hi. PDO::MySQL The code: $stmt = $db->prepare(' SELECT id, hu_name, ord FROMproducts ORDER BY ord DESC, hu_name LIMIT :offset, :limit '); $stmt->bindValue(':offset', ($offset*$limit)); $stmt->bindValue(':limit', $limit); $stmt->execute(); It is work on: - PHP Version 5.2.0-8+etch11; - PDO Driver for MySQL, client library version 5.0.32 - MySQL version: 5.0.32-Debian_7etch5-log Not work on: - PHP Version 5.2.3-1ubuntu6.3 - PDO Driver for MySQL, client library version 5.0.45 - 5.0.45-Debian_1ubuntu3.3 I hope this help you. Best regards. [2008-04-04 15:16:33] jgauld at blueyonder dot co dot uk Description: When executing a prepared statement with bound values, any integer types are seemingly treated as strings and quoted in the final SQL statement (SQL statements taken from MySQL server log). I realise I've not used the 'data_type' parameter for the ->bindValue() method, but according to the PHP manual I shouldn't need to ... "Explicit data type for the parameter using the PDO::PARAM_* constants. Defaults to PHP native type." So the PHP native type in the shown case is an integer - equivalent to PDO::PARAM_INT, yes? Reproduce code: --- CREATE TABLE my_db.my_table ( id int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; prepare('select * from my_table where id>?'); $stmt->bindValue(1, 13); $stmt->execute(); ?> or prepare('select * from my_table where id>?'); $stmt->execute(array(13)); ?> Expected result: select * from my_table where id>13 Actual result: -- select * from my_table where id>'13' -- Edit this bug report at http://bugs.php.net/?id=44639&edit=1