mysqli.allow_local_infile always off even i change in php.ini file, how to enable for the right way?

Question

I'm a newbie running ubuntu 18.04 with Apache2, Php (7.3), and Mysql.

When I run

sudo php -r 'phpinfo();' | grep -i mysqli

I get the result:

    /etc/php/7.3/cli/conf.d/20-mysqli.ini,
    mysqli
    MysqlI Support => enabled
    mysqli.allow_local_infile => Off => Off
    mysqli.allow_persistent => On => On
    mysqli.default_host => no value => no value
    mysqli.default_port => 3306 => 3306
    mysqli.default_pw => no value => no value
    mysqli.default_socket => no value => no value
    mysqli.default_user => no value => no value
    mysqli.max_links => Unlimited => Unlimited
    mysqli.max_persistent => Unlimited => Unlimited
    mysqli.reconnect => Off => Off
    mysqli.rollback_on_cached_plink => Off => Off
    API Extensions => mysqli,pdo_mysql

My question is why mysqli.allow_local_infile => Off => Off ,Even after I try to enable it in php.ini? Am I doing something wrong?

(sorry for my bad english)

Answer 1

I run php 7.0 out of the box, and I checked with your "formula"

sudo php -r 'phpinfo();' | grep -i mysqli

and I got mysqli.allow_local_infile => Off => Off
then I took a look in mysql

SHOW GLOBAL VARIABLES LIKE '%local_infile%';

and on my system it seems on. But this is prolly because I am on MariaDB
If you want to turn it on you can go:

SET GLOBAL `local_infile` = 'ON';

in mariaDB there is no such settings in the my.cnf, and I tried to add it but no effect
You can try to add in your my.cnf somewhere after

[mysqld]
[...]
local_infile=ON
// or
ocal_infile=1
// or
allow_local_infile=ON
// or
allow_local_infile=1

and restart your database. If it rocks let me know.

Another method, not so elegant I admit, is, if you are on some linux box you can go to your startup sequence (on debian it is /etc/ini.d/mysql) edit it, and search for 'start') and place after the last command before you meet ;; the following code:

case...
[...]
    'start')
    [...]
    /usr/bin/mysql -u <user> -p<pass> -e "SET GLOBAL local_infile = ON"
    ;;

( I am not sure but I think you can skip -u and -p because you are root. ) OK,
...and the last resort is the ini-file. You make a file called myini.sql write

SET GLOBAL `local_infile` = 'ON';

in it, place it in /etc/mysql and chown it to your mysql user. Finally you write in your my.cnf:

[mysqld]
[...]
init-file=/etc/mysql/myini.sql

and reload your database.

Answer 2

I have the same issue on PHP7.4. I've tried adding: mysqli.allow_local_infile = On to php.ini, .user.ini and .htaccess but it remains stubbornly "off" in spite of restart apache and php-fpm. Is this something that is set at compile time? I'm using EasyApache on WHM/cPanel and CentOS 6

It turns out there is a way to enable it on a per account basis:

/var/cpanel/userdata/username/domain.php-fpm.yaml

--- 
_is_present: 1
pm_max_children: 5
pm_max_requests: 20
pm_process_idle_timeout: 10
php_admin_value_mysqli_allow_local_infile: { name: 'php_admin_value[mysqli.allow_local_infile]', value: 1 }

Disable it globally in php.ini:

; Allow accessing, from PHP's perspective, local files with LOAD DATA statements
; http://php.net/mysqli.allow_local_infile
mysqli.allow_local_infile = Off

Restart PHP-FPM and Apache. This is documented in the cPanel/WHM docs: https://docs.cpanel.net/knowledge-base/php-fpm/configuration-values-of-php-fpm/

You also may need to rebuild your PHP-FPM pool. This is documented here: https://docs.cpanel.net/whm/scripts/the-php_fpm_config-script/ but essentially, as root:

/usr/local/cpanel/scripts/php_fpm_config --rebuild [--domain=domain] --check [--json]