|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
[2017-04-24 19:25 UTC] adambaratz@php.net
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2025 The PHP GroupAll rights reserved. |
Last updated: Wed Oct 29 12:00:01 2025 UTC |
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')