|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[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'
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Thu Nov 21 06:01:31 2024 UTC |
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.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 10Has 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);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