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

Disabled “LOAD DATA LOCAL INFILE” function

The LOAD DATA LOCAL INFILE function allows importing a CSV file directly into a MySQL table. However, this feature is often exploited by attackers to gain unauthorized access to sites hosted on servers that accept 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 to import data in CSV format into a MySQL table. The example below shows how to properly handle errors when opening the CSV file and inserting the 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 not possible to precisely indicate where in the script these lines of code should be inserted.

If this method causes issues (for example, when importing multiple CSV files simultaneously without an error message), it is possible that the table structure or field indexing is the cause. In this case, contact your webmaster for verification.

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