用Java实现SQL CHECKSUM [英] Implementing SQL CHECKSUM in Java

问题描述

我在SQL Server 2008中拥有一个现有数据库，该数据库通过存储过程为现有PHP Web应用程序执行用户身份验证。 Web应用程序向存储过程发送一个字符串，但是存储过程将存储该字符串，并使用SQL Checksum（ http://msdn.microsoft.com/en-us/library/ms189788.aspx ）。存储过程将字符串强制转换为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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！