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:

Severe

File permissions issue maybe? – Micco

Try making the path absolute and not relative. – Limulus

Fine from mysql client but not application? Are you using the same username/password to connect? – Boeke

Mike Permission are okay, in fact I put 777 to the file. – Sardinian

Amir I am using absolute path – Sardinian

Marc, the application is working okay with the connection and If I don't use the Local option it work from the application and mysql client – Sardinian

I had the same problem running 'load data local;. By removing the 'local' php ran fine. But I wonder if there is any performance hit with "load data .. " without the "local" ? – Coalesce

I have the EXACT same issue. My LOAD DATA LOCAL INFILE works perfectly from within MySQL Workbench, but throws a "LOAD DATA LOCAL INFILE" forbidden when the exact same SQL is used within a PHP script. – Belligerency

This is a bug in php. See bugs.php.net/bug.php?id=68226 for explanation and workaround. – Wappes

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

Tocsin

According to the docs it can't be set at prepare time, only at construction. Although this does fix the problem, so +1. – Abdu

This only works in the PDO constructor. Even invoking PDO::setAttribute immediately after construction won't get the job done. – Masqat

This is the only thing that worked for me, thank you. And yes-- only at construction. – Mccomas

You're my hero! 💕 – Fjeld

It works for me. But I wonder, does "mysqli.allow_local_infile" at php.ini should be "On" before using this solution? – Alexis

Answer 2

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

mysqli.allow_local_infile = On

Bravura

depending on your environment a restart of apache might be needed – Bravura

Tried this, and restarted apache, still, I couldn't get it – Leary

Make sure that you are altering the correct php.ini file. First using phpnfo(); make sure you are changing the correct php.ini file (in the apache2 folder, not cli). Do a "cat php.ini | grep 'mysqli.allow_local_infile ='. Make sure its not commented out. in my situation it was commented out. – Riddle

@GrowlingFlea I did all that and it still does not work – Smell

This happened to me in an upgrade from php 7.3 to 7.4 today, I gather they added this configuration option between versions and this solution fixed it. I should also note that this is a nice security addition since I can enable this for my php command line scripts without enabling for websites served by apache. – B

Answer 3

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' 

Centenarian

mysql_connect(server,user,code,false,128); Works with my php version. Thanks for saving time MaX. – Harrold

Answer 4

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;\"; ");

Hathaway

+1. This the solution we've gone with. It was the quickest way to get our code working; just convert the original line of code that makes the DB call into an exec() call instead. Not an elegant solution, but I can confirm that it works well. – Concession

Sadly, after attempting every other solution, this was the only one I had left. Thanks. – Chew

I dislike the solution (because the host now needs mysql client) but as @Chew said, all other solutions dont work. I tried PDO, MySQLI and also deprecated mysql. I'm currently required to use php 5.4, so I can't speak for more modern versions. – Godinez

After trying the last solution as suggested by @sboye , it now works. Seems to be related to the php mysql driver. – Godinez

with later mysql client versions, you must also pass the --local-infile parameter on your exec line – Stephanistephania

Answer 5

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

Practice

Answer 6

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'

Booher

Clive, but I am using the mysql native driver of php, what it means if when I compile php I use this --with-mysql=mysqlnd --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd, so I had no need to use full path..... And I installed mysql from binary source which mean that I don't need --local-infile=1..., because is enable by default... The prove of that is that if I use the LOCAL Option from mysql client it works... so local-infile is working... I know the problem is with php, but I think it should be a solution with the native driver. – Sardinian

Here is the information about mysql native driver on php php.net/manual/en/mysqlnd.overview.php – Sardinian

Answer 7

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

Gelid

Answer 8

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.

Oruntha

Answer 9

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'.

Councillor

This worked for me. I tried changing mysqli.allow_local_infile = On in the php.ini file and that failed on the xampp server – Laband

Answer 10

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.

Alkalify

Answer 11

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

sudo apt-get install php5-mysqlnd

Cosmos

Despite the "exec(.." solution, this is the only solution that works for me under php 5.4 – Godinez

Answer 12

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.

Lifton

Works with SF 4.4 👍 – Crabb

Answer 13

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

Polysyllable

Answer 14

2023 Version Answer for:

Uncaught mysqli_sql_exception: LOAD DATA LOCAL INFILE is forbidden;

Best Solution for Ubuntu Linux and Windows

• Lets check PHP version running on the server through command

php -i | grep php.ini

• On the terminal Copy the path provided e.g Loaded Configuration File => /etc/php/8.2/cli/php.ini

• Just copy the File path and Run the following command and edit php.ini file. Am using nano editor

sudo nano /etc/php/8.2/fpm/php.ini

Note! Uncomment the line

mysqli.allow_local_infile = On

• Press ctrl+s to save and ctrl+x to exit

• Now Refresh php.ini file to effect the changes we have made

service php8.2-fpm restart

OR

On windows

• Just open the php.ini file as Administrator and uncomment the line below then save and you are good to go. Restrictions solved.

mysqli.allow_local_infile = On

Congratulations you can now LOAD INFILE DATA

Exotic