modification. I'm thinking to store a list with Hash values for row sets.
i.e.
[pseudocode]
byte[] returnHash = MD5Hash( SELECT stuff FROM database WHERE junk )
[/pseudocode]
Is there a way to do an MD5 hash or equivilent without outputting the result
to a file and just hashing the file?
Is there a better way how to achieve the same goal?
Any comments appreciated. Thanks for your time!
-EdgarsEdgars Klepers wrote:
> I'm trying to implement some sort of security checking against database
> modification. I'm thinking to store a list with Hash values for row sets
.
> i.e.
> [pseudocode]
> byte[] returnHash = MD5Hash( SELECT stuff FROM database WHERE junk )
> [/pseudocode]
> Is there a way to do an MD5 hash or equivilent without outputting the resu
lt
> to a file and just hashing the file?
> Is there a better way how to achieve the same goal?
> Any comments appreciated. Thanks for your time!
> -Edgars
In SQL Server 2005 you could use the HashBytes function.
In earlier versions I think you'll have to use the .NET crypto classes
or Microsoft's COM crypto API. That means client side code or a call to
external code from SQL Server. Maybe you could write an extended proc
to do it (would require C++).
SQL Server 2000 has the CHECKSUM / BINARY_CHECKSUM functions but these
are just simple checksums not strong hashes.
David Portas
SQL Server MVP
--|||If you're using SQL Server 2000, there is an extended stored procedure
for MD5 hashing (and it's quick)
http://www.codeproject.com/database/xp_md5.asp|||markc600@.hotmail.com wrote:
> If you're using SQL Server 2000, there is an extended stored procedure
> for MD5 hashing (and it's quick)
> http://www.codeproject.com/database/xp_md5.asp
That's
. Thanks for the link.David Portas
SQL Server MVP
--|||I did come across that. How would one put in an entire row, or more
importantly an entire row set into that function to hash?
"markc600@.hotmail.com" wrote:
> If you're using SQL Server 2000, there is an extended stored procedure
> for MD5 hashing (and it's quick)
> http://www.codeproject.com/database/xp_md5.asp
>|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> In SQL Server 2005 you could use the HashBytes function.
Beware that in SQL 2005 RTM, HashBytes returns a random value if you pass it
a NULL value. SQL Server MVP Steve Kass has filed bug about it, and the
bug has been acknolweged as fixed, although it is unknown what result
hasbytes(NULL) yields after the fix.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Exactly how will depend upon your business requirements.
At the simplest level you can concatenate the relevant columns
select dbo.fn_md5( coalesce(colA,'') + coalesce(colB,'') )
from sometable
However, this may give you unexpected collisions in that if you have
a row with, for example, colA='X' and colB='YZ' and another row with
colA='XY' and colB='Z'. It also doesn't distinguish NULLs from empty
strings.
This may be acceptable to you though.
Also consider folding all character data to upper case and
removing leading/trailing spaces.
Lots of options, you decide.
Regards.
