JSON::XS utf8 formatted string will not load into MYSQL json type column

misterperl has asked for the wisdom of the Perl Monks concerning the following question:

I'm sure some monks have seen this, or something similar. After this step:

$_ = encode_json $hashref

and

(DBI query) insert into table ( $id, '$_' );

I get

Invalid JSON text: "Invalid encoding in string." at postition 3464 ..

I then did

split //

and that position was the first instance of a \n. It looked like:

3463 '.'
3464 '\\'
3465 'n'
.
.
[download]

so evidently it split \n into 2 chars, \ and n. I'm not sure how to direct the utf conversion to treat \(letter) as a character but I think that's where the JSON string goes south?

I chatGPT'ed issue and most suggestions were to replace all '\' with '\\' , so I tried some replacements like: s.([^\\])\\([^\\]).$1\\\\$2.g which had no joy: same error, same position. I'm wondering would like a cast $_ as json or json_object($_) or some other trick like that help?

AS always kind advice is greatly appreciated, and blessed Friday to all

Comment on JSON::XS utf8 formatted string will not load into MYSQL json type column Select or Download Code

Replies are listed 'Best First'.
Re: JSON::XS utf8 formatted string will not load into MYSQL json type column by Corion (Patriarch) on Aug 25, 2023 at 17:49 UTC
Are you using DBI placeholders? If not, you will have to think about how to quote your SQL string, as the backslash is interpreted by MySQL as a quoting character.	[reply]
Re^2: JSON::XS utf8 formatted string will not load into MYSQL json type column by misterperl (Friar) on Aug 25, 2023 at 19:22 UTC
I removed all of my substitutes, changed to DBI placeholders , and presto ALL the issues are gone ! TY you were a great help I voted you a big ++ :)	[reply]
Re^2: JSON::XS utf8 formatted string will not load into MYSQL json type column by misterperl (Friar) on Aug 25, 2023 at 19:13 UTC
placeholders as in the "?" syntax? I have used those before; that's a very good suggestion Ill try that.. TY	[reply]
Re: JSON::XS utf8 formatted string will not load into MYSQL json type column by misterperl (Friar) on Aug 25, 2023 at 18:28 UTC
no I'd expect the json type would accept these chars. I fixed the \n issue I think, with `s/\\([a-z])/\\\\$1/g`. It gets past the \n issue now. But now I'm getting errors on ', like on the word "don't". Wow what a Friday nightmare this became! Ill look into your DBI idea.. TY >	[reply] [d/l]
Re^2: JSON::XS utf8 formatted string will not load into MYSQL json type column by Corion (Patriarch) on Aug 25, 2023 at 18:36 UTC
This should be very little surprise. When you print out your SQL string, you will find that the `'` in `don't` is read as terminating the quotes around `'$_'`. DBI placeholders circumvent the problem.	[reply] [d/l] [select]
Re^3: JSON::XS utf8 formatted string will not load into MYSQL json type column by misterperl (Friar) on Aug 25, 2023 at 19:14 UTC
Great idea I'm on it!	[reply]