This guide explains how to solve a problem importing a .csv file 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 function LOAD DATA LOCAL INFILE allows you to import a CSV file into a MySQL table and is unfortunately now frequently used by hackers to gain access to certain sites hosted on machines that accept this function.

To combat malicious acts and continue to protect clients' data as much as possible, Infomaniak has disabled the LOAD DATA LOCAL INFILE function. This change does not affect users importing their CSV files (provided they do not check "CSV via LOAD DATA") via phpMyAdmin.

Here is an alternative to continue importing CSV formatted data into a MySQL table, a complete example (in French) 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:

$NomDuFichier = "data.csv";
// Connexion à la base de données MySQL avec mysqli
$link = new mysqli("localhost", "username", "password", "database");
// Vérification de la connexion
if ($link->connect_error) {
   die("Échec de la connexion : " . $link->connect_error);
}
// Ouverture du fichier CSV en lecture
if (($handle = fopen($NomDuFichier, "r")) !== FALSE) {
   // Décomposition de chaque ligne du fichier CSV
   while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
       // Préparation de la requête SQL
       $query = "INSERT INTO `test` VALUES (" . str_repeat('?,', count($data) - 1) . "?)";
       $stmt = $link->prepare($query);
       // Vérification de la préparation de la requête
       if ($stmt === FALSE) {
           die("Échec de la préparation de la requête : " . $link->error);
       }
       // Liaison des paramètres
       $types = str_repeat('s', count($data));  // Assume que toutes les colonnes sont de type string
       $stmt->bind_param($types, ...$data);
       // Exécution de la requête
       if (!$stmt->execute()) {
           die("Échec de l'exécution de la requête : " . $stmt->error);
       }
       // Fermeture de la déclaration
       $stmt->close();
   }
   // Fermeture du fichier CSV
   fclose($handle);
} else {
   echo "Erreur : impossible d'ouvrir le fichier.
";
   exit(1);
}
// Fermeture de la connexion à la base de données
$link->close();
?>

Getting help

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

If the proposed alternative causes issues when you submit multiple CSV files to read and the procedure, for example, ends without returning a message despite the error handling points in the PHP file, it is possible that the tables and fields used are not properly indexed. In this case, also contact your webmaster.

Refer to the PHP documentation regarding fgetcsv.