PHP CSV to Mysql Script - handling special chars

Question

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

Please be aware that the mysql extension (supplying the mysql_ functions) has been deprecated since 2012, in favor of the mysqli and PDO extensions. It's use is highly discouraged. See http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Oldskool, May 30 '14 at 23:14
possible duplicate of [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Incognito, May 30 '14 at 23:20
@MahasishShome, no, addslashes() is not a good solution because it has no support for MySQL character sets. Use mysql_real_escape_string(), or else query parameters. — Bill Karwin, May 30 '14 at 23:21

score 3 · Accepted Answer · answered May 30 '14 at 23:17

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.

score 1 · Answer 2 · answered May 30 '14 at 23:25

I would use LOAD DATA for this.

LOAD DATA LOCAL INFILE 'file.csv' REPLACE INTO TABLE suppliers 
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  (regid, firstname, lastname, email, company);

No need to fopen(), fgetcsv(), or execute INSERT so many times. No need to worry about apostrophes or other special characters.

score 0 · Answer 3 · answered May 30 '14 at 23:19

0

If you want to carry on using mysql_query as described, i'd suggest running $data though array _map to escape the single quotes. Something like

$data = array_map(function($string){
    return str_replace("'", "\'", $string);
}, $data);

answered May 30 '14 at 23:19

philwc

111
1
1

1

A better solution would be to move to PDO data bindings, which should take care of all the escaping automagically. – philwc May 30 '14 at 23:20
Why do you recommend this instead of `mysql_real_escape_string`? – Barmar May 30 '14 at 23:20
Assuming controlled data, only single quotes seem to be a problem. Obviously mysql_real_escape_string will cover more possibilities (but i think its slower?) – philwc May 30 '14 at 23:24

score 0 · Answer 4 · answered May 30 '14 at 23:26

The best way to do this is using the LOAD DATA INFILE statement to bulk load a CSV into MySQL:

LOAD DATA INFILE 'csvfile' into suppliers FIELDS TERMINATED BY ','

The added advantage of this is that you don't need to escape your strings at all -- LOAD DATA does it for you. Let me know if you have further questions.

PHP CSV to Mysql Script - handling special chars

4 Answers4