PDO variable is part of a table name

Question

i am trying to bind a variable which is part of a table name when using PDO binding to prevent SQL injection.

my query is similar to this:

    select * from user_:id_user.kb_:id_kbase "."_frequencies"

which must result into

    select * from user_1.kb_123_frequency

where user_1.kb_123_frequency is the intended table name. However, when the parameters get bound in:

    $st->bindParam(':id_user', $id_user, PDO::PARAM_INT);
    $sth->bindParam(':id_kbase', $id_kbase, PDO::PARAM_INT);
    $sth->execute();

it fails to bind and throws the bellow error:

Exception has occurred.

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: :id_kbase

i believe this is because of me trying to concatenate "kb_:id_kbase" with "_frequencies" but :kb_kbase is a parameter.

so my question is: can you pass a table name as a parameter and if so How to do the binding when a parameter is part of a table name?

your help is much appreciated

Answer 1

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