Bug #74220 [Com]: Special crafted SQL statement trips PDO

Edit report at https://bugs.php.net/bug.php?id=74220&edit=1 ID: 74220 Comment by: mfischer@php.net Reported by: mfischer@php.net Summary: Special crafted SQL statement trips PDO Status: Open Type: Bug Package: PDO PgSQL Operating System: Ubuntu 14.04 PHP Version: 7.1.2 Block user comment: N Private report: N New Comment: Not sure if I missed something obvious. For now I found multiple ways to make it work: 1. use prepared statements (i.e. use :name or ? placeholder, etc.) 2. set the PDO flag PDO::ATTR_EMULATE_PREPARES to true 3. apply pgsql C-Style escape syntax [1], i.e. VALUES(E'\\'':1) About 2): this worked for simple cases but failed in a large application, didn't yet investigate why About 3): haven't dug deeper whether this can cause more troubles or not [1] https://www.postgresql.org/docs/9.6/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE Previous Comments: ------------------------------------------------------------------------ [2017-03-07 22:34:16] mfischer@php.net Description: ------------ I initially reported this at https://github.com/cakephp/cakephp/issues/10373 but was told "If you can reproduce this issue with raw PDO, then its not a CakePHP issue." The provided SQL statement, as executed with PDO, does not insert the provided data correctly. Prerequisites: create a db role / database table: postgres=# create role username login password 'password'; CREATE ROLE postgres=# create database db owner username; CREATE DATABASE postgres=# \c db You are now connected to database "db" as user "postgres". db=# set role username; SET db=> create table models(field varchar(12)); CREATE TABLE The provided SQL statement (see test script) is valid Postgres syntax. Test script: --------------- <?php $pdo = new PDO('pgsql:host=localhost;dbname=db', 'username', 'password'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = <<<SQL INSERT INTO models (field) values('\'':1'); SQL; $pdo->query($sql); $result = $pdo->query('SELECT * FROM models'); var_dump($result->fetchAll()); Expected result: ---------------- array(1) { [0]=> array(2) { ["field"]=> string(4) "\':1" [0]=> string(4) "\':1" } } Actual result: -------------- array(1) { [0]=> array(2) { ["field"]=> string(4) "\'$1" [0]=> string(4) "\'$1" } } ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=74220&edit=1