php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #44639 PDO quotes integers in prepared statement
Submitted: 2008-04-04 15:16 UTC Modified: 2016-10-10 22:27 UTC
Votes:158
Avg. Score:4.5 ± 0.8
Reproduced:140 of 144 (97.2%)
Same Version:114 (81.4%)
Same OS:77 (55.0%)
From: jgauld at blueyonder dot co dot uk Assigned: adambaratz (profile)
Status: Closed Package: PDO Core
PHP Version: 5.* OS: *
Private report: No CVE-ID: None
 [2008-04-04 15:16 UTC] 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;

<?php
$DB = new PDO('mysql:dbname=my_db;host=localhost', 'user', 'pass');
$stmt = $DB->prepare('select * from my_table where id>?');
$stmt->bindValue(1, 13);
$stmt->execute();
?>

or

<?php
$DB = new PDO('mysql:dbname=my_db;host=localhost', 'user', 'pass');
$stmt = $DB->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'

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-06-12 13:42 UTC] dobamail at gmail dot com
Hi.
PDO::MySQL
The code:
            $stmt   = $db->prepare('
                SELECT  id, hu_name, ord
                FROM    products
                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-07-03 15:17 UTC] uw@php.net
This is not a driver issue. Its the PDO SQL driver messing up SQL statements.
 [2008-10-21 11:59 UTC] 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 version	5.0.51a
 [2009-07-21 00:30 UTC] 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
 [2009-08-17 17:38 UTC] jani@php.net
But you didn't bother updating the version field. Done now..
 [2009-09-22 21:18 UTC] sjoerd@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-10-25 19:29 UTC] 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-11-19 22:04 UTC] jani@php.net
Fixed invalid version. Please don't invent your own strings there. It needs to start always with the major version number..
 [2010-02-16 00:05 UTC] 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);
 [2010-02-19 18:47 UTC] 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-06-30 22:05 UTC] 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.
 [2011-04-27 20:24 UTC] riksoft at gmail dot com
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).
 [2011-12-13 22:33 UTC] stephen-d at rogers dot com
I am surprised that something this simple has been unfixed for so long.

I wasted 8 hours trying to figure out "my" mistake. Thinking that it was a PDO bug was inconceivable.

I finally turned on MySQL logging and saw that the numeric argument was being enclosed in quotes. I posted to a web site for help and a kind person sent me this link.

For others struggling with this, the work-around is to type caste all numeric values that will passed to PBO.
 [2012-01-13 19:50 UTC] engin at e-php dot net
It's really sad seeing this aged bug is not gonna fixed.
 [2012-02-25 06:28 UTC] preludeinz at gmail dot com
Please fix! I can't do my course's lab exercise (without introducing a SQL 
injection vulnerability) without this feature.
 [2012-03-12 10:15 UTC] alvaro at demogracia dot com
This only happens on emulated parameter binding, i.e. when PDO::ATTR_EMULATE_PREPARES is TRUE.
 [2012-12-05 04:02 UTC] alek0585 at mail dot ru
Yeah! Try this $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); and you can 
use that!
foreach($data as $key => $value) {
            $stmt->bindParam( ":".$key, $value, (is_int($value) ? PDO::PARAM_INT : 
PDO::PARAM_STR));
        }
but you should be careful with types of vars
 [2014-01-01 12:52 UTC] felipe@php.net
-Package: PDO related +Package: PDO Core
 [2014-01-26 18:12 UTC] mightyuhu@php.net
Confirmed on php 5.3.10-1ubuntu3.8 and MySQL 5.5.32
 [2015-06-17 02:42 UTC] shankao at gmail dot com
Indeed, the problem seems to happen only with emulated prepares.

The code that checks how the type needs to be emulated [1] is not using the PDO_PARAM_* hint at all. It tries to guess the type by itself and uses convert_to_string() on each type but NULL's and BOOL's [2]

[1] pdo_parse_params()
[2] See current git PHP code file ext/pdo/pdo_sql_parser.c around line 894
 [2015-07-23 21:58 UTC] chealer at gmail dot com
sjoerd@php.net, what makes you claim that "The third parameter to bindValue defaults to PDO::PARAM_STR."?

See related ticket #49614 (regarding PDOStatement::execute's second argument).
 [2016-10-10 22:27 UTC] adambaratz@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: adambaratz
 [2016-10-10 22:27 UTC] adambaratz@php.net
I had a duplicate ticket in for this issue (#73234). The fix I committed references the newer ticket.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 05:01:29 2024 UTC