cleaning up sql from file

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

so, i'm trying to read through a file and run queries on that data. what i'm running into is if there's an apostrophe in the word, it screws up the sql query. how should i deal with this and in general what should i look for with strange sql input from files that might break my query. this is from a file so i'm more interested in what will break the query than security such as from a cgi script (where i do checking and use dbi's taint and the likes). however, if there is some sort of cleaner module i should use for general purposes, i'm all for that.

also, i've got bad data here, so i'm splitting by word, deduping, querying and deduping that. ah crap. just noticed that i should be splitting on what is in my tr and meant to put a - and not an =... oh well, it's not why i'm getting this err i've been trying to knock out.

here's the code:

while ( <FILE> ) {

   my $line = $_;

   chomp ($line);
   my @word = split / /, $line;


   $count = 0;
   while ( $word[ $count ] ) {
      $word[ $count ] =~ tr/^[\.\/\\=a-zA-Z]//;
      $count++;
   }

   foreach my $string ( @word ) {
      next if $uword{ $string } == 1;
      $uword{ $string } = 1;
   }
}

$count = 0;

while ( my ($key, $value) = each(%uword) ) {

   my ( $owner, $om, $manager );

   my $select =   qq/SELECT r.owner, r.om, r.manager /;
   my $from =     qq/FROM r, n /;
   my $where =    qq/WHERE MATCH(r.owner, r.om, r.manager) AGAINST('+$
+key' IN BOOLEAN MODE) /;
   my $likedate = qq/AND n.time LIKE '$date%' /;
   my $join =     qq/AND r.key = n.key/;

   my $query = $select . $from . $where . $likedate . $join;

   print "SQL: $query\n";
   my $sth = $dbh->prepare( $query );
   $sth->execute;
[download]

my error is:

SQL: SELECT r.owner, r.om, r.manager FROM r, n WHERE MATCH(r.owner, r.
+om, r.manager) AGAINST('+Miller's' IN BOOLEAN MODE) AND n.time LIKE '
+2009%' AND r.key = n.key
DBD::mysql::st execute failed: You have an error in your SQL syntax; c
+heck the manual that corresponds to your MySQL server version for the
+ right syntax to use near 's' IN BOOLEAN MODE) AND n.time LIKE '2009%
+' AND r.key = n.k' at line 1 at ./ssel.pl line 55, <FILE> line 57.
DBD::mysql::st bind_columns failed: Statement has no result columns to
+ bind (perhaps you need to successfully call execute first) at ./ssel
+.pl line 57, <FILE> line 57.
DBD::mysql::st fetch failed: fetch() without execute() at ./ssel.pl li
+ne 59, <FILE> line 57.
[download]

i'm needing to change the field names and variable names. so if something looks off, it was because of that.

Comment on cleaning up sql from file Select or Download Code

Replies are listed 'Best First'.
Re: cleaning up sql from file by graff (Chancellor) on Oct 17, 2010 at 22:48 UTC
Corion mentioned placeholders, which is what you should be using here. `while ( my ($key, $value) = each(%uword) ) { my ( $owner, $om, $manager ); my $select = qq/SELECT r.owner, r.om, r.manager /; my $from = qq/FROM r, n /; my $where = qq/WHERE MATCH(r.owner, r.om, r.manager) AGAINST(? IN B +OOLEAN MODE) /; my $likedate = qq/AND n.time LIKE ? /; my $join = qq/AND r.key = n.key/; my $query = $select . $from . $where . $likedate . $join; print "SQL: $query\n"; my $sth = $dbh->prepare( $query ); $sth->execute( '+'.$key, $date.'%' );` [download]	[reply] [d/l]
Re: cleaning up sql from file by CountZero (Bishop) on Oct 18, 2010 at 06:22 UTC
Placeholders, placeholders, placeholders. There is NO other good solution. Remember Little Bobby Tables? CountZero A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James	[reply]
Re: cleaning up sql from file by halfcountplus (Hermit) on Oct 17, 2010 at 19:35 UTC
what i'm running into is if there's an apostrophe in the word, it screws up the sql query. Yep, the "catapostrophe" is VERY dangerous with SQL -- it is a principle means of SQL injection attacks (google that). One way to prevent this is to parse the string to escape the apostrophes before it goes into the db. I believe most SQL db's use double apostrophes ('' = TWO ' characters, NOT a double quote) for this, look in your docs. Another way to prevent this is by using stored procedures (google again...)	[reply]
Re^2: cleaning up sql from file by Corion (Patriarch) on Oct 17, 2010 at 19:46 UTC
Also see DBI about the `$dbh->quote()` method to quote values. I still prefer placeholders though.	[reply] [d/l]
Re^3: cleaning up sql from file by ag4ve (Monk) on Oct 17, 2010 at 20:21 UTC
heh, i forgot to login before posting why didn't i think of that `$word[ $count ] =~ s/\'/\\\'/g;` [download] i suppose i could make it look cleaner with `s\|\'\|\\\'\|g` [download] but this works great. the dbi quote or quote_identifier might have worked as well but it failed the first run and the regex worked, so i'll use what worked thanks again	[reply] [d/l] [select]
Re^4: cleaning up sql from file by Corion (Patriarch) on Oct 17, 2010 at 20:25 UTC