php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #72065 Single backslash problem in parametrized query
Submitted: 2016-04-20 15:11 UTC Modified: 2016-07-10 05:57 UTC
From: jaro at ttx dot sk Assigned:
Status: Wont fix Package: PDO PgSQL
PHP Version: Irrelevant OS: *
Private report: No CVE-ID: None
 [2016-04-20 15:11 UTC] jaro at ttx dot sk
Description:
------------
There is a problem with single backlash string in parametrized qurery. When I try run test script it gives me error. String with backslash is quoted with PDO::quote with posgresql driver. It seems valid if not used with parametrized query with next string with quotes.
Valid sql:
INSERT INTO test_quote ("id","text","text2","text3","text4") VALUES (1,'aaa',?,'\','test4')
Invalid sql:
INSERT INTO test_quote ("id","text","text2","text3","text4") VALUES (1,'aaa','\',?,'test4')
It affects all versions of php from 5.5 and above what I tested.


Test script:
---------------
$dbh = new PDO('pgsql:host=postgres;dbname=test','test_user','test');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->exec('DROP TABLE IF EXISTS test_quote');
$dbh->exec('CREATE TABLE test_quote ( id int, text varchar(40), text2 varchar(40), text3 varchar(40), text4 varchar(40) )');

$sql = <<<'EOT'
INSERT INTO test_quote ("id","text","text2","text3","text4") VALUES (1,?,'\',?,'test4')
EOT;
try {
  $statement = $dbh->prepare($sql);
  $statement->bindValue( 1 , 'test1', PDO::PARAM_STR);
  $statement->bindValue( 2 , 'test3', PDO::PARAM_STR);
  $statement->setFetchMode(PDO::FETCH_ASSOC);
  $statement->execute();
} catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage();
}


Actual result:
--------------
Connection failed: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ","
LINE 1: ...,"text","text2","text3","text4") VALUES (1,$1,'\',?,'test4')
                                                              ^

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2016-07-10 05:56 UTC] mbeccati@php.net
-Status: Open +Status: Wont fix
 [2016-07-10 05:56 UTC] mbeccati@php.net
PDO's SQL parser has to be compatible with many SQL dialects. In many of them (including old Postgres versions) the backslash is the escape character, so "'\',?,'test4'" is interpreted as "',?'" followed by "test4'".

I don't think it's feasible to make it "standards_strings" compliant on Postgres without deeply changing how PDO works or breaking other drivers.

The following should work instead:

INSERT INTO test_quote ("id","text","text2","text3","text4") VALUES (1,?,E'\\',?,'test4')
 [2016-07-10 05:57 UTC] mbeccati@php.net
-Package: PDO related +Package: PDO PgSQL
 [2016-07-18 13:30 UTC] jaro at ttx dot sk
I experienced this problem with php framework. I don't know solution (fix this in frameworks?). I dig problem and found there is problem with single backslash with pdo driver.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Dec 05 20:01:29 2024 UTC