Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

An UPDATE without a WHERE, or something close to it

rachelbythebay.com

Post reply on HN
Print view View original

11–20 of 112 posts

Re: An UPDATE without a WHERE, or something close to it

#11

Post by SketchySeaBeast »

jerf wrote: post #6
blakesterz wrote: post #4

Waaay back in the .com boom days of the late 90s I was working at a place where the CTO did something like: UPDATE users SET password = '23r23r23rdsf'; Somehow he missed the "WHERE email = 'someone';" I forget exactly how many users had their password changed that day. For some reason (maybe MySQL didn't let you cancel an UPDATE like that way back in the old days?) to stop that query he ran into the other room and un…

This isn't quite up there with the "billion dollar" NULL mistake, but in hindsight it would have saved a lot of people a lot of trouble if the SQL grammar for UPDATE and DELETE required a WHERE clause.

Yup. Even if it's just to add a WHERE 1 = 1, at least then you've deliberately loaded and then set off your footgun.

Re: An UPDATE without a WHERE, or something close to it

#12

Post by SketchySeaBeast »

is_true wrote: post #7
blakesterz wrote: post #4

Waaay back in the .com boom days of the late 90s I was working at a place where the CTO did something like: UPDATE users SET password = '23r23r23rdsf'; Somehow he missed the "WHERE email = 'someone';" I forget exactly how many users had their password changed that day. For some reason (maybe MySQL didn't let you cancel an UPDATE like that way back in the old days?) to stop that query he ran into the other room and un…

Always SELECT before making changes. Or don't if you like feeling the adrenaline rush when executing a query in prod.

SELECT, wrap it in a transaction, then either look at row count or SELECT again before COMMIT.

Re: An UPDATE without a WHERE, or something close to it

#13

Post by sokoloff »

is_true wrote: post #7
blakesterz wrote: post #4

Waaay back in the .com boom days of the late 90s I was working at a place where the CTO did something like: UPDATE users SET password = '23r23r23rdsf'; Somehow he missed the "WHERE email = 'someone';" I forget exactly how many users had their password changed that day. For some reason (maybe MySQL didn't let you cancel an UPDATE like that way back in the old days?) to stop that query he ran into the other room and un…

Always SELECT before making changes. Or don't if you like feeling the adrenaline rush when executing a query in prod.

When doing interactive maintenance/querying (which I discouraged, but we all know it happens even if only in “break glass” scenarios), I would generally

  select *
  --update  [or delete]
  from 
  where
which allowed me to execute the entire query as a select (or if I accidentally hit "run the whole buffer" it was safe), but then allowed me to highlight and run just the update (or delete) statement and ensure I had the same where clause as determined by my select statement pre-flighting.

Re: An UPDATE without a WHERE, or something close to it

#14

Post by codeulike »

re: UPDATE without a WHERE

In SSMS (the main query console tool thing for Sql Server) you can highlight a bit of code with the cursor (as if you want to copy/paste it) and press CTRL-E to execute it, its really handy when you've got a big sketchpad-like series of SELECT statements and you're doing exploratory tinkering.

But if your UPDATE statement is accross three lines, its a little bit too easy to accidentally select just the first two lines and not select the third line that has the WHERE clause. Then CTRL-E and you've footgunned yourself.

Its never actually happened to me, but I've often thought it probably has happened to some people.

Re: An UPDATE without a WHERE, or something close to it

#15

Post by bob1029 »

If you put a small program/interface around SQL entry, you can trivially verify that a user entered a reasonable query before allowing to execute.

I think validation that a query is going to do what you want is up to the user of the database, not the database vendor.

Re: An UPDATE without a WHERE, or something close to it

#16

Post by jusssi »

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident.

Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in sql), then going back to the start of the line to remove the safety when I'm done.

Re: An UPDATE without a WHERE, or something close to it

#18

Post by njs12345 »

jerf wrote: post #6
blakesterz wrote: post #4

Waaay back in the .com boom days of the late 90s I was working at a place where the CTO did something like: UPDATE users SET password = '23r23r23rdsf'; Somehow he missed the "WHERE email = 'someone';" I forget exactly how many users had their password changed that day. For some reason (maybe MySQL didn't let you cancel an UPDATE like that way back in the old days?) to stop that query he ran into the other room and un…

This isn't quite up there with the "billion dollar" NULL mistake, but in hindsight it would have saved a lot of people a lot of trouble if the SQL grammar for UPDATE and DELETE required a WHERE clause.

A fun Postgres extension to require this: https://github.com/eradman/pg-safeupdate

Re: An UPDATE without a WHERE, or something close to it

#19

Post by bombcar »

jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

I've taken to using "echo" and then when I run the command run it with a space at the front - you may have to run setopt HIST_IGNORE_SPACE or similar to get this in zsh, for example.

That way ctrl+r won't bring back a destructive command.

Re: An UPDATE without a WHERE, or something close to it

#20

Post by magicalhippo »

jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

I consider any SQL client that doesn't have implicit transactions as broken. Just too easy to make mistakes.

However even with that, I agree with your second point. I always type

    -- commit
in my SQL client and then highlight "commit" and execute only the highlighted text. That way I don't commit anything if I accidentally trigger the "execute all" action.
Post reply on HN