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.
TRUNCATE
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 ROLLBACK for TRUNCATE TABLE 🙂
DELETE again!
It’s hard to disagree! But, one question: why just DELETE? 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.
More answers?
If you have a different answer, answer our Twitter thread! We’ll probably add your answer here.
Federico Razzoli
0 Comments