PHP PDO: Syntax error or access violation (Help)

Question

I have problem with PDO and can't find the solution:

My function:

public static function create($position, $name, $mail, $mailtext, $confirmed, $key, $formid) {
    global $database;

    try {
        $pdo_result = $database->prepare('INSERT INTO Form (Position, Name, Mail, MailText, Confirmed, Key, Form_idForm) VALUES(:Position, :Name, :Mail, :MailText, :Confirmed, :Key, :Form_idForm)');
        $pdo_result->execute(array(
            ':Position' => $position,
            ':Name' => $name,
            ':Mail' => $mail,
            ':MailText' => $mailtext,
            ':Confirmed' => $confirmed,
            ':Key' => $key,
            ':Form_idForm' => $formid
        ));

        return $database->lastInsertId();
    } catch(PDOException $e) {
        Page::error('Error: Message:', $e->getMessage());
    }

    return null;
}

Exception: 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, Form_idForm) VALUES('Position', 'Name', 'Mail', 'MailText', '1', 'keeey', '' at line 1

score 5 · Accepted Answer · answered May 19 '13 at 20:28

5

You're using reserved words in your field names.

Try escaping your INSERT-statement within your $database->prepare-construct like this:

INSERT INTO Form (
    `Position`, `Name`, `Mail`, `MailText`, 
    `Confirmed`, `Key`, `Form_idForm`)  ....

answered May 19 '13 at 20:28

Bjoern

15,934
4
43
48

score 3 · Answer 2 · answered May 19 '13 at 20:26

3

Eventually because of the use of Key, a preserved MySQL word. Use another word instead or use backtick `.

by: https://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

answered May 19 '13 at 20:26

ultima_rat0

290
2
8

score 2 · Answer 3 · answered May 19 '13 at 20:30

You can still use key if you want to. Just wrap it with backtick,

INSERT INTO Form (
    `Position`, `Name`, `Mail`, `MailText`, 
    `Confirmed`, `Key`, `Form_idForm`)

but as an advise, refrain from using any reserved keyword to avoid future problems. :)

MySQL Reserved Keywords List

score 1 · Answer 4 · edited May 19 '13 at 20:30

key is a keyword in SQL. You can therefore not use it as a column name.

You should rename it, else it will cause issues like the one you are currently experiencing.. however you can "escape" the field names like so which will also rectify this issue:

INSERT INTO Form (`Position`, `Name`, `Mail`, `MailText`, `Confirmed`, `Key`, `Form_idForm`) VALUES(:Position, :Name, :Mail, :MailText, :Confirmed, :Key, :Form_idForm)');

You can find a full list of reserved words here: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

PHP PDO: Syntax error or access violation (Help)

4 Answers4