1

My goal is simply to convert a JavaScript object to JSON and insert it into a MySQL row that stores the MySQL JSON data type. This process must happen as part of a large batch insert, so I cannot edit the data manually. When call JSON.stringify on the object, I get valid JSON, but, because the object contains song lyrics that often contain single quotes, when I try to run the SQL query, MySQL throws a parse error.

This works fine

const validJson = '{"foo": "bar", "buzz": 1}';
INSERT INTO table_name ( songid, json )       
VALUES ( ${song.songid}, ${validJson} );

But, this doesn’t

const validJsonWithASingleQuote = {"foo's": "bar", "buzz": 1}';
INSERT INTO table_name ( songid, json )       
VALUES ( ${song.songid}, ${validJsonWithASingleQuote} );

I also tried using a prepared statement with no luck

PREPARE myInsert FROM 'INSERT INTO table_name ( songid, json ) VALUES ( ?, ? )';
SET @a = ${song.songid};
SET @b = ${JSON.stringify(r)};
EXECUTE myInsert USING @a, @b;

I should also mention, that the original JavaScript objects contain strings that have single quotes escaped with "\". The JSON.stringify method, decodes those backslashes.

Unfortunately, all of the SO questions I have found on this topic either recommend escaping single quotes with "\" manually or they have gone unresolved. Is there a programatic way to accomplish this? Perhaps a JavaScript or MySQL method that would generate valid JSON and leave the "\'" sequences in?

Brandon Brown
  • 331
  • 6
  • 16
  • The prepared statement option should be fine. What is your MySQL client in these scenarios? Are you running the queries directly in MySQL or are you connecting from another process (like NodeJS, PHP, etc)? – Phil Apr 22 '20 at 23:37

2 Answers2

1

I finally found my way to this answer: https://stackoverflow.com/a/49525488/1359529

Turns out the Node.js driver for mysql contains an escape method. So, something like this works:

PREPARE myInsert FROM 'INSERT INTO table_name ( songid, json ) VALUES ( ?, ? )';
SET @a = ${song.songid};
SET @b = ${mysql.escape(JSON.stringify(sr))};
EXECUTE myInsert USING @a, @b;
Brandon Brown
  • 331
  • 6
  • 16
0

This tripped me up for a couple of days!

I'm using the package and was having trouble with a single quote as a prop value:

{
  name: "Jade's Palace",
}

I struggled to escape the single quote for mysql and could not create "Jade's Palace" because JS uses \ as its escape char.

The solution was a prepared statement with the escape method.

const query = 'INSERT INTO Places(id, data) VALUES ?';

const params = results.data?.map((data: any) => [
  data.id,
  {
    toSqlString: () => connection?.escape(JSON.stringify(data))
  },
]);

await connection.query(query, [params]);
Craig
  • 11
  • 1