MySQL Bugs: #90291: load_file() will not raise an error if secure_file

Bug #90291	load_file() will not raise an error if secure_file_priv option was not set
Submitted:	3 Apr 2018 15:35	Modified:	4 Apr 2018 10:00
Reporter:	Shahriyar Rzayev	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.7.20, 5.7.21, 5.6.39, 8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi dear all,
Here is the sample test:

CREATE TABLE `t2` (
  `blob_column` blob
) ENGINE=InnoDB DEFAULT CHARSET=latin1

insert into t2(blob_column) values(load_file('/home/shahriyar.rzaev/bats_test.bats'))
Query OK, 1 row affected (0,03 sec)

select hex(blob_column) from t2;
+------------------+
| hex(blob_column) |
+------------------+
| NULL             |
+------------------+
1 row in set (0,00 sec)

It turned out to be --secure_file_priv problem:

select blob_column into dumpfile 'dump.jpeg' from t2;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

So it is clear that with load_file() there should be same error as well.
After setting --secure_file_priv='':

insert into t2(blob_column) values(load_file('/home/shahriyar.rzaev/bats_test.bats'));
Query OK, 1 row affected (0,06 sec)

select hex(blob_column) from t2;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hex(blob_column)                                                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| NULL                                                                                                                                                             |
| 504154485F533D24310A0A6563686F2024504154485F530A0A4074657374202253616D706C65205465737422207B0A0972756E206C73207B504154485F537D0A096563686F20246F75747075740A7D0A |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)

How to repeat:
See description

Suggested fix:
Give same error message with load_file() if secure_file_priv was not set

Changed severity to Serious

Hello Shahriyar,

Thank you for the report.

Thanks,
Umesh

Technically this is a feature request since the current behavior is documented. https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_load-file says:

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.