Preventing Illicit Uploads in MySQL – secure_file_priv


secure_file_priv is one of the primary settings guaranteeing data security in MySQL. Wondering how that’s done? Figure out in this blog post!

Tools used in the tutorial
Tool Description Link
MySQL logo MYSQL 8.0+

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:

Running a query to find the secure_file_priv setting.
Running a query to find the secure_file_priv setting.

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:

Error message for preventing illicit uploads in MySQL.
Error message for preventing illicit uploads in MySQL.

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


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

  • MySQL checks whether we have sufficient privileges to run the query.
  • If we do have sufficient privileges, the query checks the value of the secure_file_priv parameter.
  • 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.

Dbvis download link img

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.

Securing MySQL beyond secure_file_priv.
Your data as an Excel spreadsheet

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.


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.

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.

DbVisualizer logo

If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.

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.

About the author
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world -, frequently speaks at conferences and blogs in multiple places including his blog over at
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published

SQL TRIM: Removing Extra Space Characters From a String

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-05-16

SQL UNION ALL: Keeping Duplicates When Combining Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL UNION 8 min 2024-05-09

SQL UNION Operator: How To Combine Result Sets

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER UNION 9 min 2024-05-06

SQL CONVERT: The Handbook of Data Conversion in SQL

author Antonello Zanini tags CONVERT Data Conversion MySQL SQL SERVER 7 min 2024-05-02

SQL ORDER BY Clause: Definitive Guide

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-04-22

Adding Dates in SQL: A Complete Tutorial

author Antonello Zanini tags DATE DATETIME MySQL POSTGRESQL SQL SERVER 7 min 2024-04-15

Glossary of the SQL Commands You Need to Know

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 12 min 2024-04-11

SUBSTRING_INDEX in SQL Explained: A Guide

author Lukas Vileikis tags MySQL SQL 8 min 2024-04-08

SQL NOT IN: the Good, Bad & the Ugly

author Lukas Vileikis tags MySQL SQL 7 min 2024-04-04

SQL Add to Date Operations: A Complete Guide

author Antonello Zanini tags DATE DATETIME MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-04-01

The content provided on, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗