Thursday, November 18, 2010

Compare string with Aspnet_Membership password

Small time ago I had a particular problem, I need to compare a password written by a user in a client WinForms software with an managed password (stored in Aspnet_Membership table).

The main problem is that the password is encrypted in db and it's not possible to decrypt. The only way to solve the problem is to encrypt given password in the same way as and then compare password hashes. To do it, "Salt" is needed: the value of PasswordSalt column in Aspnet_Membership.

Here the code (I'm sorry but i haven't enought time to colorize the code :D ):


Public Function Compare(ByVal password As String, ByVal aspnetSalt As String, ByVal aspnetPassword As String) As Boolean
    Dim isEqual As Boolean = False
    If aspnetPassword = GenerateHash(password, aspnetSalt) Then
        isEqual = True
    End If
    Return isEqual
End Function

Private Function GenerateHash(ByVal pwd As String, ByVal salt As String) As String
    Dim p1 As Byte() = Convert.FromBase64String(salt)
    Return GenerateHash(pwd, p1)
End Function

Private Function GenerateHash(ByVal pwd As String, ByVal saltAsByteArray As Byte()) As String
    Dim sha As New System.Security.Cryptography.SHA1CryptoServiceProvider()
    Dim p1 As Byte() = saltAsByteArray
    Dim p2 As Byte() = System.Text.Encoding.Unicode.GetBytes(pwd)
    Dim data() As Byte = New Byte(((p1.Length + p2.Length)) - 1) {}
    p1.CopyTo(data, 0)
    p2.CopyTo(data, p1.Length)
    Dim result As Byte() = sha.ComputeHash(data)
    Dim rtn As String = Convert.ToBase64String(result)
    Return rtn
End Function


public boolean Compare (string password, string aspnetSalt, string aspnetPassword)
    boolean isEqual = False
    If (aspnetPassword = GenerateHash(password, aspnetSalt))
        isEqual = True
    return isEqual

private string GenerateHash(string pwd, string saltAsBase64)
    byte[] p1 = Convert.FromBase64String(saltAsBase64);
    return GenerateHash(pwd, p1);

private string GenerateHash(string pwd, byte[] saltAsByteArray)
    System.Security.Cryptography.SHA1 sha = new System.Security.Cryptography.SHA1CryptoServiceProvider();

    byte[] p1 = saltAsByteArray;
    byte[] p2 = System.Text.Encoding.Unicode.GetBytes(pwd);

    byte[] data = new byte[p1.Length + p2.Length];

    p1.CopyTo(data, 0);
    p2.CopyTo(data, p1.Length);

    byte[] result = sha.ComputeHash(data);

    string res = Convert.ToBase64String(result);
    return res;

Friday, November 12, 2010

Capitalize string in Sql Server

There is a very simple way to capitalize a string in Sql Server (T-SQL)

If your need is just in output, use this:

SELECT UPPER(LEFT(ColumnName,1)) + LOWER(SUBSTRING(ColumnName,2,LEN(ColumnName))) FROM TableName

If instead you need to modify and save values into db:

UPDATE ColumnName SET ColumnName=UPPER(LEFT(ColumnName,1)) + LOWER(SUBSTRING(ColumnName,2,LEN(ColumnName)))