Access to the file system using SQL

I just came across a nice option in MySQL to tap into the file system and obtain valuable information, for example about the existence of files and folders or their contents. This can be very helpful as an alternative to the php function file_exists , since the information about the existence of the file (s) can still be used in the query for further sorting and aggregation functions.

If the associated database user has the FILE privilege , you can use the command


during the query not only check whether a file exists, but also read its contents.

On Windows systems it should be noted that backslashes must be marked accordingly in the path to the file (example: C: \\ Windows \\ System32 \\ drivers \\ etc \\ hosts). The security aspect should also not be completely disregarded, since when the FILE privilege is granted, access to the database automatically means access to the rest of the file system.

To ensure that not only NULL values are returned, you must ensure that the user running MySQL has read privileges.

To access large files, the global property "max_allowed_packet", which is set to 1 MB, must be increased.

mysql -u... -p...
set global max_allowed_packet = 1024 * 1024 * 512;

You can now (after restarting the server) access files that can be up to 512 MB in size.