Escape apostrophe when importing into MySQL

[mcmon999]

Hi,

I'm trying to import a CSV (tab) delimited file into a MySQL database, however it error's when importing fields with an apostrophe.

I'm importing the data using the fgetcsv function:

$filename='

http://www.test.co.uk/2009-09-14.txt';

$handle = fopen("$filename", "r");

while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE)
{

if (strlen($data[0])== 1) {
$import="INSERT into data(type, timestamp, program_id, id, element_id, event_id, referrer, visitor, user_agent, user_id, order_number, order_value, currency, lead, click_to, invalid, epi, epi2, quantity, pool_id, report, query) values('$data[0]', '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[11]', '$data[12]', '$data[13]', '$data[14]', '$data[15]', '$data[16]', '$data[17]', '$data[18]', '$data[19]', '$data[20]', '$data[21]')";

$result = mysql_query($import);

if (!$result){ $error = mysql_error();
echo $error; }

}
}

I've attempted to use the str_replace but this returns, the below errors:

Notice: Undefined offset: 18 in C:\wamp\

http://www\revshare\csv_import.php

on line 20

Fatal error: Maximum execution time of 30 seconds exceeded in C:\wamp\

http://www\revshare\csv_import.php

on line 20

What's the best way to escape these characters when they are in an array.

Thanks in advance.

 

[jpadie]

you should escape the data variable before using it.

array_map('mysql_real_escape_string', $data);

 

[DonQuichote]

I agree that the values must be escaped, but that is not the problem here. The problem is that one of the lines does not have as many fields as the others. Probably there is a newline character in one of the fields. Not only SQL has to be escaped, CSV has to be escaped as well. The only problem is that there is no hard standard for CSV, just an RFC. (See

http://www.faqs.org/rfcs/rfc4180.html)

I am afraid that PHP just reads a line and parses it next, instead of abiding the quotes.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[mcmon999]

Hi,

Thanks very much for your help.

I've attempted to use:

array_map('mysql_real_escape_string', $data);

It's completes the import of data but the 6 fields that have an apostrophe are still dropped?

I included the code within the while loop:

$filename='2009-09-14.txt';
$handle = fopen("$filename", "r");

while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE)
{
array_map('mysql_real_escape_string', $data);

if (strlen($data[0])== 1) {
$import="INSERT into click_data(type, timestamp, program_id, affiliate_id, graphical_element_id, event_id, referrer, visitor_ip, user_agent, guid, order_number, order_value, currency, lead_number, click_to_slc, invalid_click, epi, epi2, quantity, pool_id, report_info, query_string) values('$data[0]', '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[11]', '$data[12]', '$data[13]', '$data[14]', '$data[15]', '$data[16]', '$data[17]', '$data[18]', '$data[19]', '$data[20]', '$data[21]')";
mysql_query($import);
}
}

Thought i'd include an example of how the apostrophe appears in the data:

barry's+bootcamp or chef\'s%20choice%20knife

Not sure if that affects the encoding.

regarding the mis-match with the number of fields, within the data there is a line break between each line of data but i'm sure if this can be escaped, i'm thought i'd only import fields that have data.

Thanks again.

 

[jpadie]

i would not use fgetcsv for tab delimited data, purely because the requirements of fgetcsv are that the delimeter is a single character. I do not know how php would handle the \t character.

let's try a more manual approach.

$filename='2009-09-14.txt';
$handle = fopen($filename, "rbt");
while (!feof($handle)){
    //handle the data manually
    $_data = fgets($handle);
    $_data = explode ("\t", $_data);
    $_data = array_map('cleanse', $_data);
    //ensure all data values are set for the query
    for ($i=0; $i<=21; $i++){
       $data[$i] = isset($_data[$i]) ? $data[$i] : null;
    }
    $import="INSERT into click_data(type, timestamp, program_id, affiliate_id, graphical_element_id, event_id, referrer, visitor_ip, user_agent, guid, order_number, order_value, currency, lead_number, click_to_slc, invalid_click, epi, epi2, quantity, pool_id, report_info, query_string) values('$data[0]', '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[11]', '$data[12]', '$data[13]', '$data[14]', '$data[15]', '$data[16]', '$data[17]', '$data[18]', '$data[19]', '$data[20]', '$data[21]')";
   $result =  mysql_query($import);
   if (!$result){
    echo "problem with query: <br/><pre>$import</br>" . mysql_error() . "</pre>";
   }
}
fclose($handle);

function cleanse ($item){
   $item = stripslashes($item);
   $item = trim($item);
   return mysql_real_escape_string($item);
}

 

[feherke]

Hi

i would not use fgetcsv for tab delimited data, purely because the requirements of fgetcsv are that the delimeter is a single character. I do not know how php would handle the \t character.

While [tt]\t[/tt] denotes a single #9 ( HT - horizontal tab ) character, there is no problem with it. I used [tt]fgetcsv()[/tt] many times, almost exclusively with [tt]\t[/tt] and had no problem. Of course, as long it is enclosed in double quotes, so PHP interprets the escape sequence.

Feherke.

http://rootshell.be/~feherke/

 

[jpadie]

i thought that might be the case. was too lazy to try it though.

thanks.

 

[mcmon999]

Hi,

Thanks for you feedback.

I'm testing this now but when importing the file, it seems to insert the correct about of records but all records are empty.

Am i missing anything that's preventing the variables from being populated?

$filename='2009-09-14.txt';
$handle = fopen($filename, "rbt");

while (!feof($handle)){    //handle the data manually    

		$_data = fgets($handle);    
		$_data = explode ("\t", $_data);    
		$_data = array_map('cleanse', $_data);    

//ensure all data values are set for the query    
for ($i=0; $i<=21; $i++){      $data[$i] = isset($_data[$i]) ? $data[$i] : null;    }  
	
$import="INSERT into click_data(type, timestamp, program_id, affiliate_id, graphical_element_id, event_id, referrer, visitor_ip, user_agent, guid, order_number, order_value, currency, lead_number, click_to_slc, invalid_click, epi, epi2, quantity, pool_id, report_info, query_string) values ('$data[0]', '$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]', '$data[7]', '$data[8]', '$data[9]', '$data[10]', '$data[11]', '$data[12]', '$data[13]', '$data[14]', '$data[15]', '$data[16]', '$data[17]', '$data[18]', '$data[19]', '$data[20]', '$data[21]')";

	$result =  mysql_query($import);

	if (!$result){    echo "problem with query: <br/><pre>$import</br>" . mysql_error() . "</pre>";   }
	}

	fclose($handle);


function cleanse ($item){   $item = stripslashes($item);   $item = trim($item);   return mysql_real_escape_string($item);}

Thanks!

 

[jpadie]

underscore missing. sorry

$data[$i] = isset($_data[$i]) ? $[red]_[/red]data[$i] : null;

 

[mcmon999]

Excellent...thank very much for your help on this jpadie!!

James