Обсуждение: perl and postgresql

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

----- Original Message -----
From: "Sugrue, Sean" <sean.sugrue@analog.com>
Date: Monday, March 17, 2003 8:39 am
Subject: [NOVICE] perl and postgresql

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

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

If someone notices a bug or other stupidities in my routine above, please
let me know.

ville jungman, ulvilantie 3 b 11, 00350 helsinki, finland
tel. + 358 - 9 - 225 4482 , http://www.kolumbus.fi/vilmak
usko Herraan Jeesukseen, niin sin� pelastut. (apt. 16:31)




>From: douggorley@shaw.ca
>To: "Sugrue, Sean" <sean.sugrue@analog.com>
>CC: pgsql-novice@postgresql.org
>Subject: Re: [NOVICE] perl and postgresql
>Date: Mon, 17 Mar 2003 08:55:11 -0800
>
>----- Original Message -----
>From: "Sugrue, Sean" <sean.sugrue@analog.com>
>Date: Monday, March 17, 2003 8:39 am
>Subject: [NOVICE] perl and postgresql
>
> >
> >
> > 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
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html


_________________________________________________________________
Help STOP SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail

>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'

Doesn't work if $prod is checked elsewhere.

It's easier if you can call sql-commands just in the same way that you do
with sql-prompt. For example

@result=$self->kanta("select $a from table where name='$prod'");

is much simpler than same query with placeholders. That's why I like to do
it with sub like this.

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

I'm familiar to that module. The use of these two modules are very similar
so it's easy to change my sub to use DBD if I need to do it someday. But,
good to know it's more standard way.

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


_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus

On Tue, Mar 18, 2003 at 02:05:33PM +0200, Ville Jungman wrote:
>
> It's easier if you can call sql-commands just in the same way that you do
> with sql-prompt. For example
>
> @result=$self->kanta("select $a from table where name='$prod'");
>
> is much simpler than same query with placeholders. That's why I like to do
> it with sub like this.

It's not really that much easier, if the sub kanta is written to
handle lists, like so:

@result=$self->kanta("select $a from table where name=?",$prod);

Then:

sub kanta {
    my $self=shift;
    my $sql=shift;
    my @values=@_;
    # [do stuff]
    my $sth=prepare($sql);
    $sth->execute(@values);
    # [do stuff with $sth to return the result
    return @result;
}

It's still easy to use kanta--it even allows you not to use
placeholders, but allows you to migrate to it if you wish.

--keith

--
kkeller@speakeasy.net
public key:  http://wombat.san-francisco.ca.us/kkeller/kkeller.asc
alt.os.linux.slackware FAQ:  http://wombat.san-francisco.ca.us/cgi-bin/fom

> > is much simpler than same query with placeholders. That's why I like to
>do
> > it with sub like this.
>
>It's not really that much easier

"But I'm used to this and it would be pain if I had to move back", yelled
Tim Toady, calmed down and continued: "Look at this call ripped from my
embperl-file:

$subs->kanta("
   select k.varaus_alkaa from kaavakkeet_tiedot t,kaavakkeet k
   where t.ref_kaavakkeet=k.id and k.varaus_alkaa <= $loppuaika
      and k.varaus_alkaa >= $alkuaika and t.ref_tuotteet=$ref_tuotteet

   union

   select k.varaus_paattyy from kaavakkeet_tiedot t,kaavakkeet k
   where t.ref_kaavakkeet=k.id and k.varaus_paattyy <= $loppuaika
      and k.varaus_paattyy >= $alkuaika and t.ref_tuotteet=$ref_tuotteet
");

You can't say that this would be clear to debug with placeholders. At least
if You'll someday study some Finnish the query above will look very clear -
so thinking failures are rare (or could be if some other than me was
programming). Queries are also much faster to write if they are many (like
with my current projects).

You (and [the other replyer called perhaps] Antti) are right in many things
about migrating, de facto standards etc. I'll keep them in mind. Also I
might have to convert the queries to use placeholders later - but only if
it'll make the sub faster. Perhaps i'll learn if something bad happens
because of my non-standard behaviours. Finally, I think that my sub was a
little bit insane == it just made what is asked.

Thank You for Your suggestions and advices."

---
Ville Jungman
Finland

_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

On Tue, 18 Mar 2003, Ville Jungman wrote:

> > 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'
>
> Doesn't work if $prod is checked elsewhere.
>
> It's easier if you can call sql-commands just in the same way that you do
> with sql-prompt. For example

Yep, of course. But not safest. There's additional reasons, why one would
consider using DBI. And actually it's easier to use DBI with plholders
than without.

Some helpers for DBI: (for DBI you need DBI and DBD::Pg which are
available from CPAN).

use DBI;

# returns a reference to array (the rows) of references to hashes (the
# cols).
sub my_select {
    my $dbh = shift;
    my $stmt = shift;

    return $dbh->selectall_arrayref($stmt, { Slice => { } }, @_);
}

# returns number of affected rows, undef on error, -1 on unknown.
sub my_do {
    my $dbh = shift;
    my $stmt = shift;

    return $dbh->do($stmt, undef, @_);
}

my $dbh = DBI->connect(
    "dbi:Pg:dbname=$dbname;host=$host;port=$port;options=$options;tty=$tty",
    "$username", "$password");

my $retval = my_do($dbh,
    'delete from users where username = ?', $user);

print "Total of $retval rows deleted\n";

$retval = my_select($dbh, q{
    select username,realname
        from users
        where domain = ?
}, $domain);

# result like
# $retval = [
#    { "username" => "bill", "realname" =>"BillG" },
#    { "username" => "linus", "realname" => "Linus T." }
# ];

print "Users for domain $domain:\n";
foreach (@$retval) {
    print $_->{username} . ' '. $_->{realname} . "\n";
}

print "Total of ", scalar(@$retval), " users\n";


--
Antti Haapala

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