Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL syntax error at line 1 and backtick offering odd solution

This query

$email = $mysqli->real_escape_string($_POST['email']); // User's email.
$key = RandomString(128); // A random string of 128 characters.
$newTime = $_SERVER['REQUEST_TIME'] + 1800; // Expiration timer.
$queries[] = "INSERT INTO verification (
        email, 
        key, 
        time
    ) VALUES (
        '$email', 
        '$key', 
        $newTime
    )";
$errors = false;

$mysqli->autocommit(false);
foreach ($queries as $query) {
    if (!$mysqli->query($query)) {
        $errors = true;
    }
}

is giving me the following 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 'key, time) VALUES ('[email protected]', 'e1e4091197640bae0a4588b8666e87b6b' at line 1.

But the query above works, just by adding a few backticks (grave accent):

$queries[] = "INSERT INTO verification (
        `email`, 
        `key`, 
        `time`
    ) VALUES (
        '$email', 
        '$key', 
        $newTime
    )";

Can anyone explain how this change would fix the issue?

like image 313
Aram Avatar asked Dec 21 '22 10:12

Aram


2 Answers

time and key are reserved words. If you're going to use them for a column name, which is a bad idea, they requires backticks to escape them.

like image 155
John Conde Avatar answered Feb 13 '23 02:02

John Conde


Backticks are needed in MySQL whenever the name of an identifier is a reserved word. In your case, KEY is a reserved word and will cause syntax errors unless you enclose it like that. It's normally a good idea to avoid creating names like that.

You can find a list of reserved words here.

like image 33
Brian Hooper Avatar answered Feb 13 '23 04:02

Brian Hooper