PDO bindValue for table and column

Question

Ok so I just found out that I can't use placeholders for table names and columns

$table = 'users';

$stmt = $db->prepare('SELECT * from ?');
$stmt->bindValue(1, $rable, ??);

So what is really an alternative to having dynamic table names?

$stmt = $db->prepare('SELECT * from '.$table);

This would be vulnerable. Is there an escape_string method for PDO? I went through the manual but didn't seem to catch one. All I found was quote but that doesn't work for tables and columns. Is there any way I can securely implement this functionality, or will I have to switch back to using mysqli?

@user2486495 But that way the query is vulnerable to injections? — php_nub_qq, Dec 07 '13 at 17:51

user2486495 · Accepted Answer · 2013-12-07T18:10:36.590

0

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

edited Dec 07 '13 at 18:10

answered Dec 07 '13 at 17:42

user2486495

1,709
11
19

But that way the query is vulnerable to injections? – php_nub_qq Dec 07 '13 at 17:51
use mysql_real_escape_string($table); – user2486495 Dec 07 '13 at 17:56
Try bindValue() method? Check above answer? – user2486495 Dec 07 '13 at 18:09

score 0 · Answer 2 · answered Dec 07 '13 at 17:42

0

Bindable Marks (?) or Bindable named Marks (:foo) couldn't appear as table-names or (pseudo-dynamic-) fieldnames. Both are limited to field-values.

You should avoid dynamic tables inside of your application. Just normalize your database to an more agile and intelligent structure.

answered Dec 07 '13 at 17:42

nihylum

542
3
7

My databases are normalized, this is an extended functionality for an input data validator. – php_nub_qq Dec 07 '13 at 17:50
okay, then you've to use string concatination ( your 2nd codebox ). You may sanitize your inbound tablename while checking the availability of the table before: SHOW TABLES FROM yourdb LIKE 'tablename'; – nihylum Dec 07 '13 at 17:58

score 0 · Answer 3 · answered Dec 07 '13 at 18:18

If you use ticks, then you can simply replace ticks in the user input, and you should be fine:

$column = 'foo';
$table = 'bar';

$query = 'SELECT ' . $column . ' FROM ' . $table; // Insecure!

$query = 'SELECT `' . str_replace('`', '', $column) . '` FROM `' . str_replace('`', '', $table) . '`'; // Not insecure

PDO bindValue for table and column

3 Answers3