How to use variable for table name in DBI query?

Question

How to use variable for table name in DBI query? I know how to use placeholders as part of the where clause, but how do I do this for the table name?

I would like to do something like this:

 my $table_name='table1';
 my $query = $dbh_cgi->prepare("select * from ?");
 $query->execute($table_name);

So far I am getting a MySQL syntax error because the DBI adds quotes around the name, table1.

Answer 1

One of the limitations of placeholders is that they cannot be used for table names. Instead of using a placeholder, you can use a variable. To ensure that the contents of a variable are safe, use quote_identifier(), for example:

my $table_name='table1'; #-- Or get this from somewhere else
my $safe_table_name =  $dbh_cgi->quote_identifier($table_name);
my $query = $dbh_cgi->prepare("select * from $safe_table_name");
$query->execute();