#2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
Posted by: PaulR (---.subs.proxad.net)
Date: November 10, 2020 02:55PM

Hello,

I get this error

#2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile

while using

LOAD DATA LOCAL INFILE '/wamp64/somefile.txt' INTO TABLE fscore.num FIELDS TERMINATED BY '\t' ENCLOSED BY '"' LINES TERMINATED BY '\n';

in phpmyadmin sql command window

I searched hours over days troughout the internet and modified every php.ini and my.ini to concord with specifications.


mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile'; show it's ON

read [dev.mysql.com] cryptic to me and still get the same error and much more but no cigar :/


Could someone be kind enough to point out a working solution ?


All services running on wampserver 3.2.3 64bits :
PHPMyAdmin 5.0.2
PHP 7.3.21
MySQL 8.0.21
Windows10 Pro


Thank you smiling smiley

Options: ReplyQuote
Re: #2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
Posted by: Otomatic (Moderator)
Date: November 10, 2020 03:16PM

Hi,

MySQL documentation, do a search on LOAD DATA INFILE and find :
6.1.6 Security Considerations for LOAD DATA LOCAL

MySQL enables clients to restrict local data loading operations to files located in a designated directory.
Restricting Files Permitted for Local Data Loading

See also :
my.ini secure_file_priv
my.ini local_infile

I can't tell you more, as I didn't do a load date infile.

PS : Un forum francophone existe, c'est d'ailleurs celui indiqué par :
Clic-Droit -> Aide -> Forum de support

------------------------------------------------------------------------------------------------------------
Wampserver 3.2.4 32 bit - Apache 2.4.46a - PHP 8.0.0…5.6.40 - MySQL 5.7.32 - MariaDB 10.5.8
Wampserver 3.2.4 64 bit - Apache 2.4.46a - PHP 8.0.0…5.6.40 - MySQL 5.7.32/8.0.22 - MariaDB 10.5.8
PhPMyadmin 5.0.4 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »

Options: ReplyQuote
Re: #2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
Posted by: PaulR (---.subs.proxad.net)
Date: November 10, 2020 05:16PM

Thank you for your reply.

I saw and read that document. It was the link i posted initially. But did not find answer to my question in it :/ Actually maybe it does, but the whole thing is cryptic : To me it sounds like replying how a chronowatch is made and works when simply asking for time !:O

The document does not explecitly say it's impossible, but does not state simply how to achieve this exploit either. I followed and read all links included. They are also quite dry.

I made changes in php.ini & my.ini as stated in other extensive resources i found no effect.

I will look at it again. Keep on looking, but all searches circle around the same posts and docs.

I was hoping to get a clear procedure to allow LOAD DATA LOCAL INFILE on wampserver 3.2.3 on windows 10 Pro on this Forum, thinking i was certainly not the 1st person to try to achieve this exploit grinning smiley

Thank you for your time.

Options: ReplyQuote
Re: #2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
Posted by: maximus23 (---.dynamic.voo.be)
Date: November 10, 2020 06:00PM

Hi,

For wampserver or local mode add in the file my.ini at the end in the section mysqld this instruction :
secure-file-priv = ""

Example for me:

[mysqld]
default_authentication_plugin=mysql_native_password
port = 3306
secure-file-priv = ""

Save and restart wampserver.

smiling smiley



Edited 1 time(s). Last edit at 11/10/2020 06:02PM by maximus23.

Options: ReplyQuote
Re: #2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
Posted by: Otomatic (Moderator)
Date: November 10, 2020 06:36PM

Hi,

By default - although it depends on mysql versions - the secure-file-priv directive in Wampserver points to the tmp folder of the Wampserver installation, for example:
secure_file_priv="E:/wamp64/tmp"
if you read the explanations given in the MySQL documentation, it means that the files you want to import MUST be in this folder and not somewhere else. CQFD

secure_file_priv may be set as follows:
If empty, the variable has no effect. This is not a secure setting.
If set to the name of a directory, the server limits import and export operations to work only with files in that directory. The directory must exist; the server does not create it.
If set to NULL, the server disables import and export operations.

The MySQL documentation, with the links given above, explains things well, just
simply to read, but not diagonally. RTFM

------------------------------------------------------------------------------------------------------------
Wampserver 3.2.4 32 bit - Apache 2.4.46a - PHP 8.0.0…5.6.40 - MySQL 5.7.32 - MariaDB 10.5.8
Wampserver 3.2.4 64 bit - Apache 2.4.46a - PHP 8.0.0…5.6.40 - MySQL 5.7.32/8.0.22 - MariaDB 10.5.8
PhPMyadmin 5.0.4 - MysqlDumper 1.24.5
on W10 and W7 Pro 64 bit
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons
« Ce n'est pas parce qu'ils sont nombreux à avoir tort, qu'ils ont forcément raison. Coluche »
« It's not because they are many to be wrong, they are necessarily right. Coluche »



Edited 1 time(s). Last edit at 11/10/2020 06:39PM by Otomatic.

Options: ReplyQuote
Re: #2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
Posted by: PaulR (---.subs.proxad.net)
Date: November 10, 2020 08:33PM

this is my.ini the one in the mysql8.0.21 subdir

--------------------
; MySQL config file.
;
; MySQL programs look for option files in a set of
; locations which depend on the deployment platform.
; You can copy this option file to one of those
; locations. For information about these locations, see:
; [dev.mysql.com]
;
; In this file, you can use all long options that a program supports.
; If you want to know which options a program supports, run the program
; with the "--help" option.

; The following options will be passed to all MySQL clients
[client]
port =3306
socket = /tmp/mysql.sock
loose-local-infile=1

; Here follows entries for some specific programs
; The MySQL server used as a service
; [dev.mysql.com]
; If the service-installation command specifies a service name other
; than MySQL following the --install option, the server uses that
; service name. It reads options from the [mysqld] group and the group
; that has the same name as the service in the standard option files.
; This enables you to use the [mysqld] group for options that should
; be used by all MySQL services, and an option group with the service
; name for use by the server installed with that service name.
; This [...] section has the name of the service
[wampmysqld64]
;skip-grant-tables
default_authentication_plugin=mysql_native_password
port =3306
secure-file-priv = ""
socket = /tmp/mysql.sock
key_buffer_size = 256M
max_allowed_packet = 64M
local_infile=ON
secure-file-priv = ""

; Added to reduce memory used (minimum is 400)
; See Documentation:
; [dev.mysql.com]
table_definition_cache = 600

sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
;Path to mysql install directory
basedir="d:/wamp64/bin/mysql/mysql8.0.21"
log-error="d:/wamp64/logs/mysql.log"
;Verbosity Value 1 Errors only, 2 Errors and warnings , 3 Errors, warnings, and notes
log_error_verbosity=2
;Path to data directory
datadir="d:/wamp64/bin/mysql/mysql8.0.21/data"

;Path to the language
;See Documentation:
; [dev.mysql.com]
lc-messages-dir="d:/wamp64/bin/mysql/mysql8.0.21/share"
lc-messages=en_US

; Default storage engine that will be used if the engine is not specified when creating tables.
; This does not prevent the creation of InnoDB tables, just specify ENGINE = InnoDB
default-storage-engine=MYISAM

;Disabling X Plugin
mysqlx=0
;To avoid warning messages
skip-ssl
secure_file_priv=""

explicit_defaults_for_timestamp=true

; Set the SQL mode. See Documentation:
; [dev.mysql.com]
sql-mode=""
;sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE"

; Disable Federated by default
skip-federated

; Replication Master Server (default)
; binary logging is required for replication
skip-log-bin
;log-bin="c:/wamp64/logs/mysql-bin"

; binary logging format - mixed recommended
;binlog_format=mixed

; required unique id between 1 and 2^32 - 1
; defaults to 1 if master-host is not set
; but will not function as a master if omitted
server-id = 1

; Replication Slave (comment out master section to use this)

; New for MySQL 5.6 if no slave
skip-slave-start

; The InnoDB tablespace encryption feature relies on the keyring_file
; plugin for encryption key management, and the keyring_file plugin
; must be loaded prior to storage engine initialization to facilitate
; InnoDB recovery for encrypted tables. If you do not want to load the
; keyring_file plugin at server startup, specify an empty string.
early-plugin-load=""

innodb_data_file_path = ibdata1:12M:autoextend
; You can set .._buffer_pool_size up to 50 - 80 %
; of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
; Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_flush_method=normal

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
local-infile=ON
no-auto-rehash
; Remove the next comment character if you are not familiar with SQL
;safe-updates
secure-file-priv = ""

[isamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer_size = 2M
write_buffer_size = 2M

[myisamchk]
key_buffer_size = 20M
sort_buffer_size_size = 20M
read_buffer_size = 2M
write_buffer_size = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld]
local_infile=ON
default_authentication_plugin=mysql_native_password
port =3306
secure-file-priv = ""


-----------------------------------------


i made all, if not many of the modifications proposed on different sources. Saved, Closed and restarted wamp64 but no avail :/

i still get :

MySQL said: Documentation

#2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile

So yes, i may be overlooking something ... As a matter of fact i must be !:O

thank you for your help smiling smiley

Options: ReplyQuote
Re: #2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
Posted by: PaulR (---.subs.proxad.net)
Date: November 10, 2020 08:41PM

The error message points to this section of php.ini file. I uncommented the 2 last lines.

[MySQLi]

; Maximum number of persistent links. -1 means no limit.
; [php.net]
mysqli.max_persistent = -1

; Allow accessing, from PHP's perspective, local files with LOAD DATA statements
[php.net]
mysqli.allow_local_infile = ON

Options: ReplyQuote
Re: #2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
Posted by: maximus23 (---.dynamic.voo.be)
Date: November 10, 2020 11:03PM

Hi;

See video : [www.smf-fr.com]


For me it works very well with this directive. Local mode for security.

smiling smiley

Options: ReplyQuote
Re: #2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
Posted by: PaulR (---.subs.proxad.net)
Date: November 11, 2020 10:39AM

Hello

Thanks a million !smiling smiley This last proposition worked for me !!!

For ref this worked for me : [www.smf-fr.com]

I have TAB delimited data. Inserting '\t' as shown in clip returned an error. Quick search surfaced

[dev.mysql.com]. It says

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

And it worked fine. Thanks again for your support smiling smiley

I have > 3M rows per file. 4 files per year.

I hope mysql server will be able to hold it all smiling smiley

Options: ReplyQuote
Re: #2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile
Posted by: PaulR (---.subs.proxad.net)
Date: November 13, 2020 01:43PM

My extensive thanks did not go trough :/

I wanted to notify that the later procedure you kindly provided works fine.

Thanks

Options: ReplyQuote


Sorry, only registered users may post in this forum.