Cannot determine INSERT PDO error. SQLSTATE[42000]: Syntax error or access violation

Question

I'm trying to execute an SQL INSERT but I am getting the error:

SQLSTATE[42000]: Syntax error or access violation: 1064 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) VALUES (example@example.com, hello)' at line 1

The values in the table are: id(auto increment), email and key which are both varchar(255) with the id being (int).

function validate($data)
{
    $data = trim($data);
    $data = stripslashes($data);
    $data = htmlspecialchars($data);
    return $data;
}

$key = 'hello';
$email = validate($_POST['email']);

$insert = $user->recordPasswordReset($email, $key);


public function recordPasswordReset($email, $key)
{
    try 
    {
        $db = DB();
        $sql = "INSERT INTO password_reset(email, key) VALUES (:email, :key)";
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':email', $email, PDO::PARAM_STR);
        $stmt->bindParam(':key', $key, PDO::PARAM_STR);
        $stmt->execute();
    } 
    catch (PDOException $e) 
    {
        echo ($e->getMessage());
    }
}

Can anybody see any errors in my code?

Get rid of that "validate" function. [You shouldn't be using a all-in-one sanitize function.](https://stackoverflow.com/questions/4223980/the-ultimate-clean-secure-function) — Script47, May 01 '18 at 10:57

score 1 · Answer 1 · answered May 01 '18 at 11:01

Firstly, as per the documentation, your field name key is a reserved keyword. You need to use back ticks around it.

Change,

INSERT INTO password_reset(email, key) VALUES (:email, :key)

To,

INSERT INTO password_reset(email, `key`) VALUES (:email, :key)

Secondly, please don't use an all-in-one sanitize function as they generally have some form of flaw in them.

Thirdly, you are using visibility keywords outside of a class.

Cannot determine INSERT PDO error. SQLSTATE[42000]: Syntax error or access violation

1 Answers1