Can the SQL EXPLAIN statement be a DoS vector and how to mitigate this threat? Find out here.
Preface
During preFOSDEM MySQL Belgian Days 2025, Alexander Rubin (Principal Security Architect @ AWS, leading RDS Red Team) explored an interesting use case of the SQL EXPLAIN statement. During his talk, Alexander has dived deeper into a 2020 blog on Percona written by Peter Zaitsev. The blog was titled Uncommon Sense MySQL – When EXPLAIN Can Trash Your Database and the concept of the blog was to explore whether the EXPLAIN statement, when run together with a SELECT query, executes the SELECT query in question.
SQL EXPLAIN as a DoS Vector – The Concept
The concept of the SQL EXPLAIN statement provided by Alexander Rubin was that once you run an EXPLAIN query on a SELECT, when executing certain SQL queries it can sometimes hang because it’d be actually executing the SQL query in question. The SQL query provided was as follows:
EXPLAIN SELECT * FROM (SELECT SLEEP(5000) AS a) b;
Alexander Rubin then explained the reason for that happening: according to him, the SQL EXPLAIN statement should show how many records it will scan. Since the SQL query provided on the blog by Peter Zaitsev (and shown above) is a subquery, the database has no idea how many records are there in a table because the thing that is supposed to be a table (the part of the SQL EXPLAIN statement after the FROM clause) is actually a subquery. As a result, SQL EXPLAIN will go ahead and execute any statement, materialize it, obtain the number of records, and then output the result set, leaving your database (and your application) potentially vulnerable to a Denial of Service attack.
Why Is This a Problem and How to Fix It?
The query given above could run for more than an hour (5000 seconds is more than 83 minutes), creating an accidental (or intentional) Denial of Service attack vector. One way to protect against it would be to terminate all queries that run longer than X amount of seconds, and the other would be to just not provide subqueries in places where the SQL EXPLAIN statement expects a table. If you would prefer to watch Alexander Rubin explain the concept, you can do so on YouTube:
Protecting Your Data Beyond DoS
DoS, unfortunate as that may sound, is only one attack vector that your applications have to be guarded against. These days, applications (and people developing them) are constantly under threat via various attack vectors ranging from social engineering to SQL injection, cross site scripting, and Cross-Site Request Forgery (CSRF.) Basic things like input validation will help, but they can only do so much. That’s why people turn to data breach search engines and their API appliances such as the one available in BreachDirectory:
Data breach search engines are immensely useful in this regard because they kill a couple of birds with one stone:
- They allow you to check whether your email address, username, or IP address is at risk of identity theft by allowing you to scan through publicly available data breaches to ensure that your data is safe (you can also search for the domain or title your website if you desire.)
- If your data is at risk, the BreachDirectory data breach search engine will inform you and provide you with valuable advice on what actions to take.
- Should you desire to obtain API access towards the data breach search engine, the BreachDirectory API is available 24/7 and it will provide you with JSON-based responses to your queries allowing you to integrate it into your own systems.
- Should you necessitate education on cyber security matters, the BreachDirectory blog will also always be at hand educating you on the threats and remediation measures on the most popular attacks threatening web applications and beyond.
Give the BreachDirectory data breach search engine a spin today – and take care of your SQL EXPLAIN statements, too!
Summary
In some cases, the SQL EXPLAIN statement can be considered to be a DoS vector because once you would provide a subquery in place of a table, the SQL EXPLAIN statement would actually execute the SQL query in question, and, if something like SLEEP(seconds) would be provided, the SQL EXPLAIN statement could become an intentional (or accidental) vector of a Denial of Service attack. The query would hang because it would actually be executed and you would end up waiting the described amount of seconds.
To avoid that, make sure to limit the query execution time if necessary and be aware of the fact that you should provide a table instead of a subquery after the FROM clause, too.
Frequently Asked Questions
How Can the SQL EXPLAIN Statement Become a DoS Vector?
The SQL EXPLAIN statement can become a DoS vector if one provides a SLEEP(seconds) statement in a subquery in place of a table.
How to Eliminate the SQL EXPLAIN DoS Vector?
One way to protect against such a threat would be to terminate all SQL queries that run longer than X amount of seconds, and the other would be to just not provide subqueries in places where the SQL EXPLAIN statement expects a table.
What Can I Do to Protect Myself on the Web?
Aside from limiting the number of seconds your SQL statements should take to execute, consider using prepared statements to protect your application and database from SQL injection, validating user input and using functions that replace HTML entities with “>” (greater than), “<” (less than) and other values, implementing CSRF tokens to prevent Cross-Site Request Forgery, and using data breach search engines like BreachDirectory to make sure that your data is not stolen by cybercriminals. If it is, the BreachDirectory data breach search engine will advise you on what to do next, too.