Bug #90291 load_file() will not raise an error if secure_file_priv option was not set
Submitted: 3 Apr 15:35 Modified: 4 Apr 10:00
Reporter: Shahriyar Rzayev (OCA) 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

[3 Apr 15:35] Shahriyar Rzayev
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
[3 Apr 15:35] Shahriyar Rzayev
Changed severity to Serious
[4 Apr 10:00] Umesh Shastry
Hello Shahriyar,

Thank you for the report.

Thanks,
Umesh
[30 May 12:45] Georgi Kodinov
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.