PHP Text Replacement

[fluiduk]

Hi All

Working on a script that imports a large amount of data from a csv file into a database.

However some of the lines in the CSV contain nasty characters which mySQL doesn't like, apostraphes for example.

Can someone recommend a piece of code that can be used to replace these.

I have had a look at a lot of the examples online, however I need to be able to specify multiple rules, ie replace ' with a ft or & with and.

I dont want to use too many loops etc as the data file will have almost 80000 lines when the site goes live and I don't want it to take weeks. At the moment my test file has 7000 lines and it takes 3-4 seconds to process that.

Any advice is greatly appreciated.

Thanks
Aaron

PS - in case it helps, here is the script so far

$file = array();
foreach(file('****.csv') as $line)
{
    $file[] = explode(',', $line);
}


foreach($file as $data) {

mysql_select_db($database_wp_master, $wp_master);
$insert*** = "INSERT INTO ***_temp VALUES('$data[12]','$data[13]','$data[11]','$data[15]','$data[9]','$data[3]','$data[20]','$data[21]')";
mysql_query($insert***, $wp_master) or die(mysql_error());

}
echo 'Data Imported<br/><br/>';

 

[Dj_Jestar]

mysql_real_escape_string()

 

[fluiduk]

Thanks Jestar

Im not too sure whats this sprintf stuff is and how it works, how would I rewrite my query to use it?

Would it be like this?

$query = sprintf("INSERT INTO **_temp VALUES('%s','%s','%s','%s','%s','%s','%s','%s'",
mysql_real_escape_string($data[12]),
mysql_real_escape_string($data[13]),
mysql_real_escape_string($data[11]),
mysql_real_escape_string($data[15]),
mysql_real_escape_string($data[9]),
mysql_real_escape_string($data[3]),
mysql_real_escape_string($data[20]),
mysql_real_escape_string($data[21]));
mysql_query($query, $wp_master) or die(mysql_error());

 

[fluiduk]

Got this working. Only issue I have...

Will this add slashes everywhere in my text?

Aaron

 

[robmiller]

Only escape input as you're using it in a query to avoid having slashes where there shouldn't be slashes—the sprintf method is a good one to help you with this, since if you do it for all queries you can't possibly pass unescaped data by accident

Make sure you're handling Magic Quotes too

 

[fluiduk]

Would it be a good idea to use something else to remove all the slashes? Or should I remove the characters that are causing the slashes before I run the query, so that way I can cut out the slashes that occur frequently and still be safer from injection attacks etc.

The data all comes from a distributors CSV product list, so there is no user input as such - although it's best not too leave it to chance