LOAD DATA LOCAL INFILE forbidden in PHP
When you attempt to execute LOAD DATA LOCAL INFILE from a PHP application, you often encounter an immediate error stating that the operation is forbidden. This restriction appears specifically when using the MySQL Connector/PHP driver built with mysqlnd (MySQL Native Driver), whereas the standard LOAD DATA INFILE command works as expected.
The root cause lies in the security model of mysqlnd. Unlike the traditional libmysqlclient, which connects directly to the database server and can access files on the server's filesystem if permissions allow, mysqlnd was designed with a strict client-side security boundary. The LOCAL keyword instructs the MySQL server to read the file from your application server rather than its own data directory. By default, mysqlnd disables this functionality entirely to prevent potential security risks where a compromised web application could access sensitive files on the host system.
To resolve this without switching back to legacy drivers like mysqli with libmysqlclient, you must explicitly enable the feature via the allow_local_infile parameter. This setting is not available in the configuration file for mysqlnd connections; instead, it requires passing a specific option during the connection handshake.
Enabling Local File Imports
You can enable this feature by passing an associative array of options when initializing your connection. The syntax varies slightly depending on whether you are using PDO or the native mysqli extension.
Using PDO:
$dsn = 'mysql:host=localhost;dbname=test';
$options = [
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
];
try {
$pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
// Handle connection errors
}
Using Mysqli:
$mysqli = new mysqli("localhost", "user", "password", "test");
// Enable local infile after connection but before executing the query
$mysqli->real_connect_opts = [
'allow_local_infile' => true
];
$mysqli->real_connect();
// Or simply set the attribute if using newer versions that support direct property access
$mysqli->init_command("SET GLOBAL load_data_local_infile=1"); // Often requires root privileges globally
However, the most reliable method for PDO and modern mysqli setups is passing the MYSQLI_OPT_LOCAL_INFILE option or setting the specific attribute before any query execution. For PDO, ensure you use PDO::MYSQL_ATTR_LOCAL_INFILE. For native mysqli, you may need to pass the option during the connection establishment depending on the specific version of the driver installed.
Configuration and Server-Side Considerations
Enabling the option on the PHP side is only half the battle. You must also consider the server-side configuration. The MySQL server itself has a variable named local_infile that defaults to OFF. Even if your application enables the feature, the query will fail unless this server-level setting is adjusted.
To enable it globally, you would modify the my.cnf file:
[mysqld]
local_infile=1
Or set it dynamically via SQL (requires appropriate privileges):
SET GLOBAL local_infile = 1;
It is important to note that enabling local_infile globally affects all connections, including the MySQL CLI client. In a production environment, this increases the attack surface significantly. An attacker who gains access to your database server could potentially leverage this setting if they can also influence the application logic or if the database user has file system permissions.
Security Trade-offs
The default prohibition of LOAD DATA LOCAL INFILE in PHP is a deliberate security hardening measure implemented by the mysqlnd developers. It prevents web applications from inadvertently loading sensitive files (like configuration backups, source code, or private keys) that might reside on the application server's disk but are not part of the database data directory.
If you require this functionality for legacy migration scripts or bulk imports, the trade-off is accepting a potential security risk and ensuring strict access controls. You should limit the database user used for these operations to only have permission to read specific files if your file system permissions allow it. Alternatively, consider moving the large text files into the MySQL data directory itself and using the standard LOAD DATA INFILE command without the LOCAL keyword, provided the server-side local_infile setting allows it.
For high-security environments, relying on external processes or secure file transfer protocols (like SFTP) to pre-load data into a temporary directory accessible by the database service is often a more robust architectural choice than enabling LOAD DATA LOCAL INFILE.