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

Jrgns_6681 0
Viewed 94271

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"
pdo php
8 Answers

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.

gabriel1836_201780

Using the former isn't inherently more safe than the latter, you need to sanitize the input whether it's part of a parameter array or a simple variable. So I don't see anything wrong with using the latter form with $table, provided you make sure that the content of $table is safe (alphanum plus underscores?) before using it.

Adam_Bellaire_216320

(Late answer, consult my side note).

The same rule applies when trying to create a "database".

You cannot use a prepared statement to bind a database.

I.e.:

CREATE DATABASE IF NOT EXISTS :database

will not work. Use a safelist instead.

Side note: I added this answer (as a community wiki) because it often used to close questions with, where some people posted questions similar to this in trying to bind a database and not a table and/or column.

user_14157240

Short answer is NO you cannot use dynamic table name, field names, etc in the Prepared execute statement with PDO because it adds quotes to them which will break the query. But if you can sanitize them, then you can safely plop them right in the query itself just like you would with MySQLi anyway.

The correct way to do this is with mysqli's mysqli_real_escape_string() function because the mysql_real_escape_string was removed from PHP hastily without any consideration into how that affects dynamic structure applications.

$unsanitized_table_name = "users' OR '1'='1"; //SQL Injection attempt
$sanitized_table_name = sanitize_input($unsanitized_table_name);

$stmt = $dbh->prepare("SELECT * FROM {$unsanitized_table_name} WHERE 1"); //<--- REALLY bad idea
$stmt = $dbh->prepare("SELECT * FROM {$sanitized_table_name} WHERE 1"); //<--- Not ideal but hey, at least you're safe.

//PDO Cant sanitize everything so we limp along with mysqli instead
function sanitize_input($string)
{
   $mysqli = new mysqli("localhost","UsahName","Passerrrd");
   $string = $mysqli->real_escape_string($string);

   return $string;
}
user_95882760
Related