Vincent,
Don't make the mistake of thinking that you're not directly using DBI.
These modules provide an abstraction for creating SQL that is data driven.
They free you from the error-prone string manipulation process, and allow you
to build your queries in such a way as to be able to handle a variety of data.
For example, from the SQL::Abstract mod docs:
my $sql = SQL::Abstract->new;
my $table = 'widgets';
my($stmt, @bind) = $sql->select($table, \@fields, \%where, \@order);
Now you can use this for any table selecting any fields with or without
specifying a where clause or an order by clause.
This allows you to directly create a quick hash to represent your where clause.
So, to get a SQL statement where first_name = 'Sally', last_name starts with
"S", your %where hash would look like this:
%where = (first_name => 'Sally',
Last_name => { like => 'S%'});
Which would set the $stmt variable above to
select * from widgets where first_name = ? and last_name like ?
And the @bind would be
@bind = ('Sally', 'S%')
Very helpful! Very flexible! And no more of that messy string manipulation
and escaping quotes.
Oh, and of course, the next thing you do is:
my $sth = $dbh->prepare($stmt);
$sth->execute(\@bind);
Which is where you directly use DBI.
Most all of this stuff is just an abstraction layer for creating SQL in a
data-centric way.
> -----Original Message-----
> From: Vincent Veyron [mailto:vv.li...@wanadoo.fr]
> Sent: Tuesday, April 17, 2012 6:09 PM
> To: Michael Ludwig
> Cc: modperl@perl.apache.org
> Subject: Re: Safe handling of an SQL query
>
> Le mardi 17 avril 2012 à 20:10 +0200, Michael Ludwig a écrit :
> > Bonjour Vincent,
> >
> > Vincent Veyron schrieb am 16.04.2012 um 22:21 (+0200):
> > >
> > > I guess (in the message I forwarded to the list) Andreas is right,
> > > though : the only way to be safe is to keep control of the query,
> > > therefore keep it on the server.
> > >
> > > I am doing this now, but passing parameters to the query becomes
> > > cumbersome :-(
> >
> > If you haven't done that already, you could take a look at the
> following
> > three modules. While I have never used any of them I've bookmarked
> them
> > for future opportunities …
> >
> > https://metacpan.org/module/SQL::Interp
> > https://metacpan.org/module/SQL::Abstract
> > https://metacpan.org/module/DBIx::Simple
> >
>
> Guten Tag Michael,
>
> I did not know about those, but what I don't see the benefit compared
> to
> using DBI directly, which is really concise. This is all I need to get
> a
> reference to a data set :
>
> my $dbh = $r->pnotes('dbh_data');
>
> my $sql = 'SELECT ... FROM ... WHERE X=? AND Y=?';
>
> #collect data
> eval { $data = $dbh->selectall_arrayref($sql, { Slice => {} },
> ( $param_x, $param_y ) ) };
>
>
> where dbh_data is a reference to a dbi connection using connect_cached,
> stored in pnotes.
>
> ?
>
> --
> Vincent Veyron
> http://marica.fr/
> Logiciel de gestion des sinistres assurances et des dossiers
> contentieux pour le service juridique
_______________________________________________
Barclays is one of the world's leading banks, and we believe that by continuing
to integrate the organisation we can better deliver the full power of Barclays
to customers, clients and the communities in which we work.
As a visible sign of that integration we are moving to a single Barclays brand
for the majority of our divisions, including those formerly known as Barclays
Capital, Barclays Wealth and Barclays Corporate.
_______________________________________________
This e-mail may contain information that is confidential, privileged or
otherwise protected from
disclosure. If you are not an intended recipient of this e-mail, do not
duplicate or redistribute
it by any means. Please delete it and any attachments and notify the sender
that you have received
it in error. Unless specifically indicated, this e-mail is not an offer to buy
or sell or a
solicitation to buy or sell any securities, investment products or other
financial product or
service, an official confirmation of any transaction, or an official statement
of Barclays. Any
views or opinions presented are solely those of the author and do not
necessarily represent those
of Barclays. This e-mail is subject to terms available at the following link:
www.barcap.com/emaildisclaimer.
By messaging with Barclays you consent to the foregoing. Barclays offers
premier investment banking
products and services to its clients through Barclays Bank PLC, a company
registered in England
(number 1026167) with its registered office at 1 Churchill Place, London, E14
5HP. This email may
relate to or be sent from other members of the Barclays Group.
_______________________________________________
