473,324 Members | 2,002 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

binary_checksum and validated software environments

Hi,

It appears that binary_checksum can give the same checksum for
different strings, which is a bit worrying. (I guess the algorithm is
the problem in the context of a repeating pattern.)

e.g.
select binary_checksum('A')
,binary_checksum('AAAAAAAAAAAAAAAAA')
,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA A')

,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAA')

My question...
Is this approach to generating checksums adequate for managing the
object scripts in the SQL Server to ensure that they haven't changed. I
guess that the probability of somebody making a change to a script and
ending up with the same checksum is almost negligible. Has anybody used
this approach in an FDA validated production environment, i.e. 'no ifs,
no buts'? Would it stand up to scrutiny?

Any experiences, thoughts?

Regards

Liam

Jul 23 '05 #1
5 3175
Checksums do not guarantee unique values for different string, it is a
rotating add algorithm, since it is based on XOR ing values and only returns
a very limited set of possible values (the range of int), so you will get
collisions. You will also find that the amount of collisions it is very
depending on the collation sequence used. This is why SQL Server 2005
introduces the HashBytes function.

Ge***@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.

"Liam Caffrey" <li**********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Hi,

It appears that binary_checksum can give the same checksum for
different strings, which is a bit worrying. (I guess the algorithm is
the problem in the context of a repeating pattern.)

e.g.
select binary_checksum('A')
,binary_checksum('AAAAAAAAAAAAAAAAA')
,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA A')

,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAA')

My question...
Is this approach to generating checksums adequate for managing the
object scripts in the SQL Server to ensure that they haven't changed. I
guess that the probability of somebody making a change to a script and
ending up with the same checksum is almost negligible. Has anybody used
this approach in an FDA validated production environment, i.e. 'no ifs,
no buts'? Would it stand up to scrutiny?

Any experiences, thoughts?

Regards

Liam

Jul 23 '05 #2
So an unchanged checksum does not guarantee that the data has not
changed. That seems to be clear.

However, I would guess that the chances of (a) someone making a change
to a script and (b) that change delivering functionality that can be
compiled and be meaningful, is fairly, if not very remote, as to be
irrelevant.

Does anybody use the SQL Server checksum for maintaining audit control
of database objects?

Regards

Liam

Jul 23 '05 #3
Hi Liam,

Yes, I have seen the checksum used for audting database changes on one
of my previous projects. It was used as a 'belt and braces'
verification mechanism to ensure that the database change management
solution (DB Ghost www.dbghost.com) worked as expected. i.e. it
upgraded a target database to be the same as a source database that was
built from scripts in a source control system.

I'm happy to report (because I now work for Innovartis, the makers of
DB Ghost) that a) the checksum function itself never gave any false
positives during the entire time (2 years) that I worked there and b)
DB Ghost never failed either.

The only false positives produced were due to the way SQL Server
stores/reproduces the text for objects such as Stored Procedures/views
etc. in syscomments Scripting the same object on two databases gives
annoying whitespace differences that are suprisingly difficult to
remove. However, that is not to say this is unsurmountable, it was
just deemed as a waste of time to investigate given that it only
occured once in a blue moon.

As you seem to understand the need for ultimate auditability and
traceability for changes to SQL Server then it would be worth your
while looking at DB Ghost. It's the final piece that makes full
lifecycle configuration management possible for SQL Server code changes
i.e. it will allow you to trace a change to the production schema all
the way back through development to the actual business case that
caused it. This assumes that you already have in place a configuration
management system of some kind.

Regs,

Malc

Jul 23 '05 #4
> I would guess that the chances of (a) someone making a change
to a script and (b) that change delivering functionality that can be
compiled and be meaningful, is fairly, if not very remote, as to be
irrelevant.


You would be wrong. It's trivially easy to find checksum collisions.

SELECT BINARY_CHECKSUM('ABA'), BINARY_CHECKSUM('ACQ')

Result:

----------- -----------
17761 17761

(1 row(s) affected)

As a further test I ran the following query on the syscomments table of
a database containing about 200 procs, views, etc. and got 3 rows
returned:

SELECT DISTINCT OBJECT_NAME(T1.id), BINARY_CHECKSUM(T1.text)
FROM syscomments AS T1
JOIN syscomments AS T2
ON BINARY_CHECKSUM(T1.text) = BINARY_CHECKSUM(T2.text)
AND T1.text <> T2.text

This result is perfectly natural and expected when you consider that
there are "only" 2^32 possible checksums - many orders of magnitude
fewer than the number of syntactically valid and useful pieces of
script.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #5
Liam Caffrey (li**********@gmail.com) writes:
So an unchanged checksum does not guarantee that the data has not
changed. That seems to be clear.

However, I would guess that the chances of (a) someone making a change
to a script and (b) that change delivering functionality that can be
compiled and be meaningful, is fairly, if not very remote, as to be
irrelevant.

Does anybody use the SQL Server checksum for maintaining audit control
of database objects?


When I was building a set of tables for our load tool, one thing I
looking for, was a way to track if someone had loaded an object outside
the load tool. That is, the load tool would track all loads in a table,
but would of course not track if someone tried to load an object through
Query Analyzer or somesuch.

I was indeed considering using
CHECKSUM_AGG(BINARY_CHECKSUM(syscomments.text)). The reason I eventually
didn't was that I wanted to support all three of SQL 6.5 and SQL 7 and
SQL 2000, and the troublesome here was SQL 7, which does not have
the checksum functions, but does have ALTER PROCEDURE which does not leave
a trace in sysobjects.

So eventually, I found no other way to rely on the columns crdate and
schema_ver in sysobjects. I found that for each ALTER PROCEDURE,
schema_ver is incremented with 16. This is not very documented, though.
Since I had to go with this on SQL 7, I figured I could just as well
use it on SQL 2000 as well.

On SQL 2005 it's easier. There is now a sys.objects.modified_date,
horray!

--
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 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: ACM SIGSOFT 2004 Publicity Chair | last post by:
CALL FOR PAPERS AND WORKSHOPS ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ACM SIGSOFT 2004 12th International Symposium on the Foundations of Software...
7
by: msnews.microsoft.com | last post by:
Hello everyone! I would like to know about who "can" and "should" be included in the Software Development Teams for small and startup companies. Consider this scenario: Three persons wish to...
18
by: mountain man | last post by:
Greetings to all database professionals and laymen, Let us make a bold assumption that we have developed a software tool for the SQL Server environment which simply acts as an interface between...
0
by: Dana Morris | last post by:
Call for Participation OMG's First Annual Software-Based Communications (SBC) Workshop: From Mobile to Agile Communications http://www.omg.org/news/meetings/SBC2004/call.htm September 13-16,...
2
by: Orly Junior | last post by:
Gentlemen, I am using the following query to get a list of grouped checksum data. SELECT CAST(Field0_datetime AS INT), CHECKSUM_AGG(BINARY_CHECKSUM(Field1_bigint, Field2_datetime,...
3
by: Orly Junior | last post by:
Hello, Do you know if the algorithm for the BINARY_CHECKSUM function in documented somewhere? I would like to use it to avoid returning some string fields from the server. By returning only...
3
by: mallik | last post by:
Seems like we have a strange problem. Development environment is win 2000 prof, IIS5.0, QA environment is Windows 2003 server, IIS 6.0. Everything works as expected on Dev. machine, but when...
0
by: YellowFin | last post by:
Yellowfin International today announced that Yellowfin has joined IBM's Partner Program, and that the Yellowfin BI Suite are now validated on IBM's popular DB2 9 database. This partnership opens...
5
by: =?Utf-8?B?QmVuIFIu?= | last post by:
Hi, In a .NET 2.0 winforms application, I've got a textbox that, when updated, uses the validated event to cascade the change to another textbox (along with another value). This works well if...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.