Re: perl and postgresql

On Tue, 18 Mar 2003, Ville Jungman wrote:

> You can run queries with a sub something like this:
>
> # query postgres
> # $komento == sql-command(s) to run
> # $subs-parameter might be unneeded in some circumstances
> sub kanta{
> my($subs,$komento)=(at)_;
> use Pg;
> my $conn = Pg::connectdb("dbname=YOURDATABASENAME") or die @!;
> my $result=$conn->exec($komento) or die $!;
> my @palaute;
> if(my $em=$conn->errorMessage){
> die "$em: komento: $komento";
> }
> while(my @apu=$result->fetchrow){
> push @palaute,@apu;
> }
> return @palaute;
> }
>
> Using placeholders id faster (I suppose) but this is easier way if you don't
> care about speed so much. Function above returns asked @values if you use
> select-command. I call it like command: $subs->kanta("select * from
> table1");

The point is that he and you need to use placeholders to avoid sql
injection. Just consider example below: what if variable prod is set to

$prod = '10; DROP DATABASE x'

Even if using placeholders wouldn't be faster, it will still quote string
literals properly.

You should also look into DBI/DBD, as it seems to be the de facto way of
doing database things in Perl today.

> >From: douggorley(at)shaw(dot)ca
> >
> >----- Original Message -----
> >From: "Sugrue, Sean" <sean(dot)sugrue(at)analog(dot)com>
> >
> > >
> > >
> > > I am trying to execute the following query within perl
> > >
> > > #!/usr/local/bin/perl
> > >
> > > use DBI;
> > >
> > > $prod='stdf';
> > >
> > >
> > > $dbh = DBI-
> > >
> > >connect("dbi:Pg:dbname=database;host=mink;port=0000","username","password");
> > > $sth = $dbh->prepare("select * from filestatus where fileformat =
> > > $prod");if( defined($sth)){
> > >
> > > $sth->execute;
> > > #for when model numbers are available
> > > while (@devices = $sth->fetchrow){
> > > ($product,$spec_key)=(at)devices;
> > > print"product = $product and speckey = $spec_key \n"; }
> > > }
> > >
> > > i***************************************
> > > it works if you put a literal value of 'stdf' for $prod
> > > but it fails when I try to use a variable.
> > >
> > > Another point is if it were an integer the variable would work.
> > >
> > > Question: How can I get this to work. I've used q// qw// qq// qx//
> > >
> > > Sean
> > >
> >
> >Try using placeholders.
> >
> >$prod='stdf';
> >$sth = $dbh->prepare("select * from filestatus where fileformat = ?");
> >$sth->execute( $prod );
> >
> >Doug Gorley | douggorley(at)shaw(dot)ca

--
Antti Haapala