Mysql queries with ' and "

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

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	[reply]
Re: Mysql queries with ' and " by Paladin (Vicar) on Mar 12, 2014 at 20:19 UTC
You should be using placeholders, see DBI.	[reply]
Re: Mysql queries with ' and " by locked_user 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;` [download] 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;` [download] say substr+lc crypt(qw $i3 SI$),4,5	[reply] [d/l] [select]
Re^2: Mysql queries with ' and " by runrig (Abbot) on Mar 12, 2014 at 20:23 UTC
`$insert_query =~ s/'/\\'/g;` [download] No, the right way would be to use the `$dbh->quote()` method, or even better, to use placeholders as suggested below.	[reply] [d/l] [select]
Re^3: Mysql queries with ' and " by fattahsafa (Sexton) on Mar 12, 2014 at 20:36 UTC
Thank you ! Placeholder works well	[reply]