0

I have this strange issue when I try to perform a transaction using PHP I get a SQL syntax error. When I echo the query and run it through an SQL validator I get the same error but when I directly insert it into the SQL tab in phpMyadmin, there are no error and the query works as it should.

My SQL:

START TRANSACTION;
INSERT INTO
   `orders` (producten, totaalprijs, account_id) 
VALUES
   (
      "{\"601bced33b191\":{\"naam\":\"Sierkussens binnen\", \"alias\":\"sierkussens - binnen\", \"url\":\"https:\\ / \\ / printzelf.nl\\ / new\\ / product\\ / sierkussens - binnen ? term = sierkussen\", \"afbeelding\":\"cms\\ / images\\ / producten\\ / textiel_producten\\ / Sierkussens\\ / sierkussen_1.jpg\", \"aantal\":\"1\", \"hoogte\":null, \"breedte\":null, \"uploaden\":\"1\", \"specificaties\":{\"Formaat\":{\"waarde\":\"40 x 40 cm\"}, \"Materiaal\":{\"waarde\":\"Dekostof\"}, \"Samenstelling\":{\"waarde\":\"Hoes\"}, \"Kleur garen\":{\"waarde\":\"Zwart\"}, \"Kleur rits\":{\"waarde\":\"Zwart\"}, \"Ontwerp\":{\"waarde\":\"PRO ontwerpcontrole\"}}, \"prijs\":\"13.99\", \"totaalprijs\":13.99, \"canvas\":\"{\\n \\\"customer_id\\\": \\\"33\\\", \\n \\\"order_id\\\": \\\"123\\\", \\n \\\"quantity\\\": \\\"1\\\", \\n \\\"rulers\\\": null, \\n \\\"canvas_size\\\": \\\"0x0\\\", \\n \\\"bleed\\\": null, \\n \\\"safety_margin\\\": null, \\n \\\"dpi\\\": null, \\n \\\"multiple_layouts\\\": null, \\n \\\"procheck\\\": \\\"y\\\", \\n \\\"multiple_pages\\\": \\\"1\\\", \\n \\\"product_name\\\": \\\"Sierkussens binnen\\\", \\n \\\"thumbnail\\\": \\\"cms\\\\\\ / images\\\\\\ / producten\\\\\\ / textiel_producten\\\\\\ / Sierkussens\\\\\\ / sierkussen_1.jpg\\\"\\n}\"}}", "19", "33" 
   )
;
UPDATE
   `orders` 
SET
   order_id = CONCAT(account_id, ".", id) 
WHERE
   id = LAST_INSERT_ID();
COMMIT;

This is my PHP:

// Insert orders into database
$encodedproductenarray = json_encode($_SESSION['producten']);
$insertorder = '
START TRANSACTION;
    INSERT INTO orders
    (
        producten,
        totaalprijs,
        account_id
    )
    VALUES
    (
        "'.$conn->real_escape_string($encodedproductenarray).'",
        "'.$conn->real_escape_string(subtractbtw($subtotaalcheckoutafr)).'",
        "'.$conn->real_escape_string($_SESSION['user']['id']).'"
    );
    UPDATE orders SET order_id = CONCAT(account_id, ".", id)
    WHERE id = LAST_INSERT_ID();
COMMIT;';
$insertordercon = $conn->query($insertorder);

The error says: You have an error in your SQL syntax; it seems the error is around: 'INSERT INTO orders(producten, totaalprijs, account_id) VALUES ( "{\"6' at line 2 which made me think maybe something is going wrong with escaping the json array but when removing that part, I keep getting a syntax error.

What am I missing?

twan
  • 2,450
  • 10
  • 32
  • 92
  • Consider switching to prepared, parametrized queries to [prevent SQL injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) and avoid parameter quoting issues. [Escaping is not safe enough](https://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string). – El_Vanja Feb 04 '21 at 11:52
  • Does this answer your question? [How to start and end transaction in mysqli?](https://stackoverflow.com/questions/12091971/how-to-start-and-end-transaction-in-mysqli) – El_Vanja Feb 04 '21 at 11:53
  • @El_Vanja Does it have to be programmed like that? Why does the SQL at the top of my post work in phpmyadmin but not when used with a PHP db connection? – twan Feb 04 '21 at 11:57
  • PhpMyAdmin is simply a user interface. You have no idea what it actually executes in the background, so there's really no point comparing how it handles your input to what you do in PHP. – El_Vanja Feb 04 '21 at 11:59
  • Does it have to be programmed? Maybe there's a workaround, but why would you want to avoid it? Don't try to hack a library and use it as it wasn't intended. You don't know what else the `mysqli` library does in the background when starting/ending transactions, so you're better off using it as it was designed, rather than hacking it into doing it the way you like it. – El_Vanja Feb 04 '21 at 12:04

0 Answers0