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?

5
  • Consider switching to prepared, parametrized queries to prevent SQL injection and avoid parameter quoting issues. Escaping is not safe enough. Commented Feb 4, 2021 at 11:52
  • Does this answer your question? How to start and end transaction in mysqli? Commented Feb 4, 2021 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? Commented Feb 4, 2021 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. Commented Feb 4, 2021 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. Commented Feb 4, 2021 at 12:04

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.