WampServer

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

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

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons

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

Thank you for your time.

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.





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

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

---------------------------------------------------------------
Documentation Apache - Documentation PHP - Documentation MySQL - Wampserver install files & addons



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

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

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

Hi;

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


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

Hello

Thanks a million ! 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

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

I hope mysql server will be able to hold it all

My extensive thanks did not go trough :/

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

Thanks