[RESOLVED] stripping out apostrophes

ejwf

What is the best way to 'clean' data being uploaded from a CSV file into mysql database.
I am mostly concerned with apostrophes as our users keep putting them in addresses and then they go up into the database via the csv file and everything gets moved along a field - not good!

I am thinking along the lines of

$filename = $uploadFile;
$fp = fopen($filename,"rb") or die('could not open file!'); 

while (!feof($fp)){ 

$data = fgetcsv($fp,500000); 

$ClientId = addslashes($data[0]); 
$ClientName = addslashes($data[1]); 
$Address1 = addslashes($data[2]); 
// etc.etc.etc.

$sql = "REPLACE INTO tblClient (ClientId, ClientName, Address1, etc.etc.) 
VALUES ('$ClientId', '$ClientName', '$Address1', etc.etc.)";
$result = mysql_query($sql) 
 or die(mysql_error());   

   }

but this doesn't seem to be enough to strip out the apostrophes and other nasty things........
What else do I need to clean this data?

thanks in advance

Jasp182

I use [MAN]htmlentities[/MAN]. Use the additional parameter ENT_QUOTES to include conversion of single quotes as well as double. [MAN]htmlspecialchars[/MAN] will do only convert the following (if that's all you need):

'
"
&
<

whereas htmlentities will do anything it can find a match for.

ejwf

thanks for this, but doesn't this change the character into something else?
In the manual it says when using htmlentities($str);
a single quote ' is replaced with &#039 which isn't exactly what I want......
I want to remove the single quote or replace it with a space......

thanks

kakki

$str = ereg_replace ( "[']", ' ', $str );

Jasp182

ejwf wrote:
thanks for this, but doesn't this change the character into something else?
In the manual it says when using htmlentities($str);
a single quote ' is replaced with &#039 which isn't exactly what I want......
I want to remove the single quote or replace it with a space......

thanks

I would go with [MAN]str_replace[/MAN]. The above example of using ereg_replace would work, but for a simple task like this str_replace will work fine. If you want a number of characters to be replaced by either a blank space or even to strip them and not replace with anything, you can either replace one item or replace an array.

You could change:

$ClientId = addslashes($data[0]); 
$ClientName = addslashes($data[1]); 
$Address1 = addslashes($data[2]);

To:

$array_of_invalid_characeters=array("\'","\"");

$ClientId = str_replace($array_of_invalid_characters,"",$data[0]); 
$ClientName = str_replace($array_of_invalid_characters,"",$data[1]); 
$Address1 = str_replace($array_of_invalid_characters,"",$data[2]);

If you're doing a lot of stuff like this on a regular basis, you might want to consider creating a string replacement class and just have functions that do this kind of work for you. It would make things look a little cleaner if this sort of thing occurs frequently in your code.

Weedpacket

For such short job like replacing single characters (quotes) with single characters (spaces) strtr would be even more compact:

$unquoted = strtr($string, "'\"", "  ");

It has the advantage of not having to search through the string twice looking for things to replace. It also doesn't have to worry about the string changing length as it works, so internally memory requirements are a lot more predictable.

ejwf

thanks for all your replies - with your help pointing me in the right direction I have got this sorted now..........