Resolve a CSV import issue in a MySQL table

This guide explains how to resolve a .csv file import issue into a MySQL table. The proposed alternative is to read the CSV file line by line using PHP and insert the data into the MySQL database.

"load data local infile" function disabled

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

To combat malicious acts and continue to protect customer data as much as possible, Infomaniak has disabled the LOAD DATA LOCAL INFILE function. This change does not affect users importing their CSV files (as long as "CSV via LOAD DATA" is not checked) through phpMyAdmin.

Here is an alternative to continue importing CSV data into a MySQL table, a complete example of the code with proper error handling for opening the CSV file and inserting the data into the database.

This version uses mysqli to connect to the database and prepared statements to insert the data, thus offering better security and compatibility with recent versions of PHP and ease of implementation in your PHP script or simply in a new PHP file in your /web directory:

$fileName = "data.csv";
// Connect to MySQL database using mysqli
$link = new mysqli("localhost", "username", "password", "database");
// Check connection
if ($link->connect_error) {
   die("Connection failed: " . $link->connect_error);
}
// Open the CSV file for reading
if (($handle = fopen($fileName, "r")) !== FALSE) {
   // Parse each line of the CSV file
   while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
       // Prepare the SQL query
       $query = "INSERT INTO `test` VALUES (" . str_repeat('?,', count($data) - 1) . "?)";
       $stmt = $link->prepare($query);
       // Check if query preparation was successful
       if ($stmt === FALSE) {
           die("Query preparation failed: " . $link->error);
       }
       // Bind parameters
       $types = str_repeat('s', count($data));  // Assume all columns are of type string
       $stmt->bind_param($types, ...$data);
       // Execute the query
       if (!$stmt->execute()) {
           die("Query execution failed: " . $stmt->error);
       }
       // Close the statement
       $stmt->close();
   }
   // Close the CSV file
   fclose($handle);
} else {
   echo "Error: unable to open the file.\n";
   exit(1);
}
// Close the database connection
$link->close();
?>

Get help

Unfortunately, it is impossible to specify exactly where in your script these lines of code should be added.

If the proposed alternative causes issues when you submit multiple CSV files to read and the process, 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 poorly indexed and in that case, also contact your webmaster.

Refer to the PHP documentation regarding fgetcsv: http://php.net/manual/en/function.fgetcsv.php