mysqli insert php variable not working as expected

Question

I am inserting some data into a fresh table like this:

foreach ($results as $key => $val){
    $sql = "INSERT INTO submission_meta (key, value) VALUES ('".$key."', '".$val."')";  
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

When i run the above in the foreach with variables i get the follwoing error:

Error: INSERT INTO submission_meta (key, value) VALUES ('first_name', 'John')
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 'key, value) VALUES ('first_name', 'John')' at line 1

If i hard code the data i want to insert, the data goes into the database fine.

Any issues with the above?

@u_mulder correct .. then th OP should use the backtics around the column name — ScaisEdge, Nov 26 '20 at 16:41
Please note that the way you're building your query is unsafe. You're open to [SQL injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). You should use [prepared statements](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead. They will also automatically take care of quoting. — El_Vanja, Nov 26 '20 at 16:42

score 1 · Answer 1 · answered Nov 26 '20 at 16:40

KEY is a reserved keyword in MySQL.

https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-K

You can use it as a column name only if you enclose it in back-ticks to delimit it as an identifier.

VALUE is also a keyword, but not a reserved keyword. It's okay to use a non-reserved keyword like VALUE as an identifier without back-ticks.

P.S.: This is not the cause of your syntax error, but please learn to use query parameters instead of interpolating PHP variables into your SQL query.

Here's a good way to write your code (assuming using PDO):

$sql = "INSERT INTO submission_meta (`key`, value) VALUES (?, ?)";
$stmt = $conn->prepare($sql);
foreach ($results as $key => $val) {
    $stmt->execute([$key, $val]);
}

mysqli insert php variable not working as expected

1 Answers1