Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

Quoting in DBI sql query for on the fly query

by ParisR (Acolyte)
on Oct 29, 2004 at 01:15 UTC ( #403566=perlquestion: print w/replies, xml ) Need Help??

ParisR has asked for the wisdom of the Perl Monks concerning the following question:

This is working:

$sqlstatement="SELECT Table1.Name, Table1.Address, Table1.City, Table1.State, Table1.ZIP, Table1.Phone FROM Table1 WHERE Table1.BusType = 'ARCHITECTS'"; $sth = $dbh->prepare($sqlstatement);

But attempting on the fly, this is not:

my @lines = <DATA>; my $line; foreach $line ( @lines ) { #prepare and execute SQL statement $sqlstatement="SELECT Table1.Name, Table1.Address, Table1.City, Table1.State, Table1.ZIP, Table1.Phone FROM Table1 WHERE Table1.BusType = '$line'; $sth = $dbh->prepare($sqlstatement);

I've tried variations with the quotes but I'm stuck.

Replies are listed 'Best First'.
Re: Quoting in DBI sql query for on the fly query
by dragonchild (Archbishop) on Oct 29, 2004 at 01:32 UTC
    Use placeholders. Not only does it solve your quoting problem, but it prevents nearly every SQL injection attack known to man.
    my $sth = $dbh->prepare( <<__END_SQL__ ); SELECT Table1.Name ,Table1.Address ,Table1.City ,Table1.State ,Table1.ZIP ,Table1.Phone FROM Table1 WHERE Table1.BusType = ? __END_SQL__ foreach my $line ( @lines ) { $sth->execute( $line ); # Do whatever you wanted with the executed statement, like fetch() $sth->finish; }

    Try it out before you discard it. This is how DBI is supposed to be used.

    Being right, does not endow the right to be rude; politeness costs nothing.
    Being unknowing, is not the same as being stupid.
    Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
    Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Re: Quoting in DBI sql query for on the fly query
by borisz (Canon) on Oct 29, 2004 at 01:29 UTC
    my $sqlstatement = q{ SELECT Table1.Name, Table1.Address, Table1.City, Table1.State, Table1.ZIP, Table1.Phone FROM Table1 WHERE Table1.BusType = ?}; my $sth = $dbh->prepare($sqlstatement); foreach my $line (<DATA>) { chomp $line; $sth->execute( $line ); # your fetch here fetch }
    also see DBI's quote function. DBI
    Boris
      I've tried, but something still is not working.
      Here is the code:
      #! perl -w use DBI; use DBD::ODBC; # open connection to Access database $dbh = DBI->connect('dbi:ODBC:fred3'); my @lines = <DATA>; my $line; foreach $line ( @lines ) { my $sqlstatement = q{ SELECT Table1.Name, Table1.Address, Table1.City +, Table1.State, Table1.ZIP, Table1.Phone FROM Table1 WHERE Table1.Bus +Type = ?}; my $sth = $dbh->prepare($sqlstatement); foreach my $line (<DATA>) { chomp $line; $sth->execute( $line ); my $newdir= 'c:\redone'; my $extension= ".txt"; my $target = "$newdir/$line"; open( FINAL, ">$target" ) or die "Can't open $target:$!"; #output database results while (@row=$sth->fetchrow_array) { print FINAL "$row[0]\n$row[1]\n$row[2] $row[3] $row[4]<br>\n$row[5]\n\ +n"} close FINAL; } # end of fetchrow_array } # end of loop for each of data below __DATA__ ARCHITECTS INVESTIGATORS
        What is not working exactly? I've noted a couple of areas that may be giving trouble
        .... ### my $newdir= 'c:\redone'; my $newdir= 'c:/redone'; my $extension= ".txt"; ## I would be tempted to take the preceeding 2 statements out of the l +oop my $target = "$newdir/$line"; open( FINAL, ">$target" ) or die "Can't open $target:$!"; #output database results ### while (@row=$sth->fetchrow_array) { while (@row=$sth->fetchrow_array()) { ...
        something still is not working

        Could you please be a little less specific? I'm practicing for my mindreading act. Thanks.

        You write foreach my $line (<DATA>) {, but at that point, you've already read in all of <DATA>, to @lines. What's more, you've already opened a loop over @lines -- foreach $line ( @lines ) {. Finally, you only close one of these two loops.

        You need to use strict; and pay attention to what perl tells you when the code fails.

Re: Quoting in DBI sql query for on the fly query
by mpeppler (Vicar) on Oct 29, 2004 at 06:17 UTC
    Besides the recommendation to use placeholders, your problem is that the strings you pass to the query include the trailing \n. Use chomp() to fix this.

    Michael

Re: Quoting in DBI sql query for on the fly query
by htoug (Deacon) on Oct 29, 2004 at 12:01 UTC
    In your second example the terminating " is missing.

    You should use $dbh->quote($line) to ensure that the quoting is correct. This incidentally guards against SQL-injection attacks. You need a chomp to remove the trailing newline in $line

    You should write something like (untested):

    my @lines = <DATA>; my $line; foreach $line ( @lines ) { chomp($line); #prepare and execute SQL statement $sqlstatement="SELECT Table1.Name, Table1.Address, Table1.City, Table1.State, Table1.ZIP, Table1.Phone FROM Table1 WHERE Table1.BusType = ".$dbh->quote($l +ine); $sth = $dbh->prepare($sqlstatement); }
Re: Quoting in DBI sql query for on the fly query
by rdfield (Priest) on Oct 29, 2004 at 09:52 UTC
    Did you actually look at the SQL in $sqlstatement? It often helps when debugging to look at the values held in variables. Modules such as Data::Dumper are a great help in this regard.

    rdfield

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://403566]
Approved by atcroft
Front-paged by htoug
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (6)
As of 2021-05-16 10:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Perl 7 will be out ...





    Results (151 votes). Check out past polls.

    Notices?