How to bind the value of a table name in perl?
Here I am trying to bind the table name for a specific query:
$sth = $dbh->prepare("SELECT id FROM ? WHERE service_id = ?");
and the execute statement:
$sth->execute($table_name,$service_id);
It is giving SQL error, why is that?
Is there a way to bind the table name to a specific query?
source to share
You can use query parameters instead of literal values in the SQL expression.
Ie, in a place where you would normally use a quoted string literal or literal with quotes date or number.
Parameters cannot be used for table names, column names, lists of values (for example, IN clauses), SQL words, or SQL expressions.
To make the table name dynamic, you must interpolate it into the SQL query string before preparing 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);
Be careful $table_name
not to contain untrustworthy content. Best compared $table_name
to a list of known table names. In other words, use a fixed list of tables as your whitelist. If it $table_name
doesn't match any of your table names, use the default table name.
source to share
@ Karwin's answer is ok, also a note on the SQL injection problem. You should check that $ table_name contains a valid table name before interpolating the SELECT string. For example, if you are using MySQL, you can get the current table names set in the DB with:
my @tables = @{ $dbh->selectcol_arrayref('SHOW TABLES') };
and with grep command it is easy to check if $ table_name has the correct value. It is also possible (and database independent) to get the catalog of a table using the DBI table method .
source to share