Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Mysql queries with ' and "

by fattahsafa (Sexton)
on Mar 12, 2014 at 19:52 UTC ( #1078079=perlquestion: print w/replies, xml ) Need Help??

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

Hi, My tool reads words from files, manipulates them then insert them into mysql database. For example, I use the following queries: my $insert_query = "INSERT INTO arabic_corpus (crps_word, crps_count) VALUES ('$word', '$count')"; the problem is that some words comes with single quote like "didn't". This makes a conflict with the quotes Surrounds the variables. Double quote can't also be used. any suggestion to fix this? Thanks, Abed.

Replies are listed 'Best First'.
Re: Mysql queries with ' and "
by CountZero (Bishop) on Mar 12, 2014 at 20:17 UTC
    Or use placeholders ("?") in your query. Much safer, easier and all the escaping is taken care of by the DBI/DBD.

    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

    My blog: Imperial Deltronics
Re: Mysql queries with ' and "
by Paladin (Vicar) on Mar 12, 2014 at 20:19 UTC
    You should be using placeholders, see DBI.
Re: Mysql queries with ' and "
by sundialsvc4 (Abbot) on Mar 13, 2014 at 12:14 UTC

    Placeholders are the only way to deal with this matter, to avoid the Bobby Tables problem.   (The cartoon on the front page of the site at this link says it all.)

    What if the value that made it into $count ... by whatever means ... consisted of:   “23); DROP TABLE arabic_corpus;”??   If your application connects to the database with a user-id powerful enough to issue a DROP TABLE statement and have it “stick,” your table is now gone.   (And unfortunately, many applications built by lazy programmers simply connect with a single user-id that is all-powerful.)   SQL injection ...

    And by the way, another good reason is efficiency.   prepare() a statement-handle once, for a statement that includes placeholders.   Then, repeatedly execute the prepared statement, changing the placeholder values as often as necessary.   The overhead of preparing the statement (such as it is ...) occurs only one time.   And, if you put those inserts into a transaction of reasonable size (when using certain kinds of databases), now you would really see some improved speed.

Re: Mysql queries with ' and "
by golux (Chaplain) on Mar 12, 2014 at 20:10 UTC
    Hi fattahsafa,

    Just modify your $insert_query first, to escape all occurrences of apostrophe "'":

    $insert_query =~ s/'/\\'/g;

    This changes each "'" into "\'" (you have to escape the backslash "\" in the regular expression, which why there are two).

    Update:   I agree with runrig that $dbh->quote is preferrable (as is using placeholders). On second look my way wouldn't quite work anyway, since you've got apostrophes within the string, though you could still get away with the regex if it didn't contain apostrophes to begin with; eg.:

    insert_query = qq{INSERT INTO arabic_corpus (crps_word, crps_count +) VALUES ("$word", "$count")}; $insert_query =~ s/'/\\'/g;
    say  substr+lc crypt(qw $i3 SI$),4,5
      $insert_query =~ s/'/\\'/g;
      No, the right way would be to use the $dbh->quote() method, or even better, to use placeholders as suggested below.
        Thank you ! Placeholder works well

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (9)
As of 2019-12-09 16:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?