DBI problem

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

Hi. I'm having a little trouble with DBI...

<snip>

  $dbqpinfo = $dbh->prepare(q{SELECT ? FROM pinfo WHERE name = ?})
    or die "Unable to prepare 'dbqpinfo': " . $dbh->errstr . "\n";

<snip>

  if ($type eq "password") {
    my $name = $login{$client}{'name'};
    $dbqpinfo->execute('passwd', "$name")
      or die "Unable to execute dbqpinfo: " . $dbh->errstr . "\n";
    my $password = $dbqpinfo->fetchrow_array();
    if (crypt("$input", 'llama') eq $password) {
      $user{$client} = $name;
      $client{$name} = $client;
      delete $login{$client};
    } else {
      $outbuffer{$client} .= "Incorrect password for $name! (disco use
+r)";
    }
  }

<snip>
[download]

The database has the columns name, passwd, created and laston.

Whenever this bit of code is called, $password gets 'passwd' (the column name!?). I've tried lots of different things with arrays, arrayrefs and the like, but I really don't know what is going on :(

Can anyone see anything I missed?

--
A frustrated llama.

Comment on DBI problem Download Code

Replies are listed 'Best First'.
Re: DBI problem by chromatic (Archbishop) on Jan 16, 2003 at 19:46 UTC
Placeholders are not allowed for column or table names. You'll have to use `SELECT passwd FROM pinfo...` isntead.	[reply] [d/l]
Re: DBI problem by Anonymous Monk on Jan 16, 2003 at 20:07 UTC
Thanks a bunch guys! Most helpful and fixed the problem right up!	[reply]
Re: DBI problem by pfaut (Priest) on Jan 16, 2003 at 19:47 UTC
Placeholders are used to plug data into SQL statements. They cannot be used to alter the statements themselves. What you asked the database to do was this: `SELECT 'passwd' FROM pinfo WHERE name = '<name>'` If you need to query different fields at different times, each will need it's own query statement. The alternative would be to ask for all fields and only read out the one you wanted from the response. `--- print map { my ($m)=1<<hex($_)&11?' ':''; $m.=substr('AHJPacehklnorstu',hex($_),1) } split //,'2fde0abe76c36c914586c';` [download]	[reply] [d/l] [select]
Re: DBI problem by valdez (Monsignor) on Jan 16, 2003 at 19:54 UTC
There is a complete discussion of your problem at node A DBI Query Binding Pitfall Ciao, Valerio	[reply]
Re: DBI problem by runrig (Abbot) on Jan 16, 2003 at 19:47 UTC
You are selecting the literal string "passwd", not the column passwd, and so your select is ending up as this: `select "passwd" from table where...` [download] putting placeholders in the column list is not allowed by many db's, and I suspect a bad idea in your case. Just 'select passwd', and leave the first placeholder out of the select statement.	[reply] [d/l]
Re: DBI problem by Fletch (Bishop) on Jan 16, 2003 at 20:27 UTC
Not related to the DBI problem, but the double quotes around `"$name"` and `"$input"` aren't necessary (and could cause all sorts of headaches if you ever tried passing an object which overloaded stringification and subsequently tried to invoke methods on it . . .).	[reply] [d/l] [select]
Re: DBI problem by OM_Zen (Scribe) on Jan 17, 2003 at 21:07 UTC
Hi, The holders of places is for the conditional clause. `select passwd from pinfo where name=?` should be the select and also do remember select entertains constants like `select 'passwd' from pinfo ;` is the one that can give you "passwd" as the resultset .You have to make that a part of the select ,cause , the parser assumes `passwd as "passwd"` ,	[reply] [d/l] [select]