PHP CSV到Mysql脚本 - 处理特殊字符 [英] PHP CSV to Mysql Script - handling special chars

问题描述

我有以下脚本将数据从CSV文件上传到我的数据库。

I have the following script which uploads the data from a CSV file to my database.

问题发生，虽然当CSV中的一个字段有撇号'）

Problem occurs though when one of the fields in the CSV has a apostrophe (')

来自CSV的示例数据：

Sample data from CSV:

"12345","John","Smith","[e-mail]","Company Name"
"12346","Joe","Blogg","[e-mail]","Company's Name"

代码我正在使用：

<?
$link = mysql_connect("localhost", "######", "######") or die("Could not connect: ".mysql_error());
$db = mysql_select_db("######") or die(mysql_error());

$row = 1;
$handle = fopen ("file.csv","r");

while ($data = fgetcsv ($handle, 1000, ",")) {
   $query = "INSERT INTO suppliers(`regid`, `firstname`, `lastname`, `email`, `company`) VALUES('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '".$data[4]."') ON DUPLICATE KEY UPDATE REGID='".$data[0]."', firstname='".$data[1]."', lastname='".$data[2]."', email= '".$data[3]."', company= '".$data[4]."'";
   $result = mysql_query($query) or die("Invalid query: " . mysql_error().__LINE__.__FILE__);
   $row++;
}
fclose ($handle);
?>

任何人都可以提出解决方案来解决这个问题？

Can anyone suggest a solution to get around this?

非常感谢

推荐答案

最好的解决方案是升级到PDO或mysqli，并利用他们的参数化查询

The best solution would be to upgrade to PDO or mysqli, and make use of their parametrized queries.

如果不能，您应该在将数据插入查询之前转义：

If you can't, you should escape the data before inserting it into queries:

while ($data = fgetcsv($handle, 1000, ",")) {
    $data = array_map('mysql_real_escape_string', $data);
    $query = "INSERT INTO suppliers(`regid`, `firstname`, `lastname`, `email`, `company`) VALUES('".$data[0]."', '".$data[1]."', '".$data[2]."', '".$data[3]."', '".$data[4]."') ON DUPLICATE KEY UPDATE REGID='".$data[0]."', firstname='".$data[1]."', lastname='".$data[2]."', email= '".$data[3]."', company= '".$data[4]."'";
    $result = mysql_query($query) or die("Invalid query: " . mysql_error().__LINE__.__FILE__);
    $row++;
}

您应该始终使用 mysql_real_escape_string 对任何用户提供的数据，以防止SQL注入或处理这样的语法问题。

You should always be using mysql_real_escape_string on any user-supplied data, to protect against SQL injection or deal with syntax problems like this.

这篇关于PHP CSV到Mysql脚本 - 处理特殊字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！