Table Name as Parameter Using Pdo/MySQL Prepared Statement

Table name as parameter using PDO/MySQL prepared statement

Table and Column names cannot be replaced by parameters in PDO.
see Can PHP PDO Statements accept the table or column name as parameter?

Can PHP PDO Statements accept the table or column name as parameter?

Table and Column names CANNOT be replaced by parameters in PDO.

In that case you will simply want to filter and sanitize the data manually. One way to do this is to pass in shorthand parameters to the function that will execute the query dynamically and then use a switch() statement to create a white list of valid values to be used for the table name or column name. That way no user input ever goes directly into the query. So for example:

function buildQuery( $get_var ) 
{
    switch($get_var)
    {
        case 1:
            $tbl = 'users';
            break;
    }

    $sql = "SELECT * FROM $tbl";
}

By leaving no default case or using a default case that returns an error message you ensure that only values that you want used get used.

Can I parameterize the table name in a prepared statement?

Short answer to your question is "no".

In the strictest sense, at the database level, prepared statements only allow parameters to be bound for "values" bits of the SQL statement.

One way of thinking of this is "things that can be substituted at runtime execution of the statement without altering its meaning". The table name(s) is not one of those runtime values, as it determines the validity of the SQL statement itself (ie, what column names are valid) and changing it at execution time would potentially alter whether the SQL statement was valid.

At a slightly higher level, even in database interfaces that emulate prepared statement parameter substitution rather than actually send prepared statements to the database, such as PDO, which could conceivably allow you to use a placeholder anywhere (since the placeholder gets replaced before being sent to the database in those systems), the value of the table placeholder would be a string, and enclosed as such within the SQL sent to the database, so SELECT * FROM ? with mytable as the param would actually end up sending SELECT * FROM 'mytable' to the database, which is invalid SQL.

Your best bet is just to continue with

SELECT * FROM {$mytable}

but you absolutely should have a white-list of tables that you check against first if that $mytable is coming from user input.

MySQL PDO Name-Value Prepared Statement Using Last Parameter Only

Thanks for all the help everybody!

I went with Michael's solution, but tested Ryan's too.

i.e.

Update to note as solved. Using...

$stmt->execute($params); // scrap the foreach nonsense...

bindValue() rather than bindParam() is also appropriate.

To wrap things up, as per Ryan's comment, I'm pushing an answer out.

Thanks again!

Dynamic table name setting with PDO

Do not confuse PHP and SQL.

You just used SQL quotes in PHP. While you have to use SQL quotes in SQL.

Linked answer is now fixed, so, I am closing this as a duplicate.

PHP PDO - Bind table name?

Is it possible to bind a table name?

No.

You have to whitelist table names. I doubt you want to let a user to browse any table from your database.

Given you are using a class, it will be no-brainer to add a table name as a property. It will be simple, elegant and safe. Create an abstract parent class first

abstract class abstractTable {
    private $table;
    private $db;

    public function __construct($pdo){
        $this->db = $pdo;
    }
    public function describe() {
        return $db->query("DESCRIBE `$this->table`")->fetchAll();
    }
}

Then create a specific class for your table

class someTable extends abstractTable {
    private $table = 'sometable';
}

and so you will be able to get the required list of columns

$pdo = new PDO(...);
$table = new someTable($pdo);
$fields = $table->describe();

simple, concise, powerful, safe.

---

Related Topics