Re: perl and postgresql - Mailing list pgsql-novice

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)=@_;
>       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@shaw.ca
> >
> >----- Original Message -----
> >From: "Sugrue, Sean" <sean.sugrue@analog.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)=@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@shaw.ca

--
Antti Haapala