Schedule Meeting

a

The most dangerous SQL statement

by | Sep 11, 2020 | SQL Language

Need Help?  Click Here for Expert Support
What scares DBAs?

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

All content in this blog is distributed under the CreativeCommons Attribution-ShareAlike 4.0 International license. You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions: https://creativecommons.org/licenses/by-sa/4.0/

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer. In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software. As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

Making the case for stored procedures

Making the case for stored procedures

MariaDB stored procedures and why they should be improved was the title of my talk at MariaDB Server Fest 2023 in Helsinki. My point was that an improved stored procedures implementation would bring benefits to individual users, the MariaDB community, and the two...

How slow is SELECT * ?

How slow is SELECT * ?

The most widely known query optimisation rule is that developers should always avoid SELECT *. Even when all columns are needed, they should list their names, because more columns could be created in the future. But developers find this really annoying: listing the...

Services

Need Help?  Click Here for Expert Support

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *