This guide explains how to resolve an issue when 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 LOAD DATA LOCAL INFILE function allows you to import a CSV file directly into a MySQL table. However, this feature is often exploited by attackers to gain unauthorized access to websites hosted on servers that allow it.

To prevent any security risks and protect customer data, Infomaniak has disabled the LOAD DATA LOCAL INFILE function. Users who import their CSV files via phpMyAdmin (without checking the “CSV via LOAD DATA” option) are not affected.

Here is an alternative method for importing data in CSV format into a MySQL table. The example below shows how to properly handle errors when opening the CSV file and inserting data into the database.

This script uses mysqli to connect to the database and prepared statements to insert the data. This ensures better security, optimal compatibility with recent versions of PHP, and simple integration into your project, whether in an existing script or a new file located in the /web directory:

$fileName = "data.csv";

// Connect to MySQL database using mysqli
$link = new mysqli("localhost", "username", "password", "database");

// Check database connection
if ($link->connect_error) {
   die("Connection failed: " . $link->connect_error);
}

// Open the CSV file for reading
if (($handle = fopen($fileName, "r")) !== FALSE) {

   // Read each line of the CSV file
   while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {

       // Prepare the SQL query dynamically
       $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 (assuming all columns are strings)
       $types = str_repeat('s', count($data));
       $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 the script these lines of code should be inserted.

If this method causes problems (for example, when importing multiple CSV files simultaneously without any error messages), the structure of the tables or the indexing of the fields may be the cause. In this case, contact your webmaster for verification.

Also, consult the official PHP documentation regarding the fgetcsv() function.