1

i have this JSON string, which i want to insert into table. Here is my insert query:

$insert_sql = 'INSERT INTO yun_postmeta (post_id, meta_key, meta_value)
    VALUES (5054, "_wc_free_gift_coupon_free_shipping", "a:1:{i:6;a:3:{s:10:"product_id";i:6;s:12:"variation_id";i:0;s:8:"quantity";i:1;}}");';

Of course, the query breaks because of the quotes ("). When i add backslash () in front of them, to escape, the query works and string gets inserted into the table, but for some reason my system doesn't work properly.

However, if i copy the JSON string and insert it manually into table, using phpmyadmin, system works normally.

Any idea what could be the problem here?

This sounds like encoding problem, but i don't have a clue what to do to solve it.

Thank you!

tadman
  • 208,517
  • 23
  • 234
  • 262
Veljko Simovic
  • 13
  • 3

1 Answers1

0

The SQL query should only use single quotes around values ie:

VALUES (5054, '_wc_free_gift_coupon_free_shipping', ...

So in PHP the string should be defined in double quotes to limit the amount of escaping you do as well as allowing variables to be added:

$insert_sql = "INSERT INTO yun_postmeta (post_id, meta_key, meta_value)
VALUES (5054, '_wc_free_gift_coupon_free_shipping', ... )";

As far as your values you should use mysqli_real_escape_string to handle escaping the double quotes: http://php.net/manual/en/mysqli.real-escape-string.php

$escaped_json = mysqli_real_escape_string($con, 'a:1:{i:6;a:3:{s:10:"product_id";i:6;s:12:"variation_id";i:0;s:8:"quantity";i:1;}}');

$insert_sql = "INSERT INTO ... VALUES ( 5054, '_wc_free_gift_coupon_free_shipping', $escaped_json ...
d g
  • 1,594
  • 13
  • 13
  • Or use `MySQLi_` Prepared Statements. – Martin May 28 '18 at 16:30
  • Hello, here's the code, changed based on your suggestion: $escaped_json = mysqli_real_escape_string($conn, 'a:1:{i:6;a:3:{s:10:"product_id";i:6;s:12:"variation_id";i:0;s:8:"quantity";i:1;}}'); $insert_sql = "INSERT INTO yun_postmeta (post_id, meta_key, meta_value) VALUES (4879, '_wc_free_gift_coupon_data', $escaped_json);"; I got following error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':1:{i:6;a:3:{s:10:\"product_id\";i:6;s:12:\"variation_id\";i:0;s:8:\"quantity\";' at line 2 – Veljko Simovic May 31 '18 at 11:12