CODEWITHSUNDEEP
mysqlperl
Ganesh
Ganesh

Reputation: 515

How to Bind value of table name in perl?

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?

Upvotes: 3

Views: 1266

Answers (2)

Miguel Prz
Miguel Prz

Reputation: 13792

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.

Upvotes: 3

Bill Karwin
Bill Karwin

Reputation: 562388

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.

Upvotes: 8

Related Questions