Re: Perl Mysql DBI, using placeholders with unknown variable count

With mysql you can't use placeholders for column specifications, only for data. For column names you have to use $dbh->quote_identifier instead.

my $query = "SELECT " 
           . join(", ", map { $dbh->quote_identifier($_) } @q_return) 
           . " FROM rma_list WHERE (" 
           . join(', ', ('?') x (keys %q_search)
           ...
[download]

Perl 6 projects - links to (nearly) everything that is Perl 6.

Comment on Re: Perl Mysql DBI, using placeholders with unknown variable count Select or Download Code

Replies are listed 'Best First'.
Re^2: Perl Mysql DBI, using placeholders with unknown variable count by Haioken (Novice) on Aug 18, 2009 at 10:20 UTC
I've also been painfully made aware that my search terms (@q_names) are being quoted, irrespective of SQL's datatype for this field, or of the data in my field. As an example: SELECT `status`, `rma_num`, `auth_id`, `vendor`, `part_num` FROM rma_l +ist WHERE ('status') = ('0'); [download] is now being generated, which returns 0 rows, as status is an integer. My code for $query has been extrapolated from your elegant example as: `my $query = "SELECT " . join(", ", map { $dbh->quote_identifier($_) } @q_return) . " FROM rma_list WHERE (" . join(', ', ('?') x (keys %q_search)) . ") = (" . join(', ', ('?') x @q_names) . ");";` [download] It seems obvious to me that my code isn't placing these quotes on the search items, so I guess DBI's substitution is. Is there a way to force this off for integer only variables? Or a different substitution character?	[reply] [d/l] [select]
Re^3: Perl Mysql DBI, using placeholders with unknown variable count by moritz (Cardinal) on Aug 18, 2009 at 10:27 UTC
If `status` is a column name, you may not quote it with `$dbh->quote()` or by using it as a placeholder - you must use `$dbh->quote_identifier()`. That is the problem, not the data type of the int. Sorry if I got that wrong in my first example. The rule is simply `$dbh->quote_identifier()` for column names and placeholders (`?`) for data. Perl 6 projects - links to (nearly) everything that is Perl 6.	[reply] [d/l] [select]