开发者

Inserting in mySQL a JSON encoded value with special chars fails

开发者 https://www.devze.com 2023-03-30 00:53 出处:网络
I am trying to execute this query with PDO: UPDATE categories SET keywords = \'{\"bg\":[\"keyword1\",\"\\u0431\\u044a\\u043b\\u0433\\u0430\\u0440\\u0441\\u043a\\u0438 \"]}\'

I am trying to execute this query with PDO:

UPDATE categories
SET keywords = '{"bg":["keyword1","\u0431\u044a\u043b\u0433\u0430\u0440\u0441\u043a\u0438 "]}'
WHERE id = 4;

The problem is that it seems to record the hex value without the backslashes ("\"). It does show up without the backslashes in my app but also in PHPMyAdmin.

I use exec() on this query, and I have tried it with this full-text query, without any dynamic/transformed variables. The PDO parameters are as follow:

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"

Can someone explain me what is happening here? Thanks!

UPDATE: I have executed this exact query from mysql's CLI, then dis开发者_开发问答played it there, and the problem is the same: all the backslashes have disappeared. So it doesn't seem the problem has anything to do with PDO, but with MySQL - or me! My version is 5.1.53


The \ character has special meaning in SQL. Use bound parameters instead of hardcoding the value into the base string you use for the query.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号