LOAD DATA LOCAL INFILE forbidden after php / mariadb update

Question

I know this has been asked before but I could not find any newer posts that could be of help. LOAD DATA LOCAL INFILE has been working perfectly up until today. I have not changed anything other than ran an update for PHP and (not sure but I think) MariaDB. I am running now PHP 7.2.17 and MariaDB 10.1.38

Now I get this error: Warning: mysqli::query(): LOAD DATA LOCAL INFILE forbidden

I checked what I could find: - db user has all rights, even root user gets this error - checked my.cnf for entry local-infile=1

Load statement works fine within mysql commandline but not in PHP script.

Here is how I run the import:

define ('DB_USER', "db_user");
define ('DB_PASSWORD', "my_password");
define ('DB_DATABASE', "prosjekt");
define ('DB_HOST', "localhost");

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);

I get the error on the last line -> $mysqli->query($query);

$query = <<<eof
    LOAD DATA LOCAL INFILE '$fileName'
     INTO TABLE prosjekt
     FIELDS TERMINATED BY ','  ENCLOSED BY '"'
     LINES TERMINATED BY '\r'
    (prosjektnavn,prosjektstatus,prosjektbesk,oppdragsgiver,prosjektans,prosjektdelt,start,slutt,prosjektnr,prosjekttype,sortnr,ant_rapport,litt_link)
eof;

$mysqli->query($query);

So I can run this fine in mysql console but not in a PHP script, and it starting failing today. Any ideas how to fix it? When I run SHOW VARIABLES; I get this so it should work?

local_infile ON

Amish

Answer 1

I solved this and for anyone interested. I read this post that helped

It seems the PHP upgrade has change my php.ini. I think I got some questions as to keep config or overwrite it. Not sure I answered correct there!

Since this is a cron job I needed to fix the php.ini here:

/etc/php/7.2/cli/php.ini

Uncomment this line or change it:

mysqli.allow_local_infile = On

You would do the same for php.ini if you are running php-fpm in the respective directories /etc/php/7.2 -> /fpm /apache

Amish

Answer 2

I just woke up to one of my apps doing the exact same thing using php7.2 and mysqli.

It looks like my php7.2 was upgraded last night as part of Ubuntu automatic security updates when I checked less /var/log/apt/history.log.

My local_infile variable was also on but didn’t seem to be working. I fixed my issue with:

mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true)

Polly

This solution is a must for setting at prepare time. – Rothschild