LOAD DATA LOCAL INFILE forbidden in... PHP

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,开发者_StackOverflow社区 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:

---

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

---

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

mysqli.allow_local_infile = On

---

I didn't get the exact error you get, but you need no ensure the following:

Enable by adding to your my.cnf:

[mysql]
local-infile=1

[mysqld]
local-infile=1

Tell the connection in PHP that it may use LOCAL INFILE

Using mysql:

mysql_connect(server,user,code,false,128); // 128 enables LOCAL INFILE
mysql_select_db(database);

Using mysqli:

$conn = mysqli_init();
mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($conn,server,user,code,database);

Give MySQL user FILE permission

When using LOCAL this shouldn't be necessary, though. LOCAL says that the file is located on the client server (where you have PHP is installed), otherwise it looks at server location (where MySQL is installed).

GRANT FILE ON *.* TO 'mysql_user'@'localhost' 

---

Easier work around is to use exec()

exec("mysql -u myuser -pMyPass -e \"USE mydb;TRUNCATE mytable;LOAD DATA INFILE '" . $file . "' IGNORE  INTO TABLE mytable;\"; ");

---

2019+ relevant answer with a bit more background:

In PHP >7.2.16 and >7.3.3 the default ini configuration of mysqli.allow_local_infile, which controls this, changed from '1' to '0' (so it is now disabled by default).

This directive is only configurable via PHP_INI_SYSTEM so ini_set() will not work.

The only option is to add the following directive to your php.ini file, not forgetting to reload apache.

[MySQLi]
mysqli.allow_local_infile = On

---

According to the MySQL manual MySQL must be compiled with --enable-local-infile. From a comment at that link:

You MUST have compiled PHP using the full path to MySQL, otherwise it will use it's internal handlers, which don't work with the "new" LOAD DATA.

--with-mysql=/usr/local/mysql (assuming your MySQL is located here)

You MUST start the MySQL daemon with the option '--local-infile=1'

---

The solution whish worked for me is below. Adding mysqli_options was required on second server I've setup same script.

$mysqli = new 
mysqli("$db_server_name","$db_user_name","$db_password","$database_name");
// force LOCAL_INFILE
mysqli_options($mysqli, MYSQLI_OPT_LOCAL_INFILE, true);

---

LOAD DATA LOCAL INFILE executes regardless of the warnings. it works on mysql client since it allows the execution of queries, ignoring warnings. Though it later prints out the warnings. It refuses in PHP though because a warning will halt the script.

---

Easiest solution, that may work on some servers is to remove LOCAL like:

Original:LOAD DATA LOCAL INFILE New/ It should be: LOAD DATA INFILE

Strange, but I have found this solution to work on my local machine, with xampp but it did not work on a live server with CentOS, so I'd to revert the code back and add 'LOCAL'.

---

I had exactly the same problem on a EC2 Ubuntu 12.04 LTS instance when accessing a MySQL on RDS: LOAD DATA LOCAL INFILE... works fine on a mysql console but not from PHP. Accidentaly i found out that it worked fine on another almost identical machine that used MariaDB (a binary compatible drop in replacement for MySQL).

So i replaced the MySQL clients with the ones from MariaDB and it worked.

---

If you use an Ubuntu server, you can try to install php5-mysqlnd :

sudo apt-get install php5-mysqlnd

---

To resolve the same problem in PHP Symfony application, this flag needs to be enabled in the yml config file. Here is an example:

# Doctrine Configuration
doctrine:
    dbal:
        driver:   pdo_mysql
        options:
            !php/const PDO::MYSQL_ATTR_LOCAL_INFILE: true
        # Skip the rest

Also note how to reference PHP constant here in yml file, and this format is used for Symfony 3.4. For older version, check out Symfony doc.

---

uncomment 'mysqli.allow_local_infile = On' in php.ini.