LOAD DATA LOCAL INFILE 禁止在... PHP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7638090/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LOAD DATA LOCAL INFILE forbidden in... PHP
提问by Richard Pérez
I am trying to use LOAD DATA INFILEto insert some records into a table. Unfortunately, it's not working.
我正在尝试LOAD DATA INFILE将一些记录插入表中。不幸的是，它不起作用。
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.
它使用 MySQL 客户端程序和 PHP 应用程序工作。通过这种方式，它将在我的 MySQL 安装的数据目录中查找该文件。
Now if I try to execute the instructions using the LOCALoption, it only works if I use the mysql client, but not from PHP:
现在，如果我尝试使用该LOCAL选项执行指令，则它仅在我使用 mysql 客户端时才有效，而在 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:
再次.. 它适用于 MySQL 客户端，但不适用于 PHP 应用程序...我收到此错误：
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.
我读到问题与PHP的编译和使用mysqlnd有关。我使用的是 PHP 5.3.8 和 MySQL 5.5.15，但我还没有找到解决方案。
Additional information: until now the only help I've found was an open PHP bug:
附加信息：到目前为止，我发现的唯一帮助是一个开放的 PHP 错误：
回答by Nomas Prime
Check docs http://php.net/manual/en/ref.pdo-mysql.php.
检查文档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,
));
回答by Sebastian
had this problem today and solved it by setting the following in php.ini
今天遇到了这个问题并通过在php.ini中设置以下内容解决了它
mysqli.allow_local_infile = On
回答by Gadelkareem
Easier work around is to use exec()
更简单的解决方法是使用 exec()
exec("mysql -u myuser -pMyPass -e \"USE mydb;TRUNCATE mytable;LOAD DATA INFILE '" . $file . "' IGNORE INTO TABLE mytable;\"; ");
回答by MaX
I didn't get the exact error you get, but you need no ensure the following:
我没有得到你得到的确切错误，但你不需要确保以下几点：
Enable by adding to your my.cnf:
通过添加到您的 my.cnf 启用：
[mysql]
local-infile=1
[mysqld]
local-infile=1
Tell the connection in PHP that it may use LOCAL INFILE
告诉 PHP 中的连接它可能使用 LOCAL INFILE
Using mysql:
使用 mysql：
mysql_connect(server,user,code,false,128); // 128 enables LOCAL INFILE
mysql_select_db(database);
Using mysqli:
使用 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
授予 MySQL 用户 FILE 权限
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).
但是，当使用 LOCAL 时，这不是必需的。LOCAL 表示该文件位于客户端服务器（安装了 PHP 的位置），否则它会查看服务器位置（安装 MySQL 的位置）。
GRANT FILE ON *.* TO 'mysql_user'@'localhost'
回答by Clive
According to the MySQL manualMySQL must be compiled with --enable-local-infile. From a comment at that link:
根据MySQL 手册MySQL 必须使用--enable-local-infile. 来自该链接的评论：
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'
您必须使用 MySQL 的完整路径编译 PHP，否则它将使用它的内部处理程序，这些处理程序不适用于“新的”负载数据。
--with-mysql=/usr/local/mysql（假设您的 MySQL 位于此处）
您必须使用选项“--local-infile=1”启动 MySQL 守护进程
回答by Michael H?rtl
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).
在 RDS 上访问 MySQL 时，我在 EC2 Ubuntu 12.04 LTS 实例上遇到了完全相同的问题：LOAD DATA LOCAL INFILE...在 mysql 控制台上工作正常，但在 PHP 上无法正常工作。偶然我发现它在另一台使用MariaDB 的几乎相同的机器上运行良好（替代 MySQL 的二进制兼容下降）。
So i replaced the MySQL clients with the ones from MariaDB and it worked.
所以我用 MariaDB 的客户端替换了 MySQL 客户端并且它工作了。
回答by michael kinuthia
LOAD DATA LOCAL INFILEexecutes 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.
LOAD DATA LOCAL INFILE无论警告如何，都会执行。它适用于 mysql 客户端，因为它允许执行查询，而忽略警告。虽然它后来打印出警告。但它在 PHP 中拒绝，因为警告将停止脚本。
回答by tanay jha
Easiest solution, that may work on some servers is to remove LOCAL like:
可能适用于某些服务器的最简单的解决方案是删除 LOCAL，例如：
Original:LOAD DATA LOCAL INFILE New/ It should be: LOAD DATA INFILE
原文：LOAD DATA LOCAL INFILE 新建/应该是：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'.
奇怪，但我发现这个解决方案可以在我的本地机器上使用 xampp 但它在带有 CentOS 的实时服务器上不起作用，所以我将恢复代码并添加“本地”。
回答by dryobs
If you use an Ubuntu server, you can try to install php5-mysqlnd:
如果您使用 Ubuntu 服务器，您可以尝试安装php5-mysqlnd：
sudo apt-get install php5-mysqlnd
sudo apt-get install php5-mysqlnd
回答by chris
2019+ relevant answer with a bit more background:
2019+ 相关答案，有更多背景信息：
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).
在 PHP >7.2.16 和 >7.3.3 中mysqli.allow_local_infile，控制它的默认 ini 配置从“1”更改为“0”（因此现在默认禁用）。
This directive is only configurable via PHP_INI_SYSTEMso ini_set()will notwork.
这个指令是唯一配置通过PHP_INI_SYSTEM这样ini_set()将无法正常工作。
The only option is to add the following directive to your php.ini file, not forgetting to reload apache.
唯一的选择是将以下指令添加到您的 php.ini 文件中，不要忘记重新加载 apache。
[MySQLi]
mysqli.allow_local_infile = On
