Search code examples
mysqljsonprepared-statementphp-7

Prepared statements in SQL query while saving JSON data in PHP

I have simple php code for parsing some data from a JSON file and saving them into mysql database. But it's showing following error:

Parse error: syntax error, unexpected 'my_name' (T_STRING) in C:\xampp\htdocs\mycode.php on line 25

My php code is following: 

$sql = "INSERT INTO table1 (my_name, hobby, other) VALUES ($row['my_name'], $row['hobby'], $row['other'])"; //line 25

mysqli_query ($conn, $sql);

Why is it showing syntax error? Is there anything wrong in the query?

Solution

  • You need to enclose interpolated placeholders in curly braces, i.e. $row['my_name'] -> {$row['my_name']}: 

        $sql = "INSERT INTO table1 (my_name, hobby, other) VALUES ({$row['my_name']}, {$row['hobby']}, {$row['other']})";

    This addresses only PHP syntax. The SQL syntax error you get now is the next issue. The simplest thing to "fix" this would be to include additional apostrophes around placeholders, i.e. 

        $sql = "INSERT INTO table1 (my_name, hobby, other) VALUES ('{$row['my_name']}', '{$row['hobby']}', '{$row['other']}')";

    BUT DON'T DO THAT, since this code is an example of a classic SQL Injection vulnerability.

    Consider using a prepared statement instead — this eliminates the PHP's string interpolation altogether.