in reply to cleaning up sql from file

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

Replies are listed 'Best First'.
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]

      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]

        Your approach fails if I you need to insert the following data:

        O\'Hara

        [download]

        Your routine will expand that to

        O\\'Hara </c>

        ... which is, again, invalid. SQL injection is hard to prevent if you're interpolating arbitrary data.

[reply]
[d/l]

In Section Seekers of Perl Wisdom