Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

Using placeholders in selectall_arrayref

by bradcathey (Prior)
on Jan 11, 2006 at 12:57 UTC ( #522425=perlquestion: print w/replies, xml ) Need Help??
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 => {}});

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 => {}});

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

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

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);

    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.

      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

        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.

        "The important work of moving the world forward does not wait to be done by perfect men." George Eliot
        Awesome example.
        Faced the same problem as newbee and found enlightenment here.
        Many thanks.
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);

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://522425]
Approved by Ultra
Front-paged by grinder
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (9)
As of 2017-11-23 16:06 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (336 votes). Check out past polls.