Blocking accidental global updates?

[CouponPages]

We all know that if a user intends to update (or delete) a single record, but leaves out the WHERE clause by mistake, every record in the table is updated (or deleted).

Like most programmers, I use a test environment before making any major code changes, so it's never happened on live data, but does anyone know a uniform way to put a trigger or constraint in that can prevent it?

I've considered all sorts of hacks, but has anyone got a practical solution?

 

[SQLSister]

First, users should never be writing update or insert statements. Your user interface should handle this and error checking at that end can prevent them from submitting a request without a where clause. My user interfaces are always set up so that the user only has one record available to update at a time.

Putting in a trigger will make it difficult that time you do need to change all the records at once, such as when all the prices increase by 10%.

I suppose if you really wanted to do this, you could write an instead of trigger that will check to see that the inserted table only has one record before it does the update. But then you will be limited in that only one record ata time can be updated by anyone.


Questions about posting. See faq183-874

 

[CouponPages]

I agree. I don't allow regular users to write any direct SQL statements, the users I'm talking about are other programmers who update tables using ASP pages.

As long as each of those pages are fully debugged, there would never be a missing WHERE clause. I'm just a bit worried that an eager developer may skip the debug stage and ... oops. If I could wish for one new built-in SQL parameter it would be an "ALL" requirement for doing such a query.

If I say "update xyz set field1=12", it would be great if it generated an error saying "ALL keyword not found. This table requires an ALL parameter or primary key for updates"

Then it would be more like "update xyz set ALL field1=12" or "update xyz set field1=12 where ALL".

Wishful thinking. Thanks for your input.

 

[vongrunt]

If you have some kind of centralized client-side class that runs UPDATE/DELETE statement, do it there. No WHERE clause = raise error.

 

[SQLSister]

Well our developers here aren't allowed to write code directly in the ASP pages. They are required to use stored procedures and it is my responsibility to review any stored procedures anyone else writes. I suppose if I wanted to I could write a query against the system tables looking to see if any of the sps don't have a where clause. But since I write 90% or more of the SPs, revewing the others hasn't been a big issue for me. And frankly I write any code that changes data in the database since I am responsible for data integrity. Our web developers only write sps that display the code.

Reasons for using SPs over dynamic code are plentiful. In the first place they are generally more efficient (a critical factor in web sites, especially), it the second place you no longer need to set permissions at the table level making the system more secure. Third, you as the dba can easily see every single thing that accesss your db and are therefore more aware of how changes to the design might affect the code and allows you to find and optimize pooor code as well as find out and out errors such as not using a where clause. I find it is also generally easier to debug sps than dynamically created code as well.


Questions about posting. See faq183-874

 

[ESquared]

Three-tiered web/database models offer several advantages over two-tiered. One of those is preventing problems like updates which have no WHERE clause.

SQLSister's method of using only SPs is effectively a three-tier model:

- The web application
- talks to stored procedures only
- which do the actual work

There are other ways of implementing this.

In the meantime, to answer your question, you can put in an instead of trigger:

IF (
   SELECT Count(1)
   FROM Inserted I FULL JOIN Deleted D ON I.Key = D.Key
   ) = 1 BEGIN
-- Do the update

END
ELSE BEGIN
-- Don't do the update... return an error code?

END

Another thought is to check the rowcount in an after-trigger and rollback the transaction if it's more than 1. But that could be expensive on a huge update... it's better to prevent it than roll it back.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)