Last updated on 16 September 2020
We asked this question on Twitter: what is the most dangerous SQL statement?
We didn’t get many answers, but all of them were good and will be mentioned here!
But first of all: the question is ambiguous, on purpose. Dangerous could mean that attackers may use this statement to make a big damage, that human mistakes and bugs could cause huge problems, and more. Also, there is no universal answer: it really depends on your business and your database infrastructure.
The purpose of this question is just… thinking. Think like a DBA. Find a good answer and impress us. We also have our answers of course. Each answer is somehow precious, because it highlights a potential problem of database environments, things that we all should be aware about and take care of.
So, let’s start with your answers!
GRANT and DELETE
Despite the emoji,
GRANT is surely one of the most dangerous statements! I won’t write the most dangerous just because sometimes you should legitimately have the permission to run a statement, and yet you could cause some troubles with it. Hands up if it never happened to you!
We agree on
DELETE too, but for different reasons – we’ll explain later. If foreign keys are there, the cascade deletion is probably supposed to happen.
Simple that undoubtedly true! You don’t want to run a
TRUNCATE TABLE against the wrong table, because it’s DDL and it cannot be
ROLLBACKed. Well, actually this is true in MariaDB/MySQL, but not in PostgreSQL and some others.
Oh, and if you’re one of the many persons who think that Oracle does everything better than anyone… no, Oracle does not support
TRUNCATE TABLE 🙂
It’s hard to disagree! But, one question: why just
UPDATE also destroys data.
Our answer: yes, it’s DELETE
Yes, our answer is
DELETE. But our motivation is different.
Suppose an attacker manages to run
DROP DATABASE. If you have a decent error monitoring in place (and I badly hope you have!), you will quickly find out that your database is not there anymore, and you will restore a backup. Yes, it could take time, and the downtime could cause a money loss – depending on the size of your database and the type of your business. But any company that is not a startup should have processes in place to face this problem.
But what if an attacker runs a
DELETE against some targeted rows in your financial tables? Och! This could cause serious trouble. But not now. You will find out that something’s not right in a few months. And at that point, it could be impossible to recover that data!
And don’t forget SELECT!
SELECT can be dangerous in many ways: for example one could read data that should be restricted (but then the problem is
GRANT, as Karl wrote!) or a wrong
SELECT could lead a manager to wrong decisions. But that is not what we mean! Check the following snippet:
START TRANSACTION; SELECT *, SLEEP(60 * 60 * 24 * 365) FROM very_often_written_table FOR UPDATE;
Yes, we are acquiring a write lock on all existing rows for years. Oh, but worry not: the server will crash much sooner than that!
Our point? Even a
SELECT can cause trouble. Database users should always be trusted persons/applications.
If you have a different answer, answer our Twitter thread! We’ll probably add your answer here.