Re: Help, single quotation mark conflicts error in SQL statement!



Much appreciated! :-) I'll try it and get back to you ASAP.

Anyway, can I just use this statement below instead of yours as I set id is INT with AUTOINCREMENT?

my $sql = q[INSERT INTO table1 VALUES (?, ?, ?)];

Nan


From: Chris Devers <cdevers@xxxxxxxxx>
Reply-To: beginners@xxxxxxxx
To: Nan Jiang <ayajiang@xxxxxxxxxxx>
CC: beginners@xxxxxxxx
Subject: Re: Help, single quotation mark conflicts error in SQL statement!
Date: Fri, 8 Jul 2005 09:45:56 -0400 (EDT)

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: beginners-unsubscribe@xxxxxxxx
For additional commands, e-mail: beginners-help@xxxxxxxx
<http://learn.perl.org/> <http://learn.perl.org/first-response>




.