SQL Server 2000 - preventing multiple update

[aaj]

Hi all

We had a small problem when an ASP web page had a missing 'where' statement
and updated all the records in the table. Luckily we could retrieve all the
data from the backups.

How do you guys prevent this from happening in your large systems. Is there
some teqnique for controlling this, I would imagine if you had thousands of
records in a table and some one made a programming error, then the
consequences would be disastrous.

is there a setting within SQL Server that could force SQL update commands to
be limited to a criteria and if no criteria is supplied then reject the
command

thanks in advance

Andy

[Lyle Fairfield]

Re: SQL Server 2000 - preventing multiple update

"aaj" <aaj@aaj.com> wrote in
news:419b622e$0 $9628$afc38c87@ news.easynet.co .uk:
[color=blue]
> Hi all
>
> We had a small problem when an ASP web page had a missing 'where'
> statement and updated all the records in the table. Luckily we could
> retrieve all the data from the backups.
>
> How do you guys prevent this from happening in your large systems. Is
> there some teqnique for controlling this, I would imagine if you had
> thousands of records in a table and some one made a programming error,
> then the consequences would be disastrous.
>
> is there a setting within SQL Server that could force SQL update
> commands to be limited to a criteria and if no criteria is supplied
> then reject the command
>
> thanks in advance
>
> Andy[/color]

Some developers have a production database and development database. They
have a similar separation of production code and development code.
When things work properly and have been tested many times, they are moved
from development to production.

I am guessing that you are using T-SQL text strings in your ASP. It may be
safer (and more efficient) to use Stored Procedures. One can default
parameters, and one may choose to default them to something which causes no
change. For instance,
@Identity int=0
and
UPDATE WHERE IdentityKey = @Identity
is likely to update nothing when no parameter is sent.

[Erland Sommarskog]

Re: SQL Server 2000 - preventing multiple update

aaj (aaj@aaj.com) writes:[color=blue]
> We had a small problem when an ASP web page had a missing 'where'
> statement and updated all the records in the table. Luckily we could
> retrieve all the data from the backups.
>
> How do you guys prevent this from happening in your large systems. Is
> there some teqnique for controlling this, I would imagine if you had
> thousands of records in a table and some one made a programming error,
> then the consequences would be disastrous.[/color]

As Lyle said: testing, testing and again testing is what you need. Testing
and code review.

This is a sort of error which is difficult to protect yourself against.
You could have a trigger on the table that compare @@rowcount with the
number of rows in the table, and if they are equal, you raise an error
and rollback. But what if you really want to update all rows in a table?
And what if the WHERE clause is there, but is incomplete:

UPDATE Orders
SET Shipped = 1
WHERE OrderDate = @date

But someone forgot:

AND CustomerID = @custid

And counting the rows in big tables in each update is kind of expensive.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[aaj]

Re: SQL Server 2000 - preventing multiple update

Hi Guys

Thanks for both replies

We check the software on a test bed before going live, and this is the first
time that this type of thing has slipped through. Unfortunately, there are
only 2 of us and the usual daily pressures sometimes things don't always get
done as they should......

luckily the problem was fixable, and we could piece together the missing
bits from old backups. It was just unfortunate, that a good few weeks had
passed before anyone noticed!!!

We toyed with the idea of using stored procedures and having the web pages
pass parameters, but if they are being created all the time, they could also
be open to the same type of mistake.

I think we will just have to be more careful in future. That said, its quite
worring how much havoc a dodgy update statement could cause, especially as
Erland says, WHERE OrderDate = @date while missing out AND CustomerID
= @custid, where it does look at first glance to be ok

thanks for the advice chaps

Andy



"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns95A4F38 709458Yazorman@ 127.0.0.1...[color=blue]
> aaj (aaj@aaj.com) writes:[color=green]
>> We had a small problem when an ASP web page had a missing 'where'
>> statement and updated all the records in the table. Luckily we could
>> retrieve all the data from the backups.
>>
>> How do you guys prevent this from happening in your large systems. Is
>> there some teqnique for controlling this, I would imagine if you had
>> thousands of records in a table and some one made a programming error,
>> then the consequences would be disastrous.[/color]
>
> As Lyle said: testing, testing and again testing is what you need. Testing
> and code review.
>
> This is a sort of error which is difficult to protect yourself against.
> You could have a trigger on the table that compare @@rowcount with the
> number of rows in the table, and if they are equal, you raise an error
> and rollback. But what if you really want to update all rows in a table?
> And what if the WHERE clause is there, but is incomplete:
>
> UPDATE Orders
> SET Shipped = 1
> WHERE OrderDate = @date
>
> But someone forgot:
>
> AND CustomerID = @custid
>
> And counting the rows in big tables in each update is kind of expensive.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]

[Erland Sommarskog]

Re: SQL Server 2000 - preventing multiple update

aaj (aaj@aaj.com) writes:[color=blue]
> We check the software on a test bed before going live, and this is the
> first time that this type of thing has slipped through. Unfortunately,
> there are only 2 of us and the usual daily pressures sometimes things
> don't always get done as they should......[/color]

You are not the first shop experience that. Shortage of resources is
commonplace. And of course, no matter how you test, there is always case
you haven't thought of...
[color=blue]
> We toyed with the idea of using stored procedures and having the web
> pages pass parameters, but if they are being created all the time, they
> could also be open to the same type of mistake.[/color]

There is an advantage though, in the fact that the SQL code is confined
to the stored procedure, makes them easier to review. Dynamically build
SQL statements in client code which may be interleaved by client syntax
is more difficult to get an overview over.
[color=blue]
> I think we will just have to be more careful in future. That said, its
> quite worring how much havoc a dodgy update statement could cause,
> especially as Erland says, WHERE OrderDate = @date while missing out
> AND CustomerID
>= @custid, where it does look at first glance to be ok[/color]

An even more ugly case is when you have:

FROM tbla a
JOIN tblb b ON a.col = a.col -- Oops! Wrong alias!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Andy]

Re: SQL Server 2000 - preventing multiple update

Lyle Fairfield <dontspam@me.co m> wrote in message news:<Xns95A46A 3C34F64bff2544d 56aaa4a28914@21 6.221.81.119>.. .[color=blue]
> "aaj" <aaj@aaj.com> wrote in
> news:419b622e$0 $9628$afc38c87@ news.easynet.co .uk:
>[color=green]
> > Hi all
> >
> > We had a small problem when an ASP web page had a missing 'where'
> > statement and updated all the records in the table. Luckily we could
> > retrieve all the data from the backups.
> >
> > How do you guys prevent this from happening in your large systems. Is
> > there some teqnique for controlling this, I would imagine if you had
> > thousands of records in a table and some one made a programming error,
> > then the consequences would be disastrous.
> >
> > is there a setting within SQL Server that could force SQL update
> > commands to be limited to a criteria and if no criteria is supplied
> > then reject the command
> >
> > thanks in advance
> >
> > Andy[/color]
>
> Some developers have a production database and development database. They
> have a similar separation of production code and development code.
> When things work properly and have been tested many times, they are moved
> from development to production.[/color]

Where possible I use three databases and have three code versions.
Development, testing and live.
Testing can be a long drawn out process.
In some instances you can find waiting for the testing to be completed
before carrying on with the next piece of development would mean
halting development.
Clients can get rather huffy when their "highly paid contractor" can
be seen sitting there twiddling his thumbs waiting for the tester/test
team to do their stuff.
I also do a code back up prior to each separate chunk of work.

As well as testing something yourself it is always a good idea to get
someone else to test. As the designer/developer you know how the
thing is "supposed" to work. It is often difficult to think of ways
to break it. Actually trying to break one's own creation is also a
mental hurdle to cross.

Stored procedures are definitely the way to go with web pages. Look
up "code injection" on the microsoft msdn site. You're wide open to
hacking if you use just straight sql strings out of an ASP page.
Avoiding code injection is not just a nice-to-have.

[MissLivvy]

Re: SQL Server 2000 - preventing multiple update

This story reminds me of a funny thing that happened at a company I worked
for recently. A web application bug indirectly caused a stored procedure
parameter to get passed a NULL value, which ultimately resulted in all of
the customer's address records getting deleted from the database! LOL! This
happened in the production environment at a name brand recognizable company.
Developers were responsible for developing and testing code on their local
workstation, after which the code would be promoted to QA for further
testing before getting released to production. It took our QA guy at least a
day to be able to reproduce the bug and we figured out that the bug had been
in production for at least a year before a user finally hit it. The
accidentally deleted data were restored from the most recent backup.

I have other funny disastrous stories to tell. I'm sure we all do ...


"aaj" <aaj@aaj.com> wrote in message
news:419b622e$0 $9628$afc38c87@ news.easynet.co .uk...[color=blue]
> Hi all
>
> We had a small problem when an ASP web page had a missing 'where'[/color]
statement[color=blue]
> and updated all the records in the table. Luckily we could retrieve all[/color]
the[color=blue]
> data from the backups.
>
> How do you guys prevent this from happening in your large systems. Is[/color]
there[color=blue]
> some teqnique for controlling this, I would imagine if you had thousands[/color]
of[color=blue]
> records in a table and some one made a programming error, then the
> consequences would be disastrous.
>
> is there a setting within SQL Server that could force SQL update commands[/color]
to[color=blue]
> be limited to a criteria and if no criteria is supplied then reject the
> command
>
> thanks in advance
>
> Andy
>
>
>[/color]

[aaj]

Re: SQL Server 2000 - preventing multiple update

Well some thing I've just read has put out little problem into
perspective!!!


I have just been reading MID (Managing information and documents) magazine
and on page 7 it would seem that someone at the UK tax office has deleted
ALL this years PAYE returns and they can't retrieve them from the backups. I
wonder if some one forgot the where clause...


Andy


"aaj" <aaj@aaj.com> wrote in message
news:419c52f7$0 $9974$afc38c87@ news.easynet.co .uk...[color=blue]
> Hi Guys
>
> Thanks for both replies
>
> We check the software on a test bed before going live, and this is the
> first time that this type of thing has slipped through. Unfortunately,
> there are only 2 of us and the usual daily pressures sometimes things
> don't always get done as they should......
>
> luckily the problem was fixable, and we could piece together the missing
> bits from old backups. It was just unfortunate, that a good few weeks had
> passed before anyone noticed!!!
>
> We toyed with the idea of using stored procedures and having the web pages
> pass parameters, but if they are being created all the time, they could
> also be open to the same type of mistake.
>
> I think we will just have to be more careful in future. That said, its
> quite worring how much havoc a dodgy update statement could cause,
> especially as Erland says, WHERE OrderDate = @date while missing out
> AND CustomerID = @custid, where it does look at first glance to be ok
>
> thanks for the advice chaps
>
> Andy
>
>
>
> "Erland Sommarskog" <esquel@sommars kog.se> wrote in message
> news:Xns95A4F38 709458Yazorman@ 127.0.0.1...[color=green]
>> aaj (aaj@aaj.com) writes:[color=darkred]
>>> We had a small problem when an ASP web page had a missing 'where'
>>> statement and updated all the records in the table. Luckily we could
>>> retrieve all the data from the backups.
>>>
>>> How do you guys prevent this from happening in your large systems. Is
>>> there some teqnique for controlling this, I would imagine if you had
>>> thousands of records in a table and some one made a programming error,
>>> then the consequences would be disastrous.[/color]
>>
>> As Lyle said: testing, testing and again testing is what you need.
>> Testing
>> and code review.
>>
>> This is a sort of error which is difficult to protect yourself against.
>> You could have a trigger on the table that compare @@rowcount with the
>> number of rows in the table, and if they are equal, you raise an error
>> and rollback. But what if you really want to update all rows in a table?
>> And what if the WHERE clause is there, but is incomplete:
>>
>> UPDATE Orders
>> SET Shipped = 1
>> WHERE OrderDate = @date
>>
>> But someone forgot:
>>
>> AND CustomerID = @custid
>>
>> And counting the rows in big tables in each update is kind of expensive.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>>
>> Books Online for SQL Server SP3 at
>> http://www.microsoft.com/sql/techinf...2000/books.asp[/color]
>
>[/color]