如何在PHP中删除特殊引号字符？

Question

I have been handed this project that has a large number of issues with it. One of them is that the end-user is uploading a CSV file that is exported directly from MS Access into a directory on their web server. The next step is to truncate a couple of database tables and then insert all the records from the CSV into the database.

However, there is an issue with the apostrophe character that MS Access is using for apostrophes. It isn't a single quote ', nor is it a double quote ". It is an apostrophe. Like this:

"Rock/Classic 80’s-90’s"

Now, I have tried the following in my PHP to strip them out:

$d = str_replace("’", "", $d);
$d = str_replace(array("'", "\'", "\’", "’"), "", $d);

However, this does not seem to work. In fact, when running SQL queries based off of this data, it always seems to somehow convert the ’ into ' without stripping them out, and then causing a SQL error since it thinks that the string has been terminated early.

This is one of the code blocks I am using:

$band_insert = "INSERT INTO `schedule` (`Band`, `Date`, `Genre`, `Club`, `Location`, `Venue`, `Time`) VALUES ( '%s', '%s', '%s', '%s', '%s', '%s', '%s' )";
$result = $mysqli->query('TRUNCATE TABLE `schedule`');
if(!$result) die('Truncate error');

if( ($handle=fopen('./export/schedule.csv', 'r')) !== FALSE)
{
    while( ($data=fgetcsv($handle, 1000, ',', '"', '\\')) !== FALSE )
    {
        foreach($data as $d) 
        {
            $d = str_replace("’", "", $d);
            # For debugging purposes only
            echo "<p>$d</p>";
        }
        $sql = sprintf($band_insert, $data[0], $data[1], $data[2], $data[3], $data[4], $data[5], $data[6]);
        #$sql = $mysqli->real_escape_string($sql);
        $result = $mysqli->query($sql);
        if( ! $result ) $log[] = lg("Unable to perform query ($mysqli->errno): $mysqli->error");
    }
    $log[] = lg("Successful upload (".date("Y-m-d").").");

    fclose($handle);
}

The question becomes, why is this not working? When I echo out the $d value, it prints a ? in a square. Even with header('Content-type: text/html; charset=utf-8'); at the top of the file.

Answer 1

I've had some similar hurdles with Access and Excel, and use this that I picked up somewhere to scrub the MS characters (so all due credit to it's original author). Perhaps you can use it as is, or adapt accordingly:

// First, replace UTF-8 characters.
$text = str_replace(
array("\xe2\x80\x98", "\xe2\x80\x99", "\xe2\x80\x9c", "\xe2\x80\x9d", "\xe2\x80\x93", "\xe2\x80\x94", "\xe2\x80\xa6"),
array("'", "'", '"', '"', '-', '--', '...'),
$text);

// Next, either REPLACE their Windows-1252 equivalents.
$text = str_replace(
array(chr(145), chr(146), chr(147), chr(148), chr(150), chr(151), chr(133)),
array("'", "'", '"', '"', '-', '--', '...'),
$text);

// OR, STRIP their Windows-1252 equivalents.
$text = str_replace(
array(chr(145), chr(146), chr(147), chr(148), chr(150), chr(151), chr(133)),
array('', '', '', '', '', '', ''),
$text);