mysql - 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 '
'
(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 '
'
(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:

question from:https://stackoverflow.com/questions/7638090/load-data-local-infile-forbidden-in-php

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,
    ));