PHP Json to MySql (opencart)

Question

I have small script that reeds cell from Database and anfter write to it. But it cause Error 1064. It looks like this:

public function update($cart_id, $quantity) {
    $this->db->query("UPDATE " . DB_PREFIX . "cart SET quantity = '" . (int)$quantity . "' WHERE cart_id = '" . (int)$cart_id . "' AND customer_id = '" . (int)$this->customer->getId() . "' AND session_id = '" . $this->db->escape($this->session->getId()) . "'");
    //starts here
    $cart_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "cart WHERE cart_id = '" . (int)$cart_id. "' AND session_id = '" . $this->db->escape($this->session->getId()) . "'");
    $option= ($cart_query->row['option']);//success
    $this->db->query("UPDATE " . DB_PREFIX . "cart SET option = '" . (string)$option . "' WHERE cart_id = '" . (int)$cart_id . "' AND customer_id = '" . (int)$this->customer->getId() . "' AND session_id = '" . $this->db->escape($this->session->getId()) . "'");//error
}

Thats What i see in log

2016-09-18 20:43:06 - PHP Notice:  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 'option = '{"options":{"product_id":"176","colors":["000000"],"print":{"colors":{' at line 1<br />Error No: 1064<br />UPDATE oc_cart SET option = '{"options":{"product_id":"176","colors":["000000"],"print":{"colors":{"front":["FF0000"]},"elements":{"front":[{"width":"52.9375","height":"25.0938","type":"text"}]}},"attributes":{"sizememos":"0"},"attribute":[["0","0"]],"print_type":"screen","quantity":"0","design":{"vectors":{"front":[{"type":"text","width":"54.9375px","height":"27.0938px","top":"151px","left":"86px","zIndex":"1","svg":"<svg width="54.9375" height="27.09375" viewBox="0 0 54.9375 27.09375" xmlns="http:\/\/www.w3.org\/2000\/svg" xmlns:xlink="http:\/\/www.w3.org\/1999\/xlink"><g id="0.15760551612925844"><text fill="#FF0000" stroke="none" stroke-width="0" stroke-linecap="round" stroke-linejoin="round" x="" y="" text-anchor="start" font-size="24px" font-family="arial" data-textcurve="1" data-itemzoom="1 1" data-textspacing="0"><textPath xmlns:xlink="http:\/\/www.w3.org\/1999\/xlink" xlink:href="http:\/\/138.68.62.219\/Buy-Hanes-T-shirt-PC61LS#textPath-item-0"><tspan dy="0">Hello<\/tspan><\/textPath><\/text><\/g><defs><path id="textPath-item-0" d="M 0.125 22.117808976867764 A 3093.9720937064453 3093.9720937064453 0 0 1 54.124314613414626 22.117808976867764"><\/path><\/defs><\/svg>","rotate":"0","text":"Hello","color":"#FF0000","fontFamily":"arial","align":"center","outlineC":"none","outlineW":"0"}]},"images":{"front":"cart-designes\/2016\/09\/\/cart-front-1474230421.png","back":"cart-designes\/2016\/09\/\/cart-back-1474230421.png"}},"fonts":""}}' WHERE cart_id = '387' AND customer_id = '0' AND session_id = 'kkfj9svfssdnsph8pf8i5atjn3' in /var/www/html/system/library/db/mysqli.php on line 41

What the reason? i'm just saving data that already in this cell. option column is UTF-8 , LongText

It seems that the problem is that you have a string like `'outerThing:{innerString:"I am a string, but there is a problem because right here -> "*I am using double quotes again*", and that is a big problem"}'` The part surrounded by stars is not a part of `innerString`, and that will cause some errors as the computer has no idea what to do with it. If you can place a backslash before each of the inner double quotes, that should fix the problem. — Howzieky, Sep 18 '16 at 21:12
@Howzieky i've tried `addslashes` `json_decode json_encode` `htmlspecialchars` and so on.... it succesfully read decode and encode JSON, maybe there is better way to save it, or query string limit... — tttaaabbb, Sep 18 '16 at 22:54

score 0 · Answer 1 · answered Sep 18 '16 at 21:54

Error 1064 points to a Syntax error on your MySQL query, It seems there are some quotations not well escaped on your JSON string, to prove that try to fix a value of option to some short value and call your function again.

The best practice is to use parameterized queries, these will save you from all the escaping tasks and most importantly offer some protection from SQL injection

This is just an example of how your query will look like:

$sql="UPDATE table SET opton=:option WHERE cart_id=:id AND customer_id=:customer_id and session_id=:session_id";
$parameters = array(
                'option'=>$option,
                'cart_id'=>$cart_id,
                'customer_id'=>$customer_id,
                'session_id'=>$session_id
            );

$sql = $this->db->prepare($sql);
$sql->execute($parameters);

You can find more details on the official php documentation

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php http://php.net/manual/en/pdo.prepared-statements.php

Happy Coding friend:)

score 0 · Answer 2 · answered Sep 20 '16 at 17:26

It doesn't work becouse of OPTION coz it's part of MySql syntaxis. This is work

$customer_id=(int)$this->customer->getId();
    $session_id=$this->db->escape($this->session->getId());
    $this->db->query("UPDATE " . DB_PREFIX . "cart SET `option` = '" . (int)$quantity . "' WHERE cart_id = '" . (int)$cart_id . "' AND customer_id = '" . $customer_id . "' AND session_id = '" . $session_id . "'");

PHP Json to MySql (opencart)

2 Answers2