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