How to Bind value of table name in perl?

Question

Here I am trying to bind table name for particular query:

$sth = $dbh->prepare("SELECT id FROM ? WHERE service_id = ?");

and the execute statement:

$sth->execute($table_name,$service_id);

It gives a SQL error, why so?

Is there any way that I can bind table name to particular query?

Answer 1

You can use query parameters in place of a literal value in an SQL expression.

I.e., in a place where you would normally use a quoted string literal or a quoted date literal or a number.

Parameters cannot be used for table names, column names, lists of values (like an IN clause), SQL keywords, or SQL expressions.

To make the name of the table dynamic, you have to interpolate it into the SQL query string before you prepare it:

my $table_name_lit = $dbh->quote_identifier($table_name);
my $sth = $dbh->prepare("SELECT id FROM $table_name_lit WHERE service_id = ?");
$sth->execute($service_id);

Take care that $table_name doesn't contain any untrusted content. The best way is to compare $table_name to a list of known table names. In other words, use a fixed list of tables as a whitelist. If $table_name doesn't match any of your table names, then use a default table name.

Answer 2

The @Karwin response is ok, also the note about the sql injection problem. You should check the $table_name contains a valid table name before interpolate the SELECT string. For example, if you use MySQL, you can get the current table names set at DB with:

my @tables = @{ $dbh->selectcol_arrayref('SHOW TABLES') };

and it is easy with "grep" check if $table_name contains a right value. Also it is possible (and database independent) get the table catalog with tables method of DBI.