问题描述
我在SQL Server 2008中拥有一个现有数据库,该数据库通过存储过程为现有PHP Web应用程序执行用户身份验证。 Web应用程序向存储过程发送一个字符串,但是存储过程将存储该字符串,并使用SQL Checksum()。存储过程将字符串强制转换为NVARCHAR(50),并将CHECKSUM作为int存储在用户表中。
I have an existing database in SQL Server 2008 that performs user authentication via stored procedure for an existing PHP web application. The web application sends the stored procedure a string, however the stored procedure stores, and checks the value with SQL Checksum (http://msdn.microsoft.com/en-us/library/ms189788.aspx). The Stored Procedure casts the string as NVARCHAR(50), and stores the CHECKSUM as int in the user table.
我现在在的顶部编写一个新的Java应用程序。现有的数据库,我正在编写一个自定义的spring身份验证管理器。我想用Java重新实现CHECKSUM算法,因此不需要调用存储过程来执行转换,但是找不到关于SQL CHECKSUM如何工作的任何文档。
I am now writing a new Java application on top of the existing database, and I'm writing a custom spring authentication manager. I would like to re-implement the CHECKSUM algorithm in Java so I do not need to call a stored procedure to perform the conversion, however I can not find any documentation on how SQL CHECKSUM works.
我尝试了以下代码,猜测它是CRC32,但是它未能返回与SQL CHECKSUM相同的值:
I tried the following code, with the guess that it was CRC32, however it fails to return the same value as SQL CHECKSUM:
String pass = "foobar";
CRC32 crc32 = new CRC32();
crc32.update(pass.getBytes("UTF-16")); //This is due to the stored procedure casting as nvarchar
crc32.getValue();
谁能指出我SQL CHECKSUM使用的算法,以便可以在Java中重新实现它?
Can anyone point me to the algorithm that SQL CHECKSUM uses so I can re-implement it in Java?
问题也不是哪种算法可以为安全性提供最佳哈希。在此特定情况下,安全性超出了要求,因为我们不准备强制系统级密码重置。问题是T-SQL CHECKSUM使用哪种算法,以便可以重新实现它。此特定用例用于auth,但是在许多不同的应用程序中都有必要这样做。
The question also isn't which algorithm provides the best hash for security. Security is outside of the requirements in this particular instance, as we are not prepared to force a system wide password reset. The question is what algorithm is used by T-SQL CHECKSUM, so that it could be re-implemented. This particular use case is for auth, however there is potential for this being necessary in many different applications.
推荐答案
在SQL Server论坛上,在此中指出:
On SQL Server Forum, at this page, it's stated:
SQL Server中的内置CHECKUM函数建立在一系列4位左旋转xor操作的基础上。有关更多说明,请参见此。
我能够将BINARY_CHECKSUM移植到c#(对不起,我手头没有Java编译器),而且似乎可以正常工作...我将继续研究CHECKSUM之后...
I was able to port the BINARY_CHECKSUM to c# (sorry I don't have a Java compiler at hand) and it seems to be working... I'll be looking at the plain CHECKSUM later...
private int SQLBinaryChecksum(string text)
{
long sum = 0;
byte overflow;
for (int i = 0; i < text.Length; i++)
{
sum = (long)((16 * sum) ^ Convert.ToUInt32(text[i]));
overflow = (byte)(sum / 4294967296);
sum = sum - overflow * 4294967296;
sum = sum ^ overflow;
}
if (sum > 2147483647)
sum = sum - 4294967296;
else if (sum >= 32768 && sum <= 65535)
sum = sum - 65536;
else if (sum >= 128 && sum <= 255)
sum = sum - 256;
return (int)sum;
}
这篇关于用Java实现SQL CHECKSUM的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!