Software, technology, sysadmin war stories, and more. Feed
Tuesday, October 12, 2021

An UPDATE without a WHERE, or something close to it

I know I've already commented on a certain site's big outage twice now, but I have one more bit of rumor to share, and it apparently invalidates what I had heard previously. That is, it may not have been a matter of "show X Y" vs. "clear X Y", but rather some kind of automation that went a bit too far.

Essentially, the story is now this: let's say you want to do something to a given item, be that a router, switch, server, or whatever. You go to some management interface for it, and say, okay, take this thing out of service, shut it down, reboot it, drain it, set it on fire, whatever. But, what if the interface only asked you to specify which one somehow?

Then, what if you didn't have to actually type something in to hit the button? Maybe you forgot, or something else happened, and you didn't fill it in, so when you did that, it said "okay, cool then" and did all of them instead?

Go to tool, type in target, hit button, single target is affected.

Go to tool, don't type in target, hit button, ALL targets affected.

If true, that sounds like a violation of the principle of least surprise, particularly when the impact can be so big.

It sounds like another kind of "footgun" (you know, something you can use to shoot yourself in the foot) from the world of databases: an UPDATE without a WHERE.

For the not-SQL-inclined in the crowd, you can tell a database to do something like this:

UPDATE my_table SET invalid = true WHERE asset_id = 1012;

It'll then go and set that bit on any column where asset_id is equal to 1012. If that column (asset_id) has unique values, then that's one row. If nothing matches it, then no rows will be changed.

Imagine what happens when you forget the WHERE. It's the last thing on the line as shown, so you could send off the command early and it would totally work. It would look like this:

UPDATE my_table SET invalid = true;

And... guess what, without that WHERE, it just set that bit everywhere. Everything is marked invalid, and your business might suffer for it.

There's plenty more on this topic that I won't go into here, like the "I am a dummy" thing in MySQL, adding a limit (to the query or otherwise), and so on. Just know that it's a pattern, and you will see it again.

Final note on the "do all the things" thing: maybe the tool actually is supposed to let you do that. In that case, maybe it would be good if it said something like "you are about to drain 123,456 devices, so type in that exact number to proceed". You know, that kind of thing. It tends to add enough of a pause to prevent some disasters.