php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
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
 [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

Add a Patch

Pull Requests

Add a Pull Request

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-2021 The PHP Group
All rights reserved.
Last updated: Mon Oct 18 21:03:39 2021 UTC