No apostrophe Insert into MySQL

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

I receive an "Order insert failed" when I tried to insert any text that contains an apostrophe or quote. For instance: "I don't care" throws an error because of the "don't". I solved this problem by using a regex to replace all occurences of ' with _, except now I have users who need the _ for web addreses.
I may be out of the loop, but is there an easier way to go about this than:

if($checked_feedback =~ m/[']/g) { 
   $checked_feedback =~ tr/'/_/;
}
$statement = "INSERT INTO Posts(subject,id,heading,body,date,time,appr
+oved) VALUES ('$subject','$ids','$post_title','$checked_feedback',CUR
+DATE(),CURTIME(),1)";
$insert = $db->prepare($statement) or die "Couldn't prepare the query:
+", 
$rv = $insert->execute or die "Order insert failed: ",       $insert->
+finish();
[download]

Comment on No apostrophe Insert into MySQL Download Code

Replies are listed 'Best First'.
Re: No apostrophe Insert into MySQL by Aristotle (Chancellor) on Oct 31, 2005 at 20:45 UTC
Use placeholders. `$statement = "INSERT INTO Posts( subject, id, heading, body, date, time, appro +ved )" . " VALUES ( ?, ?, ?, ?, CURDATE(), CURTIME(), 1 )"; $insert = $db->prepare( $statement ) or die "Couldn't prepare the quer +y:"; $rv = $insert->execute( $subject, $ids, $post_title, $checked_feedback + ) or die "Order insert failed: ", $insert->finish();` [download] Make sure you don’t interpolate any user input literally into your SQL statements the way you’re showing in your code. That’s a barn door sized security hole. You want to inform yourself about SQL injection. As an aside, that code looks like you need a healthy dose of strict, warnings, and particularly, taint mode. The way you’re working doesn’t look very safe… you’re going to end up with a lot of security holes like that. Have a look at Ovid’s excellent CGI course. Makeshifts last the longest.	[reply] [d/l]
Re: No apostrophe Insert into MySQL by Roy Johnson (Monsignor) on Oct 31, 2005 at 21:01 UTC
You should use placeholders, as Aristotle said. But you should also be aware that the way to escape apostrophes in ANSI SQL is with another apostrophe. So you'd do `$checked_feedback =~ s/'/''/g;` [download] Caution: Contents may have been coded under pressure.	[reply] [d/l]
Re^2: No apostrophe Insert into MySQL by Codon (Friar) on Oct 31, 2005 at 21:16 UTC
DBI also supplies a `quote()` method which will quote a string appropriately for your DB. This may save you from massive code refactoring. `my $quoted = $dbh->quote("Wouldn't you rather use bind parameters?");` [download] TMTOWTDI, although some are certainly better than others. Ivan Heffner Sr. Software Engineer, DAS Lead WhitePages.com, Inc.	[reply] [d/l] [select]
Re^2: No apostrophe Insert into MySQL by Aristotle (Chancellor) on Oct 31, 2005 at 21:30 UTC
But some databases accept backslashing as well. And others yet may have who-knows-what special rules. Unless you cover every possible base, an attacker will have a way in. Quoting your strings manually is an uphill battle, and one you may lose with your next system upgrade. So don’t even try. Use the quoting facilities supplied by the database (or the DBI driver) instead. They’re always complete – and if not, it’s because of someone else’s bug, with the maintenance cost being outside your codebase. Makeshifts last the longest.	[reply]
Re: No apostrophe Insert into MySQL by stonecolddevin (Parson) on Oct 31, 2005 at 21:39 UTC
use placeholders as said before, and use `qq` or `q` in lieu of regular quotes. This will help with confusion and escaping single quotes. `$statement = qq{ INSERT INTO Posts(subject,id,heading,body,date,time,approved) VALUES (?,?,?,?,CURDATE(),CURTIME(),1)};` [download] meh.	[reply] [d/l] [select]
Re^2: No apostrophe Insert into MySQL by intranetman (Acolyte) on Nov 01, 2005 at 16:37 UTC
Thanks to everyone for their help. I did use a regex to check user input, but place holder's are definitely a good idea. I have my own mod_security rules for SQL injection attacks, however, I will be sure to read Ovid's tutorial, use the DBI quoting facility, and use qq from this point forward. I didn't expect my request to generate so much discussion. Thank you for all the insight, I have much to learn.	[reply]