By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,190 Members | 1,653 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,190 IT Pros & Developers. It's quick & easy.

SQL Server 2000 - preventing multiple update

P: n/a
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

Jul 20 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"aaj" <aa*@aaj.com> wrote in
news:41**********************@news.easynet.co.uk:
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


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.
Jul 20 '05 #2

P: n/a
aaj (aa*@aaj.com) writes:
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.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

P: n/a
aaj
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" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
aaj (aa*@aaj.com) writes:
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.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #4

P: n/a
aaj (aa*@aaj.com) writes:
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......
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...
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.
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.
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


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #5

P: n/a
Lyle Fairfield <do******@me.com> wrote in message news:<Xn**********************************@216.221 .81.119>...
"aaj" <aa*@aaj.com> wrote in
news:41**********************@news.easynet.co.uk:
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


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.


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.
Jul 20 '05 #6

P: n/a
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" <aa*@aaj.com> wrote in message
news:41**********************@news.easynet.co.uk.. .
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

Jul 20 '05 #7

P: n/a
aaj
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" <aa*@aaj.com> wrote in message
news:41**********************@news.easynet.co.uk.. .
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" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
aaj (aa*@aaj.com) writes:
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.


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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


Jul 20 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.