Raziel has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks,

I am using the DBI module and I have the name of my table contained in a file (a kind of .ini file). When I prepare my select statement, I get an error saying there is someting wrong with the FROM statement. Here is my code:

$sth = $db->prepare('SELECT Names FROM $table WHERE Name = ?');

$rv = $sth->execute($name);

[download]

The name of the table is correct in the variable $table, but I get an error. How can I get around this? I am using an Access database. Thanks for any help

Replies are listed 'Best First'.
Re: Variable table
by Pardus (Pilgrim) on Jan 23, 2003 at 19:00 UTC
    Probably because you use single quotes, so the variable isn't be interpolated. Use double quotes.
    --
    Jaap Karssenberg || Pardus (Larus)? <pardus@cpan.org>
    >>>> Zoidberg: So many memories, so many strange fluids gushing out of patients' bodies.... <<<<
[reply]
Re: Variable table
by hardburn (Abbot) on Jan 23, 2003 at 19:52 UTC

    As stated above, you need to use double quotes for that to work correctly.

    However, you are treding on dangerous security ground here. If someone can modify that text file, they might be able to execute any SQL statement they want:

    SELECT Names FROM ; DROP some_table ;WHERE Name = ?

    Many databases won't let you use a placeholder on the table name (I often wish they did). The best you might be able to hope for is this:

    my $safe_table = $db->quote($table);
$sth = $db->prepare("SELECT Names FROM $safe_table WHERE Name = ?");

    [download]
[reply]
[d/l]
[select]
Re: Variable table
by Raziel (Initiate) on Jan 23, 2003 at 20:07 UTC
    I gave your suggestions a try, but they didn't work. I guess maybe Access doesn't allow placeholders for table names. Thanks for you help anyway, guys.
[reply]
      Your initial question is not using a placeholder for the table name -- you are attempting to interpolate a variable to get the table name into a string which is then passed to the prepare routine. This is not working as you expect because you are using single quotes ( as stated by the previous posters ).

      Make your line look like this instead and it should work: 

      $sth = $db->prepare("SELECT Names FROM $table WHERE Name = ?");

      [download]
[reply]
[d/l]
      With Access, are you using Win32::ODBC ?
      "Programming the Perl DBI" page 169 says "Placeholders and bind parameters are not supported"
      poj
[reply]

Back to Seekers of Perl Wisdom