Bug #69736 [Opn->Nab]: Correct query which contains double quotes gives error

Edit report at https://bugs.php.net/bug.php?id=69736&edit=1 ID: 69736 Updated by: cmb@php.net Reported by: Jan_Oonk at hotmail dot com Summary: Correct query which contains double quotes gives error -Status: Open +Status: Not a bug Type: Bug Package: PDO ODBC Operating System: Windows 7 SP1 PHP Version: 5.5Git-2015-05-31 (Git) -Assigned To: +Assigned To: cmb Block user comment: N Private report: N New Comment: I have created a simplified test script and recorded the ODBC trace: <https://gist.github.com/cmb69/6f090511bd4349161539>. PHP passes the SQL to SQLPrepare() as is, but the driver[1] considers the first statement to contain a parameter. The second statement is mangled by the driver ("" => []). These are obviously limitations of the driver; I wouldn't call them bugs, because it is not clear which exact SQL-Syntax is supported by the driver (at least I have not been able to find the specification). With regard to your concrete problem: just tell the users that they must not use double-quotes, but single-quotes (apostrophs). Treat everything else as syntax error. [1] Microsoft Access Driver (*.mdb) 6.01.7601.17632 Previous Comments: ------------------------------------------------------------------------ [2015-06-03 11:25:11] Jan_Oonk at hotmail dot com //You can leave the username and password empty: $username=""; $password=""; //Also you can use a different older accessdriver. This depends on your configuration $accessdriver="{Microsoft Access Driver (*.mdb)}"; //In that case make sure it's an older Access .mdb format and check file extension is .mdb $dbnameFile="C:\\wamp\\www\\elearning2\\databases\\film.mdb"; ------------------------------------------------------------------------ [2015-06-01 06:37:41] Jan_Oonk at hotmail dot com Shorter independent reproducable testscript. You only need an Access (.mdb/.accdb) database with at least one table called 'film' and a column 'titel'. Put some records in it. At least 1 with 'Batman'. //also using a older Access version of the database "film.mdb" didn't work //be sure to use full/absolute pathname $dbnameFile="C:\\wamp\\www\\elearning2\\databases\\film.accdb"; $username="user1"; $password="secret"; $accessdriver="{Microsoft Access Driver (*.mdb, *.accdb)}"; $dbDB = new PDO("odbc:Driver=$accessdriver;Dbq=$dbnameFile", $username, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); //Testcases, comment all but one //Testcase 1: works $sql="select * from film where titel like 'Batman'"; //Testcase 2: works $sql='select * from film where titel like \'Batman\''; //Testcase 3: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. $sql="select * from film where titel like \"Batman\""; //Testcase 4: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. $sql='select * from film where titel like "Batman"'; //Testcase 5: Syntax error (missing operator) in query expression 'titel like \[Batman\] $sql='select * from film where titel like \"Batman\"'; //Testcase 6: Syntax error (missing operator) in query expression 'titel like []Batman[] $sql='select * from film where titel like ""Batman""'; $result=$dbDB->query($sql); $rows=$result->fetchAll(PDO::FETCH_ASSOC); $result->closeCursor(); foreach($rows as $row) { echo $row["TITEL"]."\n"; echo "<br>"; } $dbDB=null; ------------------------------------------------------------------------ [2015-05-31 23:03:07] requinix@php.net Thank you for this bug report. To properly diagnose the problem, we need a short but complete example script to be able to reproduce this bug ourselves. A proper reproducing script starts with <?php and ends with ?>, is max. 10-20 lines long and does not require any external resources such as databases, etc. If the script requires a database to demonstrate the issue, please make sure it creates all necessary tables, stored procedures etc. Please avoid embedding huge scripts into the report. Please provide a repro script that isn't dependent on your connectDB.php code. ------------------------------------------------------------------------ [2015-05-31 21:49:49] Jan_Oonk at hotmail dot com In MySQL you can use a single and double quote string delimiter. So testcases 1-4 works and 5-6 fails as expected. In Oracle you can only use a single quote string delimiter. So testcases 1-2 works and 3-6 fails as expected. As Access also use a single and double quote string delimiter why is it then that testcase 3 and 4 don't work? Is this a bug or is there a way to run a query, on an Access database using PDO/ODBC, containing a double quote string delimiter? I can't use bind parameters, prepared statements or rewrite the query because the query is totaly unknown and given by the user and can also contain syntax error(s). I can't modify this user query and just want it to be executed by PDO/ODBC but how? I have read that the quote() function is not implemented for PDO/ODBC. Does this has something to do with it? ------------------------------------------------------------------------ [2015-05-31 21:22:16] Jan_Oonk at hotmail dot com Description: ------------ Both queries below work inside Microsoft Access 2013: [1] select * from movie where moviename like 'batman' [2] select * from movie where moviename like "batman" But when I try to run both queries with PHP 5.5.12 and PDO/ODBC only query 1 works as expected. Query 2 throws an error. I made a testscript varying with different escape characters/methods and using different string quotes. Especially the last 2 testcases #5 and #6 throw a strange error description. It looks like the double quotes are changed into square brackets: [ and ]. Test script: --------------- include "../include/connectDB.php"; //Testcases, comment all but one //Testcase 1: works $sql="select * from film where titel like 'Batman'"; //Testcase 2: works $sql='select * from film where titel like \'Batman\''; //Testcase 3: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. $sql="select * from film where titel like \"Batman\""; //Testcase 4: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. $sql='select * from film where titel like "Batman"'; //Testcase 5: Syntax error (missing operator) in query expression 'titel like \[Batman\] $sql='select * from film where titel like \"Batman\"'; //Testcase 6: Syntax error (missing operator) in query expression 'titel like []Batman[] $sql='select * from film where titel like ""Batman""'; $result=$dbDB->query($sql); $rows=$result->fetchAll(PDO::FETCH_ASSOC); $result->closeCursor(); foreach($rows as $row) { echo $row["TITEL"]."\n"; echo "<br>"; } include "../include/closedbs.php"; Expected result: ---------------- It should return 1 record with all details about the movie Batman. Actual result: -------------- //Testcase 1: works $sql="select * from film where titel like 'Batman'"; //Testcase 2: works $sql='select * from film where titel like \'Batman\''; //Testcase 3: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. $sql="select * from film where titel like \"Batman\""; //Testcase 4: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. $sql='select * from film where titel like "Batman"'; //Testcase 5: Syntax error (missing operator) in query expression 'titel like \[Batman\] $sql='select * from film where titel like \"Batman\"'; //Testcase 6: Syntax error (missing operator) in query expression 'titel like []Batman[] $sql='select * from film where titel like ""Batman""'; ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=69736&edit=1