Why does backticks fails in PDO query?

Question

I have a table with hyphens in the name, and I can't change the table name so I thought backticks would help.

Unfortunally for me it failed, some googling did'nt give me any answers. How can I solve this?

ex:

    $stmt = $this->_dbh->prepare(
        'UPDATE `:table`
        SET status = NOT status
        WHERE id=:id;');


    $stmt->bindParam(':table',$this->_settings['table'], PDO::PARAM_STR);
    $stmt->bindParam(':id',$data['id'], PDO::PARAM_INT);
    if( $stmt->execute() ){
        return 'Success';
    }
    else{
        $this->_log( $stmt->errorInfo() );
        return 'Action failed.';
    }

In the log, with backticks:

13:25:18    42S02
1146
Table 'db_name.'table-name'' doesn't exist

Without backticks:

13:38:14    42000
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 ''table-name'
            SET status = NOT status
            WHERE id='1'' at line 1

Because you can't bind table/column names.... and string bindvars are quote wrapped as part of the binding — Mark Baker, Feb 12 '15 at 12:42
possible duplicate: http://stackoverflow.com/q/15182910/3933332 — Rizier123, Feb 12 '15 at 12:43
Then @MarkBaker is there a best practice way to use variables for table or column names? Have solved the problem btw. Thank you! — Jorgeuos, Feb 12 '15 at 12:54
See my answer, though personally I use an ORM these days for most of my database work — Mark Baker, Feb 12 '15 at 12:55

score 4 · Accepted Answer · answered Feb 12 '15 at 12:53

If you need to inject the table name, you can't do it as a bind variable; as long as the value has been whitelisted, you can use

$stmt = $this->_dbh->prepare(
        sprint(
            'UPDATE `%s`
                SET status = NOT status
              WHERE id=:id;',
            $this->_settings['table']
        )
    );


$stmt->bindParam(':id',$data['id'], PDO::PARAM_INT);

Why does backticks fails in PDO query?

1 Answers1

Linked