Can PHP PDO statements accept table or column names as parameters?

Question

Why can't I pass a 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 any other safe way to insert table names into SQL queries? By security, I mean I don't want to do

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

Answer 1

To understand why binding table (or column) names does not work, you have to understand how placeholders in prepared statements work: they are not simply replaced with (properly escaped) strings, and executed Generated SQL. In contrast, a DBMS that requires a "prepare" statement will come up with a complete query plan for how to execute the query, including which tables and indexes will be used, which will be the same no matter how you fill in the placeholders. < /p> The plan for

SELECT name FROM my_table WHERE id = :value will be the same as if you replaced :value with a superficially similar SELECT name FROM :table WHERE id = :value cannot be scheduled because the DBMS does not know which table you are actually selecting from.

This is also not a problem that an abstract library like PDO can or should solve, as it defeats 2 key purposes of prepared statements: 1) allowing the database to decide ahead of time how to run the query, and to use the same plan multiple times ; 2) Prevent security issues by separating query logic from variable input.

Answer 2

Table names and column names cannot be replaced with parameters in PDO.

In this case, you only need to manually filter and clean the data. One way to accomplish this is to pass a shorthand argument to a function that will execute the query dynamically, and then use a switch() statement to create a whitelist of valid values ​​or column names for the table name. This way, user input doesn't go directly into the query. For example:

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

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

By not keeping the default case or using a default case that returns an error message, you can ensure that only the values ​​you want to use are used.