Bug #69736 [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: Not a bug Type: Bug Package: PDO ODBC Operating System: Windows 7 SP1 PHP Version: 5.5Git-2015-05-31 (Git) Assigned To: cmb Block user comment: N Private report: N New Comment: > Maybe this explains the mangling? That's possible. The developers of the Microsoft Jet Enginge (Jet Red) most likely have deeper insights. :) Previous Comments: ------------------------------------------------------------------------ [2015-06-04 13:00:10] Jan_Oonk at hotmail dot com Thanks for the reply. Obviously I can do nothing about this so I have to accept this. Indeed the only work around is to use single quotes which is fine. Also I found out that you CAN use double quotes but not as a delimiter for a string inside a query. So this is perfectly fine: SELECT plantnaam, 1+1 as [bla1 column],2+2 as bla2,3+3 as [bla3] FROM Plant where plantnaam like '%""%' gives the following record: plantnaam bla1 column bla2 bla3 RO""RO"RO 2 4 6 Also you see that in Access square brackets are optionally used to define a new columnname, but that it is required when you use a space in the new columnname. In other SQL languages double quotes are used to define a new columnname. For example using Oracle with PHP/PDO I can use: SELECT plantnaam, 1+2 as "bla1 column",2+2 as bla2,3+3 as "bla3" FROM plant; Maybe this explains the mangling? ------------------------------------------------------------------------ [2015-06-04 11:57:22] cmb@php.net 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 ------------------------------------------------------------------------ [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. ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=69736 -- Edit this bug report at https://bugs.php.net/bug.php?id=69736&edit=1