PDO: Show table keys fails to bindparam properly

Question

I want to use this method to get array of keys (primary, foreign, ...) from specified table:

public function getTableKeys($table){
    //OBTAIN TABLE KEYS
    try {
        $conn = $this->db->_pdo;    
        $conn->beginTransaction();

        $query = $this->db->_pdo->prepare('SHOW KEYS FROM :table');
        $query->bindParam(':table', $table, PDO::PARAM_STR);
        $query->execute();
        $keys = $query->fetchAll(PDO::FETCH_ASSOC);

        $conn->commit();
        return $keys;
    }catch (Exception $e) {
        $conn->rollback();
        echo 'Caught exception: ',  $e->getMessage(), "\n";
        return false;
    }
}

The problem is, there is an error thrown:

Caught 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 ''ps_customer'' at line 1

Now if I run SQL manually in PHPMyAdmin, it successfully returns a set of keys. The problem is that it has to be in following format:

SHOW KEYS FROM ps_customers

not in this format (with quotes):

SHOW KEYS FROM "ps_customers"

My question is: How do I bindParam parameter that needs to be inserted into SQL without quotes but is in fast a string (use of PDO::PARAM_INT doesn't work).

Thanks for possible suggestions, guys.

You can't bind table names or column names to prepared parameters. — Ben Fortune, Nov 06 '13 at 12:06
Thx for answer. ... So is there any valid solution to this "problem" other than doing this: `$query = $this->db->_pdo->prepare('SHOW KEYS FROM '.$table);` ? — Dejv, Nov 06 '13 at 12:07
You will have to pass the variable inside the statement, ensuring it's properly sanitized. — Ben Fortune, Nov 06 '13 at 12:09

score 2 · Accepted Answer · answered Nov 06 '13 at 19:46

As Ben said you can't bind table names in prepared statements. You can sanitize the table name by whitelisting.

An array of allowed table names is used to ensure only those on the whitelist can be used.

$table = "table1";//Your table name
$allowed_tables = array('table1', 'table2');//Array of allowed tables to sanatise query
if (in_array($table, $allowed_tables)) {
    getTableKeys($table);
}

The SQL SHOW KEYS FROM $table will only be queried if table1 is in list.

public function getTableKeys($table){
    //OBTAIN TABLE KEYS
    try {
        $conn = $this->db->_pdo;    
        $conn->beginTransaction();
        $query = $this->db->_pdo->prepare('SHOW KEYS FROM $table');
        $query->execute();
        $keys = $query->fetchAll(PDO::FETCH_ASSOC);

        $conn->commit();
        return $keys;
    }catch (Exception $e) {
        $conn->rollback();
        echo 'Caught exception: ',  $e->getMessage(), "\n";
        return false;
    }
}

PDO: Show table keys fails to bindparam properly

1 Answers1