LOAD DATA LOCAL INFILE forbidden in... PHP

Question

I am trying to use LOAD DATA INFILE to insert some records into a table. Unfortunately, it's not working.

Here are some details

If I use this instruction:

LOAD DATA INFILE 'file.txt' INTO TABLE table_ex FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, field2, field3, field4); 

It works using the MySQL client program and a PHP application. In this way it will look for the file in the Data Directory of my MySQL installation.

Now if I try to execute the instructions using the LOCAL option, it only works if I use the mysql client, but not from PHP:

LOAD DATA LOCAL INFILE 'path/to/file/file.txt' INTO TABLE table_ex FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, field2, field3, field4); 

Again.. it works with MySQL client but not from the PHP application... I get this error:

LOAD DATA LOCAL INFILE forbidden in /path/to/my/application 

I read that the problem is related to the compilation of PHP and using mysqlnd. I am using PHP 5.3.8 and MySQL 5.5.15, but I haven't found a solution.

Additional information: until now the only help I've found was an open PHP bug:

Answer 1

Check docs http://php.net/manual/en/ref.pdo-mysql.php.

Basically you need:

PDO::MYSQL_ATTR_LOCAL_INFILE => true 

Set at instantiation.

Example:

    $conn = new \PDO("mysql:host=$server;dbname=$database;", "$user", "$password", array(         PDO::MYSQL_ATTR_LOCAL_INFILE => true,     )); 

Answer 2

had this problem today and solved it by setting the following in php.ini

mysqli.allow_local_infile = On