Fix a CSV import issue

This guide explains how to resolve an issue with importing a .csv file.

"load data local infile" Function Disabled

The "LOAD DATA LOCAL INFILE" function is used to import a CSV file into a MySQL table and unfortunately is now frequently exploited by hackers to gain access to certain sites hosted on machines that accept this function.

To combat malicious activities and continue to protect our customers' data as much as possible, we have disabled the "LOAD DATA LOCAL INFILE" function. Individuals importing their CSV files (provided they do not select "CSV via LOAD DATA") via phpMyAdmin are not affected by this change.

Here is an alternative to continue importing CSV data into a MySQL table, an example in a few easy-to-implement PHP lines within your PHP script or simply in a new PHP file in your /web directory:

// The code above is only an example for you to understand how to replace this function, feel free to develop
// your own, more efficient way to insert data.

$FileName = "data.csv";

// Open the CSV file for reading
$handle = fopen($FileName, "r");
if ($handle) {
    // Decompose each line of the CSV file
    while (($data = fgetcsv($handle, 1000, ";", """)) !== FALSE) {
        $num = count($data)-1;
        // Generate SQL query
        $query = "INSERT INTO `test` VALUES (";
        for ($c=0; $c < $num; $c++) {
            $query .= "'" . mysql_real_escape_string($data[$c]) ."',";
        }
        $query .= "'" . mysql_real_escape_string($data[$num]) ."")";
        // Insert the row into the MySQL database
        mysql_query($query, $link) or die (mysql_error());
        $query = NULL;
    }
    // Close the CSV file
    fclose($handle);
} else {
    echo "Error: Unable to open the file.

Unfortunately, we cannot provide you with the exact location in your script where these code lines should be added. If you are unable to locate the appropriate location, please contact your webmaster or one of our partners on the following page: http://partenaires.infomaniak.com/appel-offres/.

If the proposed alternative causes issues when submitting multiple CSV files for reading, and the procedure, for example, ends without returning a message despite the message handling points in the PHP file, it is possible that the tables and fields used are improperly indexed. In this case, please also contact your webmaster.

We also invite you to consult PHP's documentation regarding fgetcsv here: http://php.net/manual/fr/function.fgetcsv.php