PHP PDO query troubleshoot

Question

why will the following query not work?

$q=$conn->prepare("SELECT GUID FROM :table WHERE URL = :url AND Status = 1 LIMIT 0,1");
$q->execute(array(':table'=>'su_prm_'.$url_params['leading_url'],':url'=>$url_params['trailing_url']));
$r=$q->rowCount();
//returns 0, should return 1. Querying from the console (with quotes) returns 1.

I thought perhaps there was an issue whereby the variable strings were not being parsed as strings, so tried

$q->bindParam(1, 'su_prm_'.$url_params['leading_url'], PDO::PARAM_STR);
$q->bindParam(2, $url_params['trailing_url'], PDO::PARAM_STR);

also tried placing the variables into new, standalone vars ($str = 'su_prm_'.$url_params['leading_url']) and running the query that way. No luck. What am I missing?

EDIT: btw, the strings are 'stream' and 'general'. Nothing fancy...

Because of this `FROM :table` most probably. I've never seen that type of query, maybe something I don't know yet. — Funk Forty Niner, Oct 14 '13 at 15:27
You will need to specify :table using bindParam rather than via the execute method. It's the same with limit/sort by params :( — Gavin, Oct 14 '13 at 15:28
If I specify the table, the where clause should be fine though, right? — Eamonn, Oct 14 '13 at 15:28
Technically `PDO::rowCount()` is approximately equivalent to `mysqli_affected_rows()` in that it's for INSERT, UPDATE, DELETE type queries rather than SELECT so may or may not return the correct value (depending on the database engine) when used to determine the number of rows in a SELECT result. — CD001, Oct 14 '13 at 15:29
Try using `$q->bindValue(':Table', 'su_prm_' . $url_params['leading_url'], PDO::PARAM_STR);` instead of `bindParam`. — Gavin, Oct 14 '13 at 15:31

score 2 · Accepted Answer · edited May 23 '17 at 12:21

Parameters can't bind to the table name (or field names). You must specify it without a bound parameter. If you're accepting the table name through the URL as a query string parameter then be sure to have a whitelist to check against, to sanitize the input.

$table = 'su_prm_'.$url_params['leading_url'];
$whitelist = array(
    'su_prm_A',
    'su_prm_B',
    'su_prm_C'
);

if(!in_array($table, $whitelist))
{
    $table = 'su_prm_A'; // default
    // show message to user - bad table name?
}

$q=$conn->prepare("SELECT GUID FROM $table WHERE URL = :url AND Status = 1 LIMIT 0,1");

For counting the number of rows, see this answer.

Yeah, I should have figured the query couldnt be prepared this way, I just couldn't see it! Thanks for the help. (will accept when allowed) — Eamonn, Oct 14 '13 at 15:32

PHP PDO query troubleshoot

1 Answers1