PDO Error with MySQL query

Question

I have been staring at the below code for over an hour any cannot see any issues.

    public function add($data){
        $sql = 'INSERT INTO ' . $this->name . '(fbid, userAccessToken, name, location, story, gender, email, email_md5, referrer, date, use, optin) VALUES (:fbid, :userAccessToken, :name, :location, :story, :gender, :email, :email_md5, :referrer, :date, :use, :optin)';

        $mysqldate = date('Y-m-d G:i:s');
        $result = $this->dbh->prepare($sql);

        if($result->execute(array(
            ':fbid' => $data['fbid'],
            ':userAccessToken' => $data['userAccessToken'],
            ':name' => $data['name'],
            ':location' => $data['location'],
            ':story' => $data['story'],
            ':gender' => $data['gender'],
            ':email' => $data['email'],
            ':email_md5' => md5($data['email']),
            ':referrer' => $data['referrer'],
            ':date' => $mysqldate,
            ':use' => $data['use'],
            ':optin' => $data['optin']
        ))){

            $return = $this->dbh->lastInsertId();  
        }
    }

The error is

PHP Warning: PDOStatement::execute() [pdostatement.execute]: 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 'use, optin) VALUES ('517371547', 'no-auth', 'Shane Jones', 'Manchest' at line 1

When stuck, trust the fact that the START of the error report string points to where MySQL got confused. Here it starts with the word 'use', so that points to the problem right there. — D Mac, Feb 01 '13 at 15:13
@YourCommonSense - agreed that hashing the emails is probably pointless. (unless he's planning to index the field and use it as a pseudo-random sort order for querying the table...?) — SDC, Feb 01 '13 at 15:27

score 8 · Answer 1 · answered Feb 01 '13 at 15:03

8

USE is a reserved word in mySQL.

You need to put it in backticks, or use a different column name.

answered Feb 01 '13 at 15:03

Pekka

442,112
142
972
1,088

1

Thanks for that... My palm has now met my face – Shane Jones Feb 01 '13 at 15:08
1

Generally a good idea to put all your column names in backticks regardless; it prevents any possibility of a facepalm moment. :-) Also a good idea to avoid simple generic words as column names. Make them a bit more meaningful. – SDC Feb 01 '13 at 15:22

score 3 · Answer 2 · answered Feb 01 '13 at 15:05

use is a keyword in MySQL. If you want to use it as a column identifier, enclose it in backticks:

$sql = 'INSERT INTO `' . $this->name . '` ( `fbid`, `userAccessToken`, `name`, `location`, `story`, `gender`, `email`, `email_md5`, `referrer`, `date`, `use`, `optin`) VALUES (:fbid, :userAccessToken, :name, :location, :story, :gender, :email, :email_md5, :referrer, :date, :use, :optin)';

Anyway you should always enclose all identifiers in backticks, to prevent such errors!

score 2 · Answer 3 · answered Feb 01 '13 at 15:06

2

USE is a reserved keyword that must be enclosed with backticks ` (see documentation).

answered Feb 01 '13 at 15:06

Fabian Schmengler

24,155
9
79
111

Your Common Sense · Answer 4 · 2013-02-01T15:23:04.680

1

Your problem comes from the fact that you are building your query manually.
While with whatever sane database abstraction library which will take the duty of building syntactically correct queries for you, the code become as small as few short lines:

public function add($data){
    global $db;
    $data['date'] = date('Y-m-d G:i:s');
    $db->query('INSERT INTO ?n SET ?u',$this->name,$data);
    return $db->insertId();
}

and raise no error on any of nearly hundred reserved words even if you know none of them.

edited Feb 01 '13 at 15:23

answered Feb 01 '13 at 15:09

Your Common Sense

156,878
40
214
345

`+1` for showing another technique. What is `?n` and `?u`? are they parameter place holders too? and why are you using `SET`? by the way I tested `INSERT INTO tb SET..` and it works. – John Woo Feb 01 '13 at 15:13

PDO Error with MySQL query

4 Answers4