load_file function is not working in mysql

Question

I am a beginner in mysql and wanted to insert an image in mysql table cell but it is not updating.

update studentDetails set studentPic = LOAD_FILE('/images/picture.jpg') where studentID = 4;

Please help!

Answer 1

A quick excerpt from the documentation of LOAD_FILE() MySQL function:

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.

AS you can see, there are myriads of reason your call to LOAD_FILE() fails:

1. The file must be present on the computer where the MySQL server runs. This is not always the same computer as the one where the PHP script runs (and this happens for security reasons). If they run on different computers then most probably you cannot use LOAD_FILE(). Case closed.

   In theory you could use FTP or SFTP to transfer the file on the computer where MySQL runs but for the same security reasons, you probably don't have access to that computer.

2. Assuming in your case both PHP and MySQL run on the same computer, /images/picture.jpg is the path component of the URL, not a path on the file system. You can use dirname() and __DIR__ to compose the path to the images directory starting from the path of the current script.

3. The user you use to connect to the MySQL server must have the FILE privilege. The privilege is granted to the user by a DBA.

4. The file must be readable by all. This is the easiest part. When you run ls -l on the file, the last three symbols in the column of file rights must be rw- or r-- (there is no point having the executable bit set).

5. The default value of max_allowed_packet system variable is 4 MiB but a DBA can change it. You can run SELECT @@max_allowed_packet FROM dual to find out its current value.

6. If the secure_file_priv system variable is set then the path you provide must be relative to this directory. Again, you can run SELECT @@secure_file_priv FROM dual to find its current value.