Search code examples
phppdo

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"

Solution

  • 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.