Edit report at https://bugs.php.net/bug.php?id=72065&edit=1
ID: 72065
Comment by: jaro at ttx dot sk
Reported by: jaro at ttx dot sk
Summary: Single backslash problem in parametrized query
Status: Wont fix
Type: Bug
Package: PDO PgSQL
Operating System: *
PHP Version: Irrelevant
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2016-07-10 05:56:56] 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-04-20 15:11:34] 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')
^
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=72065&edit=1