From: immobilia at hotmail dot com
Operating system: Windows/Linux
PHP version: Irrelevant
Package: PDO related
Bug Type: Bug
Bug description:PDO not binding when columns with quote
Description:
------------
In certain cases, binding is not working.
In my case, it's due to the quotes : one in a column name, and two
others in DATE_FORMAT
"SELECT d1, ROUND( SUM(col1 with space and' quote) / SUM(`col1 with
space) * 100 , 2) AS Percent`
FROM test WHERE d1 BETWEEN :condition1 AND :condition2
GROUP BY DATE_FORMAT(d1, '%Y-%m')"
// FAILLED : KO on 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 ':condition1 AND :condition2 GROUP BY DATE_FORMAT(d1, '%Y-%m')
"SELECT d1, ROUND( SUM(col1 with space and' quote) / SUM(`col1 with
space) * 100 , 2) AS Percent`
FROM test WHERE d1 BETWEEN :condition1 AND :condition2
GROUP BY DATE_FORMAT(d1, " . '"%Y-%m")'
// SUCCESS : OK on execute
Tried with :
WINDOWS + PHP Version 5.4.17 + PDO : Client API version mysqlnd 5.0.10 -
20111026
LINUX + PHP Version 5.3.10 + PDO : Client API version 5.5.34
Test script:
---------------
/*
DROP TABLE IF EXISTS test;
CREATE TABLE IF NOT EXISTS test (
col1 with space int(11) NOT NULL,
col1 with space and' quote int(11) NOT NULL,
d1 date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
*/
$dsn = 'mysql:host=localhost;dbname=mydb';
$username = 'sql_user';
$password = 'sql_pass';
$driver_options = array(
PDO::ATTR_TIMEOUT => 3 ,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
$pdo = new PDO($dsn, $username, $password, $driver_options);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$condition1 = '2010-01-01';
$condition2 = date("Y-m-d");
$query = "SELECT d1, ROUND( SUM(col1 with space and' quote) /
SUM(col1 with space) * 100 , 2) AS Percent
FROM test WHERE d1 BETWEEN :condition1 AND :condition2
GROUP BY DATE_FORMAT(d1, '%Y-%m')"
; // KO on 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 ':condition1 AND :condition2 GROUP BY DATE_FORMAT(d1, '%Y-%m')
// $query = "SELECT d1, ROUND( SUM(col1 with space and' quote) /
SUM(col1 with space) * 100 , 2) AS Percent
// FROM test WHERE d1 BETWEEN :condition1 AND :condition2
// GROUP BY DATE_FORMAT(d1, " . '"%Y-%m")'
// ; // OK on execute
$stmt = $pdo->prepare($query);
$stmt->bindParam(':condition1', $condition1);
$stmt->bindParam(':condition2', $condition2);
$stmt->execute();
Expected result:
----------------
execute() working with standard binding.
Actual result:
--------------
execute() has no replaced binding and throw an exception :
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 ':condition1 AND
:condition2 GROUP BY DATE_FORMAT(d1, '%Y-%m')
--
Edit bug report at https://bugs.php.net/bug.php?id=66434&edit=1
--
Try a snapshot (PHP 5.4): https://bugs.php.net/fix.php?id=66434&r=trysnapshot54
Try a snapshot (PHP 5.5): https://bugs.php.net/fix.php?id=66434&r=trysnapshot55
Try a snapshot (trunk): https://bugs.php.net/fix.php?id=66434&r=trysnapshottrunk
Fixed in SVN: https://bugs.php.net/fix.php?id=66434&r=fixed
Fixed in release: https://bugs.php.net/fix.php?id=66434&r=alreadyfixed
Need backtrace: https://bugs.php.net/fix.php?id=66434&r=needtrace
Need Reproduce Script: https://bugs.php.net/fix.php?id=66434&r=needscript
Try newer version: https://bugs.php.net/fix.php?id=66434&r=oldversion
Not developer issue: https://bugs.php.net/fix.php?id=66434&r=support
Expected behavior: https://bugs.php.net/fix.php?id=66434&r=notwrong
Not enough info: https://bugs.php.net/fix.php?id=66434&r=notenoughinfo
Submitted twice: https://bugs.php.net/fix.php?id=66434&r=submittedtwice
register_globals: https://bugs.php.net/fix.php?id=66434&r=globals
PHP 4 support discontinued: https://bugs.php.net/fix.php?id=66434&r=php4
Daylight Savings: https://bugs.php.net/fix.php?id=66434&r=dst
IIS Stability: https://bugs.php.net/fix.php?id=66434&r=isapi
Install GNU Sed: https://bugs.php.net/fix.php?id=66434&r=gnused
Floating point limitations: https://bugs.php.net/fix.php?id=66434&r=float
No Zend Extensions: https://bugs.php.net/fix.php?id=66434&r=nozend
MySQL Configuration Error: https://bugs.php.net/fix.php?id=66434&r=mysqlcfg