Categories
pdo php

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

264

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"

0

    235

    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.

    5

    • 23

      +1 for whitelisting options instead of using any kind of dynamic method. Another alternative might be mapping acceptable table names to an array with keys that correspond to the potential user input (e.g. array('u'=>'users', 't'=>'table', 'n'=>'nonsensitive_data') etc.)

      – Kzqai

      Dec 22, 2011 at 18:05


    • 4

      Reading over this, it occurs to me that the example here generates invalid SQL for bad input, because it has no default. If using this pattern, you should either label one of your cases as default, or add an explicit error case such as default: throw new InvalidArgumentException;

      – IMSoP

      Oct 22, 2015 at 9:34

    • 3

      I was thinking a simple if ( in_array( $tbl, ['users','products',...] ) { $sql = "SELECT * FROM $tbl"; }. Thanks for the idea.

      – Phil Tune

      Mar 2, 2016 at 17:20


    • 3

      I miss mysql_real_escape_string(). Maybe here I can say it without someone jumping in and saying “But you don’t need it with PDO”

      – Rolf

      Oct 20, 2018 at 8:39

    • 1

      The other issue is that dynamic table names break SQL inspection.

      – Acyra

      Jul 20, 2019 at 11:02

    162

    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.

    2

    • 1

      True, but does not account for PDO’s prepare statement emulation (which could conceivably parameterise SQL object identifiers, albeit I still agree that it probably shouldn’t).

      – eggyal

      Dec 27, 2013 at 19:40

    • 1

      @eggyal I guess the emulation is aimed at making standard functionality work on all DBMS flavours, rather than adding completely new functionality. A placeholder for identifiers would also need a distinct syntax not directly supported by any DBMS. PDO is quite a low-level wrapper, and doesn’t for instance offer and SQL generation for TOP/LIMIT/OFFSET clauses, so this would be a bit out of place as a feature.

      – IMSoP

      Jan 1, 2014 at 19:53


    14

    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);
    }
    

    3