Q:

PHP CSV to Mysql Script - handling special chars

I have the following script which uploads the data from a CSV file to my database.
Problem occurs though when one of the fields in the CSV has a apostrophe (')
Sample data from CSV:
"12345","John","Smith","john.smith@gmail.com","Company Name"
"12346","Joe","Blogg","joe.blogg@gmail.com","Company's Name"

Code I'm using:
<?
$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?
Many thanks

A:

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++;
}

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.

