INSERT to MYSQL throwing error due to apostrophe-CodePudding

sql ="""INSERT INTO birthday(team, birthday)
                VALUES ('Norway', {"2020-01-01": "Ram's BDay"}));"""

Above sql statement throws an error while inserting.

ProgrammingError: (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server ver

Based on manual attempts I know it is related to apostrophe. Is it possible to insert the above statement, I don't have the control over apostrophe coming in the data stream.

CodePudding user response：

You have a syntax mistake

sql ='"""INSERT INTO birthday(team, birthday)
            VALUES (\'Norway\', {"2020-01-01": "Ram\'s BDay"}));"""'

If you are using more apostrophe be sure to add \ so it will not read

CodePudding user response：

you must put the hole JSON String in quotes an escape them in the string

sql ="""INSERT INTO birthday(team, birthday)
                VALUES ('Norway', "{\"2020-01-01\": \"Ram's BDay\"}" );"""

or you use single quotes then you must escape them in the string

sql ="""INSERT INTO birthday(team, birthday)
                VALUES ('Norway', '{"2020-01-01": "Ram\'s BDay"}' );"""

SAMPLE

mysql>   CREATE TABLE birthday(team VARCHAR(100), birthday VARCHAR(100)) ;
Query OK, 0 rows affected (0.14 sec)

mysql>   
mysql>   INSERT INTO birthday(team, birthday)
    ->                 VALUES ('Norway', "{\"2020-01-01\": \"Ram's BDay\"}" );
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM birthday;
 -------- ------------------------------ 
| team   | birthday                     |
 -------- ------------------------------ 
| Norway | {"2020-01-01": "Ram's BDay"} |
 -------- ------------------------------ 
1 row in set (0.03 sec)

mysql>