Table Import with CSV using LOAD DATA LOCAL causes error "LOAD DATA LOCAL INFILE is forbidden"

[jormaster3k]

Describe the bug

Beginning with PHPMyAdmin 5.1.0, I'm no longer able to use the table import function with CSV using LOAD DATA LOCAL.

Attempting to perform an import returns the following error:

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

This works without any issues on PHPMyAdmin 5.0.4

Here is the URL that is impacted:

5.0.4

/tbl_import.php?db=my_database_name&table=my_table_name

5.1.0

/index.php?route=/table/import&db=my_database_name&table=my_table_name

To Reproduce

Steps to reproduce the behavior:

1. Log in to PHPMyAdmin
2. Click on a database
3. Click on a table
4. Click "Import"
5. Select a CSV file from your computer
6. Under format, select "CSV using LOAD DATA"
7. Under format-specific options, select "Use LOCAL keyword"
8. Click "Go"

Expected behavior

I expect that the import will complete successfully, with the following message on the page:

Import has been successfully finished, [N] query executed.

Observed behavior

Process fails with following error

Error
SQL query:
LOAD DATA LOCAL INFILE '/tmp/phpFbFIv3' INTO TABLE my_table_name FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\n';

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

Server configuration

• Operating system: Ubuntu 18.04
• Web server: Apache 2.4
• Database version: MySQL 5.7
• PHP version: 7.2.24
• phpMyAdmin version: 5.1.0

Client configuration

• Browser: Chrome
• Operating system: Windows 10

Additional context

I have installed PHPMyAdmin 5.0.4 and 5.1.0 side-by-side on the same server, in the same parent directory, see below:

/my/webserver/sqladmin/
|-- phpMyAdmin-5.0.4-all-languages
|-- phpMyAdmin-5.1.0-all-languages
|-- phpMyAdmin-5.1.1-all-languages

So there should be no server, PHP, or webserver configuration differences, only a difference in PHPMyAdmin version. This leads me to believe that it's a regression bug with PHPMyAdmin 5.1.0.

I have also verified this is broken in most recent 5.1.1 version as well.

I also verified in my PHP configuration by way of calling phpinfo() in the PHPMyAdmin directory that mysqli.allow_local_infile=On, but that doesn't seem to make a difference.

[williamdes]

Hi @jormaster3k

Thank you for the report, here is some patches that could be related

See: c5e01f8
See: 1e6b740

Do you have a "CSV using LOAD DATA" option in your Import select page ?

If you make this line always true, does it work ?

phpmyadmin/libraries/classes/Controllers/ImportController.php

Line 91 in 7a89e9b

if ($format === 'ldi') {

[jormaster3k]

Hi @williamdes,

Yes, I have the "CSV using LOAD DATA" option, that is exactly what I am using.

Thanks for the change references.

I tried removing the if ($format === 'ldi') check from ImportController.php, ensuring that we define PMA_ENABLE_LDI unconditionally. However, I still received the error.

If in libraries/classes/Dbi/DbiMysqli.php I change the defined('PMA_ENABLE_LDI') check so that we always set the mysqli option MYSQLI_OPT_LOCAL_INFILE to true, the import works!

        if (true) {
            $mysqli->options(MYSQLI_OPT_LOCAL_INFILE, true);
         } else {
            $mysqli->options(MYSQLI_OPT_LOCAL_INFILE, false);
        }

Next I tried simply defining PMA_ENABLE_LDI just before checking it:

        define('PMA_ENABLE_LDI', 1);
        if (defined('PMA_ENABLE_LDI')) {
            $mysqli->options(MYSQLI_OPT_LOCAL_INFILE, true);
        } else {
            $mysqli->options(MYSQLI_OPT_LOCAL_INFILE, false);
        }

With this method, the import works as well, but interestingly I receive a notice in ImportController.php about PMA_ENABLE_LDI already being defined:

Notice in ./libraries/classes/Controllers/ImportController.php#91
Constant PMA_ENABLE_LDI already defined

This leads me to believe that the code in ImportController.php is being triggered after the code in DbiMysqli.php, and accordingly it won't work to try and define PMA_ENABLE_LDI there. Unfortunately I don't know enough about the application in order to determine a better approach :(

[williamdes]

Hi @jormaster3k
I am working on this issue, sorry for not replying earlier
This was pinned in my inbox, I did not forget about it :)

[williamdes]

I found one more bug, I fixed it with 90018d5

And fixed the bug you reported with 84de4a6

[williamdes]

I just built the latest 5.1 version in development (phpMyAdmin 5.1+snapshot), it contains the fix. Feel free to try it and let me know