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.

In reply to cleaning up sql from file by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.