PHP PDO custom sql query preparation

Question

I'm having a little problem getting a sql query with prepare on PDO, I have this code:

$portfolio = $db->prepare("SELECT * FROM `news`, `:sub` WHERE `news`.`id` = `:sub`.`id_news` AND `page` = `:under` ORDER BY `date` DESC LIMIT :start, :limit");
$portfolio->bindParam(':under', $_GET['under'], PDO::PARAM_STR);
$portfolio->bindParam(':sub', $_GET['sub'], PDO::PARAM_STR);
$portfolio->bindParam(':start', $start, PDO::PARAM_INT);
$portfolio->bindParam(':limit', $limit, PDO::PARAM_INT);
$portfolio->execute();

But this doesn't give any value and my DB has the values correct, any one knows why this doesn't work? PS: var $start and $limit are fine, no problem with it cuz it's pagination script, that work very fine in all the pages.

For exemple i'm in the url: mysite.com/index.php?sub=vid&under=info

so the query should be like this:

"SELECT * FROM `news`, `vid` WHERE `news`.`id` = `vid`.`id_news` AND `page` = `info` ORDER BY `date` DESC LIMIT 0, 10"

So for what i understood having this code before should work and still be safe right?

switch($_GET['sub']){
    case "vid":
        $table = "vid";
        break;
    case "img":
        $table = "img";
        break;
}
$portfolio = $db->prepare("SELECT * FROM `news`, `$table` WHERE `news`.`id` = `$table`.`id_news` AND `page` = :under ORDER BY `date` DESC LIMIT :start, :limit");

Why do you have a **dynamically linked table?** Something awful with your design, you know. — Your Common Sense, Sep 30 '13 at 15:58
You cannot bind table names with PDO; check http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter — newfurniturey, Sep 30 '13 at 15:59
It's not possible to have dynamic table names, you'll need to go a different way making those dynamic (but as Your Common Sense says above, it may be preferable to look into your database structure and why you need it this way in the first place.) — Pekka, Sep 30 '13 at 15:59

Bill Karwin · Accepted Answer · 2013-09-30T16:14:03.753

2

You can't use query parameter placeholders for table names or column names.

Use query parameters only to substitute for a literal value in an expression. I.e. a quoted string, quoted date, or numeric value.

Also, even if you are using a parameter for a string or date, the parameter doesn't go inside quotes.

To make table names or column names dynamic, you have to interpolate application variables into your SQL string before you submit the string to prepare().

But be careful to validate user input (e.g. $_GET variables) so you avoid SQL injection. For instance, test the input against a list of known legitimate table names.

Example:

$subtables = array(
 "DEFAULT" => "text",
 "text" => "text",
 "vid" => "vid",
 "pic" => "pic"
);

// if the key exists, use the table name, else use the default table name
$subtable = $subtables[ $_GET["sub"] ] ?: $subtables[ "DEFAULT" ];

// now $subtable is effectively whitelisted, and it is safe to use
// without risk of SQL injection

$portfolio = $db->prepare("SELECT * 
  FROM news, `$subtable` AS sub 
  WHERE news.id = sub.id_news 
    AND page = :under 
  ORDER BY date DESC LIMIT :start, :limit");

edited Sep 30 '13 at 16:14

answered Sep 30 '13 at 15:59

Bill Karwin

538,548
86
673
828

Bill can you check if my final code should work and still be safe? – Hugo Alves Sep 30 '13 at 16:45
Yes, you've whitelisted the table name. But you still have the `:under` parameter inside quotes, which won't work. I don't know if you intended 'info' to be a column name or a string literal. – Bill Karwin Sep 30 '13 at 16:47
@HugoAlves, one more comment about your code: if $_GET["sub"] is *neither* "vid" nor "img", then $table is not set. What happens to the SQL? You should always test for cases where users access your page directly, or if they enter an URL manually, not only when they click well-formatted links from your other pages. – Bill Karwin Oct 01 '13 at 13:50
That is protected either in the php code that shows a not exists page (404 error), either on the .htaccess – Hugo Alves Nov 16 '13 at 18:04

score -1 · Answer 2 · edited May 23 '17 at 11:57

-1

You can't use parameters for the names of tables and table object (i.e fields). See this question where this is covered.

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

edited May 23 '17 at 11:57

Community

1
1

answered Sep 30 '13 at 16:01

Mike Brant

70,514
10
99
103

score -2 · Answer 3 · answered Sep 30 '13 at 16:00

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );

are two magical lines that will solve all your minor problems after you fix the BIGGEST one - a dynamically linked table.

What it have to be is a single table where "sub" is a field name to distinguish a category

SELECT * FROM news n, subnews s 
WHERE n.id = id_news AND s.sub =:sub AND `page` = :under 
ORDER BY `date` DESC LIMIT :start, :limit

Also you have to quit that habit of wrapping in backticks everything that moves.

PHP PDO custom sql query preparation

3 Answers3