On Fri, 8 Jul 2005, Nan Jiang wrote:
I'm trying to add data to my mysql database, however, I received a SQL syntax
error because sometimes my string variable contains single quotation mark
(').
Does anyone know how to cope with it?
My statement is below:
$dbh->do("INSERT INTO table1 values(id, '$_', '$t')") foreach sort keys %temp;
Using SQL placeholders solves exactly this problem.
<http://search.cpan.org/~timb/DBI/DBI.pm#Placeholders_and_Bind_Values>
With placeholders, the SQL syntax will be something like this:
INSERT INTO table1 (id, col2, col3) VALUES (?, ?, ?)
This then gets executed something like this, for a single insertion:
my $sql = q[INSERT INTO table1 (id, col2, col3) VALUES (?, ?, ?)];
my $sth = $dbh->prepare( $sql );
$sth->execute( undef, $col_B, $col_C );
This then gets executed something like this, for a bulk insertion:
my $sql = q[INSERT INTO table1 (id, col2, col3) VALUES (?, ?, ?)];
my $sth = $dbh->prepare( $sql );
foreach sort keys %temp {
$sth->execute( undef, $_, $t );
}
Try it and see if it works for you.
--
Chris Devers
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
