Single Quote messes up file load

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Perryl7
Forum Newbie
Posts: 11
Joined: Tue Feb 08, 2005 1:32 pm

Single Quote messes up file load

Post by Perryl7 »

I am loading a CSV file into a table. All name that do not contain an apostrophe load fine, but one like "O'Reilly" throw warnings (even though they load into the table). I am doing this with a PHP page. Below is my code. Does anyone know how to ignore single quotes on a load command or any other way around this?

Code: Select all

$sql = "LOAD DATA INFILE '" . $current_file . "'
INTO TABLE temp FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY 
'\\r\\n' IGNORE 1 LINES";
		
$result = mysql_query($sql);
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

Code: Select all

// on inputting to the database
$current_file = addslashes($current_file);
// on retrieval from the database
$current_file = stripslashes($current_file);
Perryl7
Forum Newbie
Posts: 11
Joined: Tue Feb 08, 2005 1:32 pm

Post by Perryl7 »

Will the addslashes make the import ignore the single quotes because it is escaping it? Once it is in the database I can retrieve it without a problem using double quotes.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

yes it will.

basically the reason it was "crashing" is because PHP thinks the ' is part of the actual PHP code and not the MySQL code, therefore crashing PHP with a syntax error, escaping it allows the value to be altered for database storage and then returned to normal when retrieving.
Perryl7
Forum Newbie
Posts: 11
Joined: Tue Feb 08, 2005 1:32 pm

Post by Perryl7 »

Thanks, that worked.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Post Reply