How to filter load data local infile (MySql) escaped by ' single-quote'

[chamindas]

$singlequote ="\'";

$myquery="load data local infile '$filename' replace into table $tablename fields terminated by ';' enclosed by '$doublequote' escaped by '$singlequote' ";

$execute=$connect->query($myquery);


when I am running above perl script it generates an errror like this

DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''''' at line 1 at /usr/lib/perl5/Mysql.pm line 175.


Could U tell me how to insert single quote in the SQL query

Thanks,

Chaminda

[miller]

Hello Chaminda,

$singlequote ="\'";
 
# Is the same as
 
$singlequote ="'";
 

In MySQL, single quotes can be escaped with either a backslash, or by doubling the single quote.

$singlequote ="\\'";
 

- Miller

[chamindas]

HI Miller,

Thanks a lot your solution.

$singlequote ="\\'"; is working for me

Regards,

Chaminda

[miller]

Glad to hear that you got it working.

Please note that in the end it would be a good idea to learn how to use placeholders instead of hardcoding variables into sql statements. This lets DBI handle the escaping of special characters for you. But doing it like this will work for now.

- Miller