Preventing Illicit Uploads in MySQL – secure_file_priv

Data uploading is a big part of life for any DBA – no matter if you’re a junior or a principal engineer, INSERT queries are always on your way. There is, however, another way to upload files into your database – some of you may have heard of LOAD DATA INFILE as well. LOAD DATA INFILE was specifically designed to allow people to work with big data sets inside of their database instances and it comes with many bells and whistles such as having little overhead, but all queries have something that backs them up – LOAD DATA INFILE has a setting called secure_file_priv.

The secure_file_priv setting efines the directory from where files can be uploaded into our MySQL servers and it can be found in my.cnf or by running a query like the following:

By default, MySQL will respond with a directory that files can be loaded into MySQL from – if the directory used together with the LOAD DATA INFILE query is different, users will face a problem:

This error is partly what prevents illicit uploads in MySQL – it’s MySQL’s way of filtering “legit” and “illegal” uploads.

LOAD DATA INFILE Explained

MySQL works that way because of the internal workings of LOAD DATA INFILE – here’s what happens behind the scenes:

1. MySQL checks whether we have sufficient privileges to run the query.
2. If we do have sufficient privileges, the query checks the value of the secure_file_priv parameter.
3. If the parameter matches the directory that is specified by the user, the query starts to run. Otherwise, we face an error we just saw above.

In many cases, LOAD DATA INFILE looks like so:

As you can see, the query has quite a few parameters specific to itself – there’s the TERMINATED BY parameter specifying where one column ends and another begins, the OPTIONALLY ENCLOSED BY parameter specifying that sometimes values can have certain signs at the beginning and end of themselves, the ESCAPED BY parameter lets us specify an escape character, and we can even ignore a certain amount of lines or rows. It’s not made that way by accident – it’s specifically designed to prevent overhead caused by INSERT queries and to allow people to insert data into a database in a quick and reliable fashion.

secure_file_priv Explained

secure_file_priv is one of the main settings related to LOAD DATA INFILE and SELECT INTO OUTFILE because the setting defines the principal directory where files can be loaded into the database from or created. This setting is ON by default and if that’s the case, all files that are loaded into the database not from the directory that’s specified by MySQL (the default directory is “tmp”) or by the user itself (users can specify any directory) will error out.

The setting can be turned OFF, but such a practice is not recommended for security reasons – by default, MySQL wants to ensure that the file is not publicly accessible in any point in time. Also, if a data breach was to happen and the attacker somehow got access to a user account that has the privilege to load data into the database, his work will be made harder since there will be only one directory that accepts data input – all other directories will produce the same error.

It’s also recommended to only assign the FILE privilege (the privilege controls secure_file_priv modification) to very trusted users.

Securing MySQL Beyond secure_file_priv

In MySQL, security doesn’t begin or end with the parameter and it’s an important, but nonetheless very small piece of security puzzle. To ensure that your database is available, performant, and secure at the same time, consider using tools like the one provided by DbVisualizer – with features each thoughtfully crafted to solve real-world problems of DBAs and developers alike, DbVisualizer is the go-to database tool for developers, database administrators, and engineers alike – it offers ease of access to your databases, comes with an easy-to-use visual query builder, lets you visualize your data, and optimize your database performance all within one solution. From the ability to provide your data in a form of an Excel spreadsheet (see below) to presenting visual explain plans from the database to help you write more efficient queries, DbVisualizer has it all.

Tools alone won’t take you very far though – familiarizing yourself with the security infrastructure of your database management server of choice will be a very good additional step. Make sure to look into the following aspects:

• Access control and reserved accounts – make sure to know what accounts can access specific parts of your database. Consider renaming the root (principal) account for some more obfuscation, and lock accounts that you are sure you’re not going to use for a while (you can always unlock them afterwards.)
• Privileges, roles, and account categories – make sure to only assign privileges that are absolutely necessary for users to have. Also, keep in mind that starting from MySQL 8, roles were introduced as collections of privileges, so if you create a role, assign some permissions to that role, and then assign that role to a specific user, the user is going to have the same privileges as well.
• Password management and account locking – always make sure all of your users have strong passwords and that accounts that you don’t use are in locked condition (this feature is only available in MySQL 8 and above.)
• Backups – back up your data regularly and test all of your backups to make sure they’re fully recoverable in case of a disaster as well. The last thing you want is your infrastructure going down and you not being able to recover any of your data, right?
• Security plugins – if you’re very security-conscious, also consider looking into security plugins offered by MySQL such as the one offered by the enterprise part of the database and others. Some plugins like the MySQL Enterprise Backup solution also provide you with the capability of taking hot, cold, and warm backups that can be stored in different mediums including local storage and the cloud as well, so make sure to evaluate MySQL from that perspective as well.

Summary

MySQL comes with a wide variety of parameters that can be configured and amongst those parameters is a parameter called secure_file_priv. This parameter is the cornerstone of preventing illicit uploads into your MySQL infrastructure whenever you find yourself using LOAD DATA INFILE or SELECT INTO OUTFILE commands – make sure to familiarize yourself with the impacts of this command before using any of the aforementioned commands.

Aside from that, don’t lean on the functionality provided by secure_file_priv alone – keep in mind that MySQL comes with many other tools and functionalities that help you improve the security of its infrastructure, so keep them in mind at all times, and you should be good to go.

Keep an eye out on our blog for more news in the database space, and until next time!

Frequently Asked Questions

What Is secure_file_priv?

secure_file_priv is a parameter within MySQL that helps prevent illicit file uploads by explicitly defining the directory from where files should be uploaded – uploads from other directories will fail.

When Is secure_file_priv Used?

secure_file_priv is used whenever the functions LOAD DATA INFILE or SELECT … INTO OUTFILE are invoked.

Are There Any Other Ways to Protect Against Hacker Attacks?

There are! Consider using data breach search engines like BreachDirectory or employing the security features within SQL clients like DbVisualizer.