A Comprehensive Guide on mysqli_set_local_infile_handler Function in PHP

The mysqli extension provides a variety of functions for interacting with MySQL databases. One such function is mysqli_set_local_infile_handler, which allows you to set a callback function for handling LOAD DATA LOCAL INFILE requests. Available since PHP 5.3, this function requires the mysqli extension.

This guide explains how the function works and how to use it effectively in your PHP projects.

What is the mysqli_set_local_infile_handler function?

mysqli_set_local_infile_handler is a built-in PHP function that specifies a custom callback to handle LOAD DATA LOCAL INFILE requests. Instead of letting MySQL read the file directly, you intercept the request and decide exactly what bytes get streamed into the table — so you can validate, transform, or log the data first.

Syntax

mysqli_set_local_infile_handler(mysqli $connection, callable $callback): bool

In object-oriented style the equivalent method is $connection->set_local_infile_handler($callback).

Parameters

• $connection — a mysqli connection object, typically returned by mysqli_connect or new mysqli().
• $callback — the callback that handles each LOAD DATA LOCAL INFILE request. PHP calls it with four arguments and you control the data it returns.

Return value

Returns true on success and false on failure.

The callback signature

The callback receives four arguments and must return the number of bytes read (or a negative value to signal an error):

function callback(
    $stream,        // resource opened by mysqli; you read from it via the buffer
    string &$buffer, // pass-by-reference buffer to fill with data for MySQL
    int $bufferLen,  // maximum number of bytes to place into $buffer
    string &$error   // pass-by-reference error message to set on failure
): int {
    // return number of bytes written to $buffer, 0 at EOF, or < 0 on error
}

Features of mysqli_set_local_infile_handler

The mysqli_set_local_infile_handler function provides a reliable way to intercept and process LOAD DATA LOCAL INFILE requests in PHP. Some of the key features include:

1. Custom Data Processing

The primary feature is allowing you to specify a custom function to handle requests to load data from a local file into a MySQL table. This is useful if you want to perform custom validation, logging, or transformation of the data before it is loaded into the table.

2. Works with Existing Connections

You can attach the handler to any active mysqli connection object. If you have an existing connection, you can use the same object to set a custom callback for handling LOCAL INFILE requests.

How to use mysqli_set_local_infile_handler

Follow these three steps to use the function in your PHP projects.

1. Connect to the MySQL server

First, establish a connection. For modern PHP projects the object-oriented constructor is recommended (see the mysqli_connect chapter for the full options):

<?php

$host = 'localhost';
$user = 'username';
$password = 'password';
$database = 'mydatabase';

$connection = new mysqli($host, $user, $password, $database);

if ($connection->connect_error) {
    die('Connection failed: ' . $connection->connect_error);
}

2. Set the callback function

With a connection in place, register the callback. The callback streams data to MySQL in chunks: each time it is called it should fill $buffer with up to $bufferLen bytes and return how many bytes it wrote, 0 at end of file, or a negative number on error.

Important: For this handler to trigger, the MySQL server must have the local_infile system variable enabled (SET GLOBAL local_infile = 1;), and the PHP client must allow it (mysqli.allow_local_infile = On). LOAD DATA LOCAL INFILE also carries security risks (a malicious server can request arbitrary files), so use it only when necessary and validate paths.

<?php

function custom_local_infile_handler($stream, &$buffer, $bufferLen, &$error) {
    // Read up to $bufferLen bytes from the file stream MySQL opened for us.
    $data = fread($stream, $bufferLen);

    if ($data === false) {
        $error = 'Failed to read from the input file.';
        return -1; // signal an error to MySQL
    }

    // (Optional) validate or transform $data here before handing it off.
    $buffer = $data;

    return strlen($data); // bytes provided; 0 means end of file
}

if (mysqli_set_local_infile_handler($connection, 'custom_local_infile_handler')) {
    echo "Callback function set successfully.";
} else {
    echo "Error setting callback function: " . mysqli_error($connection);
}

The callback custom_local_infile_handler now intercepts every LOCAL INFILE request on this connection. Returning strlen($data) tells MySQL how many bytes are ready; returning 0 ends the transfer; returning a negative value aborts it with the message you placed in $error.

3. Trigger the handler

The callback is automatically invoked when you run a LOAD DATA LOCAL INFILE statement with mysqli_query:

<?php

$sql = "LOAD DATA LOCAL INFILE '/path/to/your/data.csv' INTO TABLE my_table FIELDS TERMINATED BY ','";
$result = mysqli_query($connection, $sql);

if ($result) {
    echo "Data loaded successfully. Rows affected: " . mysqli_affected_rows($connection);
} else {
    echo "Error loading data: " . mysqli_error($connection);
}

The filename in the query is passed to MySQL, which opens the stream and routes the data through your callback. Use mysqli_error to inspect any failure and mysqli_affected_rows to confirm how many rows were imported.

Conclusion

In summary, mysqli_set_local_infile_handler gives you full control over how local file data is ingested into MySQL. By implementing a custom callback, you can securely manage file access, apply data transformations, and maintain detailed logs, ensuring that bulk imports align with your application's security and business rules.

Practice