mysql INSERT is broken by some charactors in the inserted record 
Author Message
 mysql INSERT is broken by some charactors in the inserted record

Hi,

Mysql version: 3.23.39.  Perl 5.005.

My Perl script has the following statement.  But sometimes it gives
'ERROR 1064: You have an error in your SQL syntax near...'.  Then I
notice that the value that $value holds has a "'" or maybe some other
characters that INSERT does not like.

$sth = $dbh->prepare("INSERT INTO $table (var_name,var_value) values
('$name','$value')");

What should I do to get the record inserted?  Seems PHP has
addslashes().  Does Perl have some similar funtion that can help prevent
SQL statement from being broken by weird characters?

Appreciate any help!

Bing



Fri, 27 Feb 2004 18:36:24 GMT  
 mysql INSERT is broken by some charactors in the inserted record

Quote:

> Hi,

> Mysql version: 3.23.39.  Perl 5.005.

> My Perl script has the following statement.  But sometimes it gives
> 'ERROR 1064: You have an error in your SQL syntax near...'.  Then I
> notice that the value that $value holds has a "'" or maybe some other
> characters that INSERT does not like.

> $sth = $dbh->prepare("INSERT INTO $table (var_name,var_value) values
> ('$name','$value')");

> What should I do to get the record inserted?

Use the DBI quote() function, or let DBI do it for you:

$sth = $dbh->prepare("INSERT INTO $table (var_name,var_value) values (?,?)");

$sth->execute($name,$value);

--
If the organizational structure is threatening in any way, nothing is
going to be documented until it is completely defensible.
                                - F. Brooks, _The Mythical Man-Month_



Fri, 27 Feb 2004 19:10:49 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Inserting binary files into mysql database with DBD::mysql

2. Inserting a record into a table using a value from a sequence

3. Getting id for MSSQL2000 newly inserted record

4. DBD:Xbase -- Inserted records are seen as deleted

5. Perl-Win32::ODBC to insert a record

6. How to insert newline break into a long string

7. search for string, then insert page break and header

8. Inserting into Mysql w/DBI

9. Timestamps - Inserting rows into MySQL

10. Insert file into Mysql

11. Insert date into mysql-database

12. DBI-MYSQL-LAST INSERTED ROW

 

 
Powered by phpBB® Forum Software