An UPDATE without a WHERE, or something close to it • Hacker News Board

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

#21

Post by mprovost » Fri, Oct 29, 2021, 1:45 PM UTC

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…

One quick trick I use a lot in the shell is to run a known safe command with the argument first, and then run the dangerous command with the the !$ variable (the last argument of the previous command) so there's no possibility of a mistake in copying and pasting. Something like: `ls /tmp/junk; rm !$`.

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

#22

Post by willcipriano » Fri, Oct 29, 2021, 1:46 PM UTC

When working in prod, first I write a select to grab all of the data I wish to modify, confirm that is correct, then add the set portion, then add the UPDATE last. Never had a issue and I work in prod all the time.

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

#23

Post by andreareina » Fri, Oct 29, 2021, 1:52 PM UTC

RenThraysk wrote: post #17

With databases, start a transaction first, then update, and look at the number of rows affected. If it's an oh shit moment, rollback, if not commit.

And be sure that you're not using something that implicitly commits the transaction, such as TRUNCATE in Redshift.

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

#24

Post by dcminter » Fri, Oct 29, 2021, 1:53 PM UTC

magicalhippo wrote: post #20

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.

Yes, but mind you I once locked up the database for my colleagues by not realising the consequences of implicit transactions in a DB client.

It was a long time ago, but I think it was with SQLPlus on Oracle - and presumably staging or something non-prod since it's not a particularly vivid memory!

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

#25

Post by tyingq » Fri, Oct 29, 2021, 1:54 PM UTC

UPDATE also supports ORDER BY and LIMIT, which is kind of odd when you first see it.

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

#26

Post by hanselot » Fri, Oct 29, 2021, 1:59 PM UTC

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 use vim bindings for shell, so doing dangerous things I usually open it in edit mode and have to physically do :wq to execute. Much less chance of being destructive, and you can easily drop the output into clipboard or a file from here.

Otherwise, simply having a mindset of, I'm doing dangerous things also goes a long way.

Never do things if you are in a panicked/frantic state.

Edit: I guess running galera is actually a blessing. I can't run any write/update methods on our qa/prod data...

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

#27

Post by kwoff » Fri, Oct 29, 2021, 2:01 PM UTC

tyingq wrote: post #25

UPDATE also supports ORDER BY and LIMIT, which is kind of odd when you first see it.

Speaking of LIMIT. Another potential "footgun", but with SELECT, is "select * from table limit1". Instead of limiting the number of rows to 1, you aliased the table as "limit1".

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

#28

Post by lbriner » Fri, Oct 29, 2021, 2:03 PM UTC

bob1029 wrote: post #15

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.

Yes, in an ideal world but so many companies don't value the small details which can make everything nicer. I have spent the day trying to work out why a container runs in one environment and not another. I still don't because basics like easy log access, dns debugging tools etc. are not all part of Kubernetes.

OK, this isn't about breaking an entire network but small details can be super helpful. I have only found a small number of companies who seem to care about error messages.

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

#29

Post by lbriner » Fri, Oct 29, 2021, 2:05 PM UTC

codeulike wrote: post #14

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 sel…

I have, although nothing deadly serious. I guess that SQL statements in a text file are not really the correct management tool for a production database. If we are needing to do these sorts of things, we should have an admin app or tool that can ensure we do things correctly.

I haven't built one so I'm not complaining!

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

#30

Post by ComodoHacker » Fri, Oct 29, 2021, 2:10 PM UTC

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…

Were those days so early, backups weren't mandatory yet?