Edit report at http://bugs.php.net/bug.php?id=44639&edit=1
ID: 44639
Comment by: stuart at tase dot co dot uk
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.*
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[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] [email protected]
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
------------------------------------------------------------------------
[2009-09-24 09:03:55] [email protected]
Related reports: http://bugs.php.net/bug.php?id=49651 ,
http://bugs.php.net/49381
------------------------------------------------------------------------
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
