http://www.perlmonks.org?node_id=977819

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

Hello Monks, first you helped my much with my insert/update problem in mysql. Now I have a little question for the select command. I'm doing a select in my module by this command: 

if ($what eq "W")
        {
                $sth = $dbh->prepare( sprintf "select * from %s where 
+%s = %s",$dbh->quote_identifier($table),$dbh->quote_identifier($col),
+$sel);
        .....and so on
        }
}

[download]
My csv list is looking like this: 

XZ14B:TEST:TEST:TEST:TEST
XX145:TEST:TEST:TEST:TEST
11111:TEST:TEST:TEST:TEST

[download]
The first column is my primary key (customer article number). I only get the lines back when I'm looking for the article number with only number in it. In this example I get 11111:TEST:TEST:TEST:TEST. The others cause an error Unknown column 'XZ14B' in where clause. But I don't give the article numbers to the column scalar. In the column scalar is really the column name (in this case tab1). Did I make a mistake at the columns values. First column is CHAR(30) or VARCHAR(30) right ? Crosspost: http://forums.mysql.com/read.php?10,558320,558320#msg-558320

Replies are listed 'Best First'.
Re: Mysql Select Query by Perl
by roboticus (Chancellor) on Jun 22, 2012 at 11:18 UTC

    endymion:

    In SQL, you need to quote strings, otherwise they'll be interpreted as column names and such. That's what the warning is trying to tell you. I see you're trying to properly quote the table name and column, but you're failing to quote the value. Try the quote() method.

    Frequently, you'll find that using placeholders will give you more robust code, so you may want to learn to use them. It's certainly no harder than building the SQL strings yourself, and less likely to fail. The only problem in this case is that you can't pass in an identifier (at least for the databases I typically use).

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
      Hello roboticus, I can't find the site at the moment, but I had read that the the columns always need to be quoted to identify them als columns from mysql, and the values not ? Couldn't it be that my primary key has a problem ? Because all numbers without letters are taken.
[reply]

        No, that's backwards. In SQL (at least in MySQL), you need to quote string values, not column names. A statement like this:

        SELECT * FROM mytable WHERE customer = Smith;

        tries to find rows where the value of the customer field equals the value of the Smith field. So it gives you an error like the one you got, complaining that there isn't a column named Smith. If you're looking for a customer whose name is Smith, you do:

        SELECT * FROM mytable WHERE customer = 'Smith';

        In Perl/DBI, you can do that a couple different ways. The second one below, using placeholders, is much safer, especially when searching on user-provided data (which is the case most of the time). It's also easier, because you let DBI do the quoting for you.

        my $name = 'Smith';
my $st = $db->prepare( qq| SELECT * FROM mytable WHERE customer = '$na
+me'; | );
$st->execute or die $DBI::errstr;

# or with placeholders
my $name = 'Smith';
my $st = $db->prepare( q| SELECT * FROM mytable WHERE customer = ?; | 
+);
$st->execute($name) or die $DBI::errstr;

        [download]

        Aaron B.
        Available for small or large Perl jobs; see my home node.

[reply]
[d/l]
[select]
Re: Mysql Select Query by Perl
by tobyink (Canon) on Jun 22, 2012 at 14:17 UTC 
    $sth = $dbh->prepare(
   sprintf(
      q(select * from %s where %s=?),
      $dbh->quote_identifier($table),
      $dbh->quote_identifier($col),
   )
);
$sth->execute($sel);

    [download]
    perl -E'sub Monkey::do{say$_,for@_,do{($monkey=[caller(0)]->[3])=~s{::}{ }and$monkey}}"Monkey say"->Monkey::do'
[reply]
[d/l]
Re: Mysql Select Query by Perl
by Anonymous Monk on Jun 22, 2012 at 16:10 UTC

    As the other posters have mentioned already, table names, column names, and values need to be quoted differently:

    $sth = $dbh->prepare(
  sprintf("select * from %s where %s = %s",
    $dbh->quote_identifier($table),
    $dbh->quote_identifier($col),
    $dbh->quote($sel))
);

    [download]

    And (continuing with the other posters theme), you ought to be using prepared statements to decouple SQL and the actual values.

[reply]
[d/l]

Back to Seekers of Perl Wisdom