php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #80271 prepare json_set throw exception on mysql 8.0.22
Submitted: 2020-10-22 15:55 UTC Modified: 2020-12-20 13:48 UTC
Votes:5
Avg. Score:5.0 ± 0.0
Reproduced:5 of 5 (100.0%)
Same Version:4 (80.0%)
Same OS:4 (80.0%)
From: cxlblm at gmail dot com Assigned: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: 7.4.11 OS: docker/ubuntu
Private report: No CVE-ID: None
View Developer Edit
 [2020-10-22 15:55 UTC] cxlblm at gmail dot com 
Description:
------------
$pdo = new \PDO("mysql:host=host.docker.internal;dbname=laravel;charset=utf8mb4", 'root', 'root');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $pdo->prepare('update test set setting = json_set(`setting`, "$.name", ?)');
$r = $stmt->execute(['fefwef']);
var_dump($r);

Expected result:
----------------
true

Actual result:
--------------
PHP Fatal error:  Uncaught PDOException: SQLSTATE[22032]: <<Unknown error>>: 3141 Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 1. in /opt/project/local/PDO.php:10
Stack trace:
#0 /opt/project/local/PDO.php(10): PDOStatement->execute(Array)
#1 {main}
  thrown in /opt/project/local/PDO.php on line 10

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-10-24 19:07 UTC] mxmsw at hotmail dot com 
Encountering this issue with 'ATTR_EMULATE_PREPARES' set to true will "fix" this. This is not the recommended way (security vulnerabilities).

Reading the mysql change log (https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-22.html)

under the 'Optimizer Notes' section notes

----
Important Change: A prepared statement is now prepared only once, when executing PREPARE, rather than once each time it is executed. In addition, a statement inside a stored procedure is also now prepared only once, when the stored procedure is first executed. This change enhances performance of such statements, since it avoids the added cost of repeated preparation and rollback of preparation structures, the latter being the source of several bugs.
------

This has also affected prepared statements not regarding JSON specifically.

Downgrading from MYSQL 8.0.22 to 8.0.21 resolves this issue
 [2020-12-18 14:16 UTC] cxlblm at gmail dot com 
This is a MySQL bug, fixed in 8.0.23
 [2020-12-18 14:19 UTC] nikic@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: nikic
 [2020-12-18 14:19 UTC] nikic@php.net 
Okay, in that case I'll close this one... do you happen to have a bug ID on the MySQL side handy, for future reference?
 [2020-12-20 13:48 UTC] cxlblm at gmail dot com 
MySQL bug:
https://bugs.mysql.com/bug.php?id=101575
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Dec 26 14:01:30 2024 UTC