yii2
yii2 copied to clipboard
yiisoft
PDO exception on batch insert with specially formed strings on postgresql database
There is an issue when trying to batch insert on postgresql database using the following code:
$rows = [
[':a ? \\'],
];
$db->createCommand($queryBuilder->createTable('test', ['text' => 'string']))->execute();
$db->createCommand($queryBuilder->batchInsert('test', ['text'], $rows))->execute();
Running this will provide the following exception:
Exception: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters Failed to prepare SQL: INSERT INTO "test" ("text") VALUES (':a ? ')
I'm using PHP 5.5.27.
My guess is that sql, created by batchInsert should not contain escaped values, but placeholders.
@cebe I think its simpler than that. https://github.com/yiisoft/yii2/blob/master/framework/db/ColumnSchema.php#L132
When using an insert in batchInsert() this is the line that hadles the sanitization of the data which as you can see it doesn't sanitize special characters like quotes, slashes and parenthesis
I see what it is bug of PDO driver for PostgreSQL:
$pdo = new \PDO('pgsql:host=localhost;dbname=yii2basic', 'username', 'password');
$pdo->prepare("select ':a ? \\'");
PDO::prepare(): SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters
And this code works properly:
$pdo = new \PDO('mysql:host=127.0.0.1;dbname=yii2basic', 'username', 'password');
$pdo->prepare("select ':a ? \\'");
Hi, i have a problem with batchInsert postgresql- error SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters. Only with those 3 strings Vorfahrt Mercedes GLC Coupй:Vorfahrt Mercedes GLC Coupй Backstage DTM 2016 (Teil 2):Backstage DTM 2016 (Teil 2) planet e.:Glitschige Geschдfte-Die Aalmafia ,when i replace ':' with ': ', everything works Ok . Other records with strings that content ':' working fine Can anyone help? Thanks in advanse
Yii version2.0.36 (not sure about 2.0.40).
Original error: SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters Failed to prepare SQL: INSERT INTO ...
Bug still present even for : , like @nazakar suggested. Escaping in \: and \? also didn't solved problem. Setting the
Yii::$app->db->emulatePrepare = false;
, as suggested many times for such case, didn't solved the issue. Also, this bug, in my case, is present in raw createCommand()->execute() flow. Only suitable solution for me was direct call of PDO->exec():
Yii::$app->db->pdo->exec('your raw sql command');
That's why this is not the PDO nor PostgreSQL bug, but the Yii2 itself
