Error with importing .CSV file with my PHP script. How to remove special characters

PHP
1

Hello,

I am using PHP in order to import .CSV files into a MySQL database. I am having an issue with special characters. For example: The following name gives me a SQL error when I try to import from a .CSV

Chilling 'n Grilling

The ( ’ ) apostrophe is giving me an error in my SQL Insert statement in the following code:



<?php

$passedfile = $_GET["fn"];
include "connect.php";

if(isset($_POST['submit']))
   {
     $filename=$_POST['filename'];
	

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

$cnt=0;
     while (($data = fgetcsv($handle, 1000, ",")) !== FALSE && $cnt<100)

     {


$import="INSERT into events
(eventname, location, category, month, day, duration, url, contact, demographic, attendance)
VALUES
('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]',
 '$data[6]','$data[7]','$data[8]','$data[9]' )";
	

       mysql_query($import) or die(mysql_error());
	
	   $cnt++;

     }

     fclose($handle);


echo "import processed";
   }

   else

   {



      print "<form action='thisscript.php' method='post'>";

      print "Type file name to import:<br>";

      print "<input type='text' name='filename' value='upload/$passedfile' size='20'><br>";
      print "<input type='submit' name='submit' value='submit'></form>";
	  print "<a href='../sompage.php'>cancel</a>";

   }
?>

so anything that is Inserted via the $data[0] variable that has an ’ apostrophe in it is causing an error in the SQL Insert statement.

Does anyone have suggestions how I can remove the ’ apostrophe from the values prior to the INSERT statement in the WHILE LOOP.

Please let me know… all suggestions are welcome and I do appreciate your assistance.

This is my first post here ever so lets see what the sitepoint.com crowd can do!

Cheers! :slight_smile:

2

Hi arkitektron,

You need to use the function [fphp]mysql_escape_string[/fphp] to make the strings safe (by doing things like escaping apostrophes) before inserting them into the DB. You can run the function on each item in your $data array like this:

array_map('mysql_escape_string', $array);

NOTE: you really shouldn’t use the php MySQL extension anymore, as it’s been depreciated… use the [fphp]MySQLi[/fphp] functions, or better still, [fphp]PDO[/fphp].