perl mysql question

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

Hi,

OK, what I want to do is put some text into mysql table.
But, I get a problem where if there's a single quote in
my $body, then the sql syntax complains.
Here's my code.

#!/usr/bin/perl -w
use strict;
use DBI;
use Mail::Internet;

my $dbase   = "python";
my $driver  = "DBI:mysql";
my $user    = 'morongo';
my $passwd  = 'llama';
my $dbh     = DBI->connect("$driver:database=$dbase",$user,$passwd) or
+ die "Can't connect";
my $msg     = new Mail::Internet \*STDIN;
my $from    = $msg->head->get('From');
my $to      = $msg->head->get('To');
my $date    = $msg->head->get('Date');
my $subject = $msg->head->get('Subject');
my $body    = join('',@{$msg->body});


$dbh->do("INSERT INTO agency (AgencyName,Email) Values('CNN','$to')");
$dbh->do("INSERT INTO article (Subject,CNNDate,SenderEmail) Values('$s
+ubject','$date','$from')");
$dbh->do("INSERT INTO body (Body) Values('$body')");


$dbh->disconnect;
[download]

If my $body or $subject has the following
This is a 'test'
Then it complains....which can be understandable that there's a single quote in my sql cmd.

any suggestions?

Comment on perl mysql question Download Code

Replies are listed 'Best First'.
Re: perl mysql question by halley (Prior) on Nov 05, 2005 at 23:36 UTC
Use placeholders. They take the literals OUT of your SQL statements, and take them as direct arguments instead. This has the side benefit of letting you pre-compile your SQL statements. Any level of chasing quotes and doublequotes and escape sequences will just backfire eventually. Do it right from the start. Compare: `$dbh->do("INSERT INTO body (Body) Values('$body')");` [download] `$dbh->do("INSERT INTO body (Body) Values (?)", $body);` [download] (Actual syntax is not tested. (Thanks, tinita.)) -- `[ e d @ h a l l e y . c c ]`	[reply] [d/l] [select]
Re^2: perl mysql question by tinita (Parson) on Nov 06, 2005 at 00:47 UTC
`$dbh->do("INSERT INTO body (Body) Values (?)", $body);` [download] one small mistake: `$dbh->do("INSERT INTO body (Body) Values (?)", undef, $body);` [download] perldoc DBI says `$rv = $dbh->do($statement); $rv = $dbh->do($statement, \%attr); $rv = $dbh->do($statement, \%attr, @bind_values);` [download]	[reply] [d/l] [select]
Re^2: perl mysql question by pg (Canon) on Nov 06, 2005 at 02:45 UTC
By calling do(), you didn't actually benefit from prepared SQL statement. As every time you call do(), the statement is prepared on fly, if this do() is executed more than once, the SQL statement will be prepared every and each time. do() is usually only used for non-repeated non-select SQL statement. The usual way is to prepare() the statement once, and execute() with parameters many times.	[reply]
Re^3: perl mysql question by Thilosophy (Curate) on Nov 06, 2005 at 07:00 UTC
By calling do(), you didn't actually benefit from prepared SQL statement. While you do not get all the benefits of a properly `prepare`d (and repeatedly re-`execute`d) SQL statement, using placeholders still is a significant improvement even when using `do`, because the SQL is typically also cached in the DB server. So when you `do` the same SQL string the second time, it does not have to be reparsed from scratch (server-side). This even works when someone else connected to the same DB uses the same SQL, which is a good thing because you are probably using more than one connection at the same time. Conversely, not using bind variables can totally kill the scalability of a database application. Of course, re-using prepared statements where possible is the best way, but using bind variables is itself a great (I daresay necessary) improvement (and a good habit, for both performance and security reasons).	[reply] [d/l] [select]
Re: perl mysql question by sh1tn (Priest) on Nov 05, 2005 at 23:35 UTC
`$quoted_string = $dbh->quote($string);` You may want to see DBI.	[reply] [d/l]
Re: perl mysql question by davidrw (Prior) on Nov 06, 2005 at 00:50 UTC
As already mentioned, you absolutely want to use DBI and placeholders, but thought i'd mention just for general SQL knowledge that you can escape single quotes. Both of these are valid statements: `$dbh->do("INSERT INTO body (Body) Values ('Here''s a quote')"); my $rows = $dbh->selectall_arrayref("select * from body b where b.Body + like '%''%'");` [download]	[reply] [d/l]
Re: perl mysql question by Evil Attraction (Novice) on Nov 07, 2005 at 10:01 UTC
You should always use placeholders when inserting data into databases; `my $stInsert = $dbh->prepare( 'INSERT INTO agency (AgencyName, Email) +VALUES (?, ?)' ); $stInsert->execute( 'CNN', $to ); $stInsert->finish(); # ...` [download]	[reply] [d/l]
Re: perl mysql question by kulls (Hermit) on Nov 08, 2005 at 04:27 UTC
Hi, you can call `$dbh->quote($var_name)` this method before executing the query. .This method will take care of the special characters. more info: http://search.cpan.org/~timb/DBI-1.48/DBI.pm#quote_identifier	[reply] [d/l]