you're reading...
MySQL, SQL Server

Password Function in SQL Server

Like Mysql, SQL Server have any function for password encrypte. In mysql we can see like MD5,PASSWORD,HASH and any other type of function make text encrypte.

You can see any example and comparing that function :

In a recent SQL Server port of the Actions module for Drupal 5, I ran into the following MySQL query:

SELECT aid FROM {actions} WHERE MD5(aid) = '%s'

In order to get this working in SQL Server 2005, we have to first use the HASHBYTES() function instead:

SELECT aid FROM {actions} WHERE HASHBYTES('MD5',aid) = '%s'

This technically does the trick, BUT, the catch is that SQL Server prepends “0x” onto the result hash. I believe this is because it wants to specify the base of the result (in this case, hexadecimal). In my case, I needed to compare this result with a hash that did not have the 0x prefix. To do so, we need to drop the prefix using SUBSTRING(). Since you can’t manipulate binary data directly, I used an “undocumented” SQL Server function, sys.fn_sqlvarbasetostr(), to convert the hash to a string.

SELECT aid FROM {actions} WHERE SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',$1)),3,32) = '%s'

The 32 in the SUBSTRING function refers to the 32 characters in the MD5 hash.


I need to add some extra info here as the biggest headache with this is data types:

make sure what you are matching is of the exact same data type.

The below from some testing, I was looking for the hashed value in the database.
The first SQL line I’ve got 8 harcoded just to see what the hash result looks like, then I pasted it in the 2nd SQL which I use in my actual application to test the URI password reset call – will obviously concatenate a Salt into this.

SELECT SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5','8')),3,32);
SELECT * FROM acl_auth
WHERE  SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5',cast(acl_auth_id as varchar))),3,32) = 'c9f0f895fb98ab9159f51fd0297e236d'

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.


No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 10 other followers

November 2011
    Dec »


Web Statistic

Blog Stats

  • 154,352 hits
%d bloggers like this: