PHP Pdo - Bind Table Name

PHP PDO - Bind table name?

Is it possible to bind a table name?

No.

You have to whitelist table names. I doubt you want to let a user to browse any table from your database.

And you have to format identifiers manually as well.
There is a tag wiki with example. Why not read it first?

Update:
As you can see, PDO turns out to be inconvenient for real life tasks.
So, you have to have a more intelligent abstraction library to handle MySQL queries.
Here is an example using the safeMysql class, which will make your code dramatically shorter:

class form{
    public function __construct($table){
        global $db;
        return $db->getAll("DESCRIBE ?n", $table);
    }
}

2 notes:

• I've removed the second parameter as there is no code in your function that uses it.
• NEVER connect in the class. Use an already opened connection instead. Or you will kill your MySQL server with so many connects.

Exclude implemented version

class form {
    public function __construct($table,$skip = array("id")){
        global $db;
        $data = array();
        $res = $db->query("DESCRIBE ?n", $table);
        while($row = $db->fetch($res)) {
            if (!in_array($row['Field'],$skip)) {
                $data[] = $row;
            }
        }
        return $data;
    }
}

However, such class seldom can be used as intended - there are always a lot of exceptions and manual formatting to make it usable.

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

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.

PDO variable is part of a table name

Prepared statements do not work that way.

The best (safest) way to do this, is to only accept a certain set of values, for example using a switch statement.

switch ($tbl) {
    case "baz":
    case "foo":
        $tbl = "bar".$tbl;
        break;
}

PDO bindValue for table and column

For escape String

From the link:http://php.net/manual/en/pdo.prepare.php

Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information, and helps to prevent SQL injection attacks by eliminating the need to manually quote the parameters.

Prepare values are only for fields.

Regarding dynamic table names

Append it table name to query as u did in second statement.

Example

$pdo = new PDO('mysql:host=localhost;dbname=site;',USER,PASS);

$query = $pdo->prepare("DESCRIBE :table");

$query->bindValue(':table', $table, PDO::PARAM_STR, strlen($table));

$query->execute();

while($field = $query->fetch(PDO::FETCH_NUM)){
    var_dump($field);
    //do something
}

unset($pdo);

PDO with bindParam() returns syntax error

You can't paramatise table names with PDO.

You will need to sanitize the table name and insert into the SQL string.

"UPDATE ".$this->table."
SET `name`=:name, 
`title`=:title,
`description`=:description 
WHERE id=:id";

You can see the quotes its placing around 'category'

PDO: Show table keys fails to bindparam properly

As Ben said you can't bind table names in prepared statements. You can sanitize the table name by whitelisting.

An array of allowed table names is used to ensure only those on the whitelist can be used.

$table = "table1";//Your table name
$allowed_tables = array('table1', 'table2');//Array of allowed tables to sanatise query
if (in_array($table, $allowed_tables)) {
    getTableKeys($table);
}   

The SQL SHOW KEYS FROM $table will only be queried if table1 is in list.

public function getTableKeys($table){
    //OBTAIN TABLE KEYS
    try {
        $conn = $this->db->_pdo;    
        $conn->beginTransaction();
        $query = $this->db->_pdo->prepare('SHOW KEYS FROM $table');
        $query->execute();
        $keys = $query->fetchAll(PDO::FETCH_ASSOC);

        $conn->commit();
        return $keys;
    }catch (Exception $e) {
        $conn->rollback();
        echo 'Caught exception: ',  $e->getMessage(), "\n";
        return false;
    }
}

use a table name that is equal or similar to variable captured in form to do query

You could try to replace spaces with underscores using str_replace

$city = str_replace(' ', '_',$_POST['city']);
$sql = $db->query("SELECT * FROM {$city}");

***please note this code is prone to sql attacks*

PDO query method with bindValue() seems not to be working

Your code:

$ruleValue = "value1";
$input = "value2";
$inputValue = "value3";

$this->_db->query('SELECT * FROM ? WHERE ? = ?', array($ruleValue, $input, $inputValue)

4 lines

insecure

saves state -> a HUGE pitfall you dug for yourself

never works

regular PDO

$stmt = $this->db->prepare('SELECT * FROM value1 WHERE value2 = ?')
$stmt->execute([$value3]);
$results = $stmt->fetchAll();

3 lines

secure

stateless
works

Conclusion: GET RID of this malicious function and use raw PDO

Can I parameterize the table name in a prepared statement?

Short answer to your question is "no".

In the strictest sense, at the database level, prepared statements only allow parameters to be bound for "values" bits of the SQL statement.

One way of thinking of this is "things that can be substituted at runtime execution of the statement without altering its meaning". The table name(s) is not one of those runtime values, as it determines the validity of the SQL statement itself (ie, what column names are valid) and changing it at execution time would potentially alter whether the SQL statement was valid.

At a slightly higher level, even in database interfaces that emulate prepared statement parameter substitution rather than actually send prepared statements to the database, such as PDO, which could conceivably allow you to use a placeholder anywhere (since the placeholder gets replaced before being sent to the database in those systems), the value of the table placeholder would be a string, and enclosed as such within the SQL sent to the database, so SELECT * FROM ? with mytable as the param would actually end up sending SELECT * FROM 'mytable' to the database, which is invalid SQL.

Your best bet is just to continue with

SELECT * FROM {$mytable}

but you absolutely should have a white-list of tables that you check against first if that $mytable is coming from user input.

---

Related Topics