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

Why can't I pass the table name to a prepared PDO statement?

$stmt = $dbh->prepare('SELECT * FROM :table WHERE 1');
if ($stmt->execute(array(':table' => 'users'))) {
    var_dump($stmt->fetchAll());
}

Is there another safe way to insert a table name into a SQL query? With safe, I mean that I don't want to do

$sql = "SELECT * FROM $table WHERE 1"

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.

To understand why binding a table (or column) name doesn't work, you have to understand how the placeholders in prepared statements work: they are not simply substituted in as (suitably escaped) strings, and the resulting SQL executed. Instead, a DBMS asked to "prepare" a statement comes up with a complete query plan for how it would execute that query, including which tables and indexes it would use, which will be the same regardless of how you fill in the placeholders.

The plan for SELECT name FROM my_table WHERE id = :value will be the same whatever you substitute for :value, but the seemingly similar SELECT name FROM :table WHERE id = :value cannot be planned, because the DBMS has no idea what table you're actually going to select from.

This is not something an abstraction library like PDO can or should work around, either, since it would defeat the 2 key purposes of prepared statements: 1) to allow the database to decide in advance how a query will be run, and use the same plan multiple times; and 2) to prevent security issues by separating the logic of the query from the variable input.

I see this is an old post, but I found it useful and thought I'd share a solution similar to what @kzqai suggested:

I have a function that receives two parameters like...

function getTableInfo($inTableName, $inColumnName) {
    ....
}

Inside I check against arrays I've set up to make sure only tables and columns with "blessed" tables are accessible:

$allowed_tables_array = array('tblTheTable');
$allowed_columns_array['tblTheTable'] = array('the_col_to_check');

Then the PHP check before running PDO looks like...

if(in_array($inTableName, $allowed_tables_array) && in_array($inColumnName,$allowed_columns_array[$inTableName]))
{
    $sql = "SELECT $inColumnName AS columnInfo
            FROM $inTableName";
    $stmt = $pdo->prepare($sql); 
    $stmt->execute();
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
}

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

Related

Recent Posts