When send encoded json to mysql reject

Question

I am building some new script for add long text with whitespace to MySQL. I need to send array elements using encode from JSON to MySQL table. encode also working fine. My question is when I tried to insert data MYSQL is reject data.

$day_details['description']=escape_string($_POST['description']);
$data_[]=$day_details;
$mysql_desc=json_encode($data_);

$sql="INSERT INTO 'table' 'description' VALUES ('".$mysql_desc."')";
if($conn->query($sql)){

}

syntax error

You need to mysqli_real_escape_string() (or equivalent) the json — zanderwar, Aug 28 '19 at 03:43
i using mysqli_real_escape_string from escape_string function.when I put whitespace into text that error has shown. — Sasindu Jayampathi, Aug 28 '19 at 03:44
`$sql="INSERT INTO table_name (description) VALUES ....` read the error message carefully it tells you where the error is near — Accountant م, Aug 28 '19 at 03:45
You'll likely need to escape `$mysql_desc` regardless of escaping the description (in fact that's all you should be escaping) — zanderwar, Aug 28 '19 at 03:48

Nick · Accepted Answer · 2019-08-28T04:06:36.940

4

You have a few issues:

Table and column names should not be enclosed in single quotes, but backticks if required (see this Q&A).
The column name should be in parentheses.
Finally, you need to escape the data after converting to JSON. But better yet, use a prepared query, then you don't need to escape the data at all:

    $day_details['description']=$_POST['description'];
    $data_[]=$day_details;
    $mysql_desc=json_encode($data_);

    $sql="INSERT INTO `table` (`description`) VALUES (?)";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('s', $mysql_desc);
    if($stmt->execute()){
        // do something
    }

edited Aug 28 '19 at 04:06

answered Aug 28 '19 at 03:53

Nick

138,499
22
57
95

i thing the case is \\r\\ – Sasindu Jayampathi Aug 28 '19 at 04:01
1

@SasinduJayampathi as long as you use a prepared statement you shouldn't have issues with special characters in the JSON. Note that you **don't** need to call your `escape_string` routine at all. – Nick Aug 28 '19 at 04:03
let me try with that – Sasindu Jayampathi Aug 28 '19 at 04:06
Thanks for everyone.this is solution. – Sasindu Jayampathi Aug 31 '19 at 05:30

When send encoded json to mysql reject

1 Answers1