Using placeholders in selectall

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

Fellow Monasterians,

I have found gmax's DBI recipes helpful many times over, but I'm trying to lock down security and use placeholders. I'm able to do this in all of his examples but my rendition of:

my $query2 = qq{select id, name, salary from employees};
my $employees_loh = $dbh->selectall_arrayref($query2, {Slice => {}});
[download]

I need to add a where condition with a user input parameter:

$employer = $query->param('employer');
my $query2 = qq{ select id, name, salary from employees where employer
+ = ? };
my $employees_loh = $dbh->selectall_arrayref($query2, {Slice => {}});
[download]

Where do I insert the variable name, $employer, in my selectall_ line? Or is it even necessary? Thanks!

—Brad
"The important work of moving the world forward does not wait to be done by perfect men." George Eliot

Comment on Using placeholders in selectall_arrayref Select or Download Code

Replies are listed 'Best First'.
Re: Using placeholders in selectall_arrayref by Fang (Pilgrim) on Jan 11, 2006 at 13:01 UTC
From the DBI doc: `$ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);` Update: thanks to Happy-the-monk for clarifying what the notation actually means. I thought the OP would understand it, seeing how he used an anonymous hash himself. What the docs actually say is: `$ary_ref = $dbh->selectall_arrayref($statement); $ary_ref = $dbh->selectall_arrayref($statement, \%attr); $ary_ref = $dbh->selectall_arrayref($statement, \%attr, @bind_values);` [download] This means that the `selectall_arrayref` method takes either one, two, or any number of arguments. The first two have to be scalars, a string (SQL query) and a reference to a hash (attributes). After that, you can supply a list of values for the placeholders in your query, if any.	[reply] [d/l] [select]
Re^2: Using placeholders in selectall_arrayref by Happy-the-monk (Canon) on Jan 11, 2006 at 13:30 UTC
So to speak... `my $employees_loh = $dbh->selectall_arrayref($query2, {Slice => {}}, $employer);` (This was not missing in Fang's reply, but this might be clearer to both DBI and perlreferences newbees. It would sureley have confused me when I was one.) Cheers, Sören	[reply]
Re^3: Using placeholders in selectall_arrayref by bradcathey (Prior) on Jan 11, 2006 at 13:48 UTC
Thanks Happy-the-monk! I didn't understand that the `{Slice => {}}` was the `\%att` as shown in the doc. Nice to see an example. Perfect. —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply] [d/l] [select]
Re^3: Using placeholders in selectall_arrayref by vagabonding electron (Curate) on Jun 03, 2012 at 13:18 UTC
Awesome example. Faced the same problem as newbee and found enlightenment here. Many thanks.	[reply]
Re: Using placeholders in selectall_arrayref by bravenmd (Sexton) on Jan 11, 2006 at 21:08 UTC
You are so close. The line you have provided looks like: my $employees_loh = $dbh->selectall_arrayref($query2, {Slice => {}}); To add the parameter the selectall line needs to read: my $employees_loh = $dbh->selectall_arrayref($query2, {Slice => {}}, $employer);	[reply]