0

I usually don't work with MSSQL and wonder how i can match a md5 value of a field value stored as string.

In mysql i would use 

SELECT * FROM table WHERE md5(field) = $md5value

but i can't find a simple solution for this in mssql.

Now i loop all posts and check for the matched value in the loop, but this is way to slow.

How do I solve this in MSSQL?

I have no possibility to add a extra field in the table and store the md5 value, so i have to do the check this way.

Using odbc driver in php.

tobros91
  • 668
  • 1
  • 9
  • 24
  • Does this help? http://stackoverflow.com/questions/3525997/generate-md5-hash-string-with-t-sql – mlinth Feb 02 '15 at 10:38

1 Answers1

1

MSSQL uses no MD5(), but a function called HASHBYTES:

SELECT * FROM table WHERE HASHBYTES('MD5',field) = $md5value

This function appends '0x' to the hash though, so to fully check it, you need:

SELECT * FROM table WHERE HASHBYTES('MD5',field) = '0x' . $md5value

Edit: in PHP, it looks like this:

$query = "SELECT * FROM table WHERE HASHBYTES('MD5',field) = \"0x" . $md5value . '"';
nxu
  • 2,202
  • 1
  • 22
  • 34
  • This gives me Incorrect syntax near '641' when the given md5value is 641d77dd5271fca28764612a028d9c8e – tobros91 Feb 02 '15 at 11:13
  • 1
    @tobros91: what is your SQL string now? I suspect you've just added your MD5 string literal without quotes. – halfer Feb 02 '15 at 11:16
  • If i use quotes i get Synax error, and the whole MD5 value. $sql="SELECT * FROM APPLICATION_DETAIL WHERE HASHBYTES('MD5',APPLICATION_ID) = '0x' . '".$key."'"; – tobros91 Feb 02 '15 at 11:20
  • Umm.. i meant the string concatenation within php... $sql="SELECT * FROM APPLICATION_DETAIL WHERE HASHBYTES('MD5',APPLICATION_ID) = " . '"0x' . $key . '"'; – nxu Feb 02 '15 at 11:23
  • Edited my post (and my comment above, fixed a typo) – nxu Feb 02 '15 at 11:28
  • Thanks for your patience, i tried several combinations with quotes with different errors, latest is "The cursor was not declared" with the latest solution. – tobros91 Feb 02 '15 at 11:39