Bug #66434 [Opn]: PDO not binding when columns with quote

Edit report at https://bugs.php.net/bug.php?id=66434&edit=1 ID: 66434 Updated by: adambaratz@php.net Reported by: immobilia at hotmail dot com Summary: PDO not binding when columns with quote Status: Open Type: Bug Package: PDO related Operating System: Windows/Linux PHP Version: Irrelevant Block user comment: N Private report: N New Comment: The SQL parser gets confused when it sees quotes. A workaround would be to bind a parameter with the date format string. It might also be worth trying to disable emulated prepares for better performance. Previous Comments: ------------------------------------------------------------------------ [2014-01-07 14:13:01] immobilia at hotmail dot com 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 this bug report at https://bugs.php.net/bug.php?id=66434&edit=1