Escaping a charator parsing a csv file.

phsynet

I did a quick search on this and didn't find anything. I've been trying for several days to finish a script I'm writing to dump a csv file to an sql database using a php submit form, and have come across a slight snag.

In several of the csv fields i need to upload, there exists a single quote ( ' ). ex: 203 Martha's Way

This causes sql to stop the query at that spot and report an error. Here is the relevant code, I'd appreciate any help anyone can lend.

<?
if(isset($_POST['upload'])) {   

    $filename = ($_FILES['userfile']['tmp_name']); 
    $handle = fopen($filename, 'r'); 

$row = 1; 
while (($data = fgetcsv($handle, 1000, ',', '"')) !== FALSE) { 
mysql_query (" 

INSERT INTO cc_test (
	lastname, 
	firstname, 
	oldaddress, 
	oldcitystate, 
	oldzip, 
	phone, 
	newaddress, 
	newcitystate, 
	newzip, 
	office) 

VALUES ( 
    '" . $data[0] . "',
	'" . $data[1] . "',
	'" . $data[2] . "',
	'" . $data[3] . "',
	'" . $data[4] . "',
	'" . $data[5] . "',
	'" . $data[6] . "',
	'" . $data[7] . "',
	'" . $data[8] . "',
	'" . $data[10] . "'
) 
") 
    or die(mysql_error()); 

} 

fclose($handle); 
}           

?>

Kudos.

devinemke

[man]addslashes[/man]

phsynet

ps: I also would like some suggestions on a way to report the number of arrays or data fields uploaded in the query... I'm sure this is easy, but my brain refuses to figure it out... thanks!

phsynet

Resolved as follows

<?
require_once('db.class.php');
$db = new db_class;
   if (!$db->connect('localhost', 'root', 'boston', 'cc_csv', true)) 
   $db->print_last_error(false);

$date = date('Y-m-d') ."\n";   


if(isset($_POST['upload'])) {   

    $filename = ($_FILES['userfile']['tmp_name']); 
    $handle = fopen($filename, 'r+');

$row = 1; 
while (($data = fgetcsv($handle, 1000, ',', '"')) !== FALSE) { 
$data = array_map('addslashes', $data); 
mysql_query (" 
INSERT INTO cc_test (lastname, firstname, oldaddress, oldcitystate, oldzip, phone, newaddress, newcitystate, newzip, office, date) 
    VALUES ( 
        '" . $data[0] . "',
		'" . $data[1] . "',
		'" . $data[2] . "',
		'" . $data[3] . "',
		'" . $data[4] . "',
		'" . $data[5] . "',
		'" . $data[6] . "',
		'" . $data[7] . "',
		'" . $data[8] . "',
		'" . $data[10] . "',
		'" . $date . "'
    ) 
") 
    or die(mysql_error()); 

} 

fclose($handle); 
}           

?>

Thanks for the help!