Knowledge base
1000 FAQs, 500 tutorials and explanatory videos. Here, there are only solutions!
Is the "LOAD DATA LOCAL INFILE" function available on the server ?
Those importing CSV files will be unaffected by this change provided that they do not tick the "CSV via LOAD DATA" option in phpMyAdmin.
The "LOAD DATA LOCAL INFILE" function allows a CSV file to be imported into a MySQL table and is now unfortunately often used by hackers to gain access to certain websites hosted on servers allowing this function.
Needless to say, we provide an alternative so that you can still import data in CSV format into a MySQL table. Below you will find an example consisting of a easily implementable lines of PHP that can be incorporated into your PHP script or simply placed inside a new PHP file in your /web folder:
<?
// The following code is provided as an example for illustration purposes only.
// Feel free to develop your own more efficient method
// for inserting data.
$FileName = "data.csv";
// Open file in read-only mode
$handle = fopen($FileName, "r");
if ($handle) {
// Break csv file down into individual lines
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 row into MySQL database
mysql_query($query, $link) or die (mysql_error());
$query = NULL;
}
// fermeture du fichier csv
fclose($handle);
} else {
echo "<p>Error: cannot open file.</p>\n";
exit(1);
}
?>
Unfortunately, we cannot tell you exactly where in your script to place these lines of code. If you cannot identify the appropriate place, please contact your webmaster or one of our affiliates listed on the following page: http://partners.infomaniak.com/request-proposal/.
If the suggested alternative causes problems when you submit several CSV files to be read and if the procedure terminates, for instance, without any message being returned despite the message logging code in the PHP file, this may be because the tables and fields used are poorly indexed, in which case you should contact your webmaster.
We also recommend that you read the PHP documentation for fgetcsv which you can find here: http://php.net/manual/fr/function.fgetcsv.php
Link to this FAQ: