Encrypt password in database

I currently have a MS SQL 2008 Database with passwords. I need to switch the encryption.
I am going to run a script that will decrypt all the passwords in the database.

After I do this I need to find a solution that will help me encrypt them. And I also need to be able to encrypt/decrypt from the ASP page when I insert a password. Right now I have a function that I call like this:


I insert the password and that's it. When users login it is decrypted and compared to the password the user enters. Y decrypt it in a similar way.

I am using classic ASP. What are my options ?  which are my options ?  I am looking for high quality password encryption but simple to implement.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Why don't you store hashes? If you are able to encrypt there will be someone who is able to decrypt it (read: hack). When you hash the password no-one including yourself will be able to unhash (see, there is even no such word exists) it.

To help you hash the passwords MS provides you with a simple transact SQL function: HASHBYTES that supports all modern  hashing algorithms:
Returns the MD2, MD4, MD5, SHA, SHA1, or SHA2 hash of its input in SQL Server.
If you insist on encryption/decryption then use ENCRYPTBYPASSPHRASE . It is also a T-SQL function and also very easy to use
AleksAuthor Commented:
If I hash it how can I then compare it to the user's input when they login ?
Using the same function! Let me show you some examples.
1. Creating a new user
When a new user is created they provide username and password. You store it as:
INSERT INTO users (username, pwd_hash) values('peter',  HASHBYTES('SHA1', 'my_strong_password'))

Open in new window

The value for the pwd_hash will be stored as (I have checked):

Open in new window

2. Validating username/password
When they login you validate it like this:
SELECT username from users
WHERE username = @username and pwd_hash = HASHBYTES('SHA1', @password)

Open in new window

If they used the same username and password (i.e. 'peter' and 'my_strong_password') then you will get the record back. Otherwise just tell them:
Invalid username and/or password.
If they forgot the password tell them that you do not it either. Ask them to reset the password to a new one
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

AleksAuthor Commented:
I understand the logic. I don't understand the Hash.  Do I just enter SHA1 ?  like the example above ?  that's it ?   SHA1 is the best option ?
Does it not use a 'key' or something ?
Use SHA-2. According to wikipedia SHA-1 has a "theoretical break". SHA-2 has no known successful attacks
AleksAuthor Commented:
Thanks. I also foud a tutorial on Lynda.com


What do you think about that ?  Uses an SQL function "Encrypthypahsphrase"

Problem with this is that people can see the passphrase in the ASP when inserting / updating or reading the password  :$ ... defeats the purpose I think.

This might be a better way to do it ?

As I mentioned before, it only makes sense you you need to decrypt the data back. It makes sense when you store the Credit Card details for example. You may need to refer to this information again when the customer makes a repeating order and uses the "Use stored payment method". However, please note that when your database is compromised anyone will be able to decrypt it back. The algorithm used here is a Triple des, it is strong, but can be decrypted using a brute-force attack. If you are going to use it then use a strong keypass.
AleksAuthor Commented:
Is there a tutorial on how to do this with SHA-2 ?

I need to learn how to insert encrypted. How to read and decrypt. And how to encrypt existing unencrypted data.
if you want SHA-2 use:
HASHBYTES('SHA2', @password)

Open in new window

AleksAuthor Commented:
I don't understand something. If someone got a hold of the DB they could simply decrypt it by using the above since there is no 'key'  ... am I correct ?
You do not understand the two different terms. There is a hashing and there is encryption. Please look up these two terms.

And to answer your question: No. There is no direct way to convert the hash back to the password. There is only a brute-force way to use a dictionary.

As for the encryption: the encryption relies on the key. If you are hacked there is a good chance that the key will be hacked as well. Then it is a matter of seconds to decrypt all of your data.
AleksAuthor Commented:
I'll look it up tonight.
Big MontyWeb Ninja at largeCommented:
I use SHA256 hashing for classic asp, which is what I'm assuming this is for. Put the following code in its own file so that you can include it wherever you need it:

' See the VB6 project that accompanies this sample for full code comments on how
' it works.
' ASP VBScript code for generating a SHA256 'digest' or 'signature' of a string. The
' MD5 algorithm is one of the industry standard methods for generating digital
' signatures. It is generically known as a digest, digital signature, one-way
' encryption, hash or checksum algorithm. A common use for SHA256 is for password
' encryption as it is one-way in nature, that does not mean that your passwords
' are not free from a dictionary attack. 
' If you are using the routine for passwords, you can make it a little more secure
' by concatenating some known random characters to the password before you generate
' the signature and on subsequent tests, so even if a hacker knows you are using
' SHA-256 for your passwords, the random characters will make it harder to dictionary
' attack.
' NOTE: Due to the way in which the string is processed the routine assumes a
' single byte character set. VB passes unicode (2-byte) character strings, the
' ConvertToWordArray function uses on the first byte for each character. This
' has been done this way for ease of use, to make the routine truely portable
' you could accept a byte array instead, it would then be up to the calling
' routine to make sure that the byte array is generated from their string in
' a manner consistent with the string type.
' This is 'free' software with the following restrictions:
' You may not redistribute this code as a 'sample' or 'demo'. However, you are free
' to use the source code in your own code, but you may not claim that you created
' the sample code. It is expressly forbidden to sell or profit from this source code
' other than by the knowledge gained or the enhanced value added by your own code.
' Use of this software is also done so at your own risk. The code is supplied as
' is without warranty or guarantee of any kind.
' Should you wish to commission some derivative work based on this code provided
' here, or any consultancy work, please do not hesitate to contact us.
' Web Site:  http://www.frez.co.uk
' E-mail:    sales@frez.co.uk

Private m_lOnBits(30)
Private m_l2Power(30)
Private K(63)

Private Const BITS_TO_A_BYTE = 8
Private Const BYTES_TO_A_WORD = 4
Private Const BITS_TO_A_WORD = 32

m_lOnBits(0) = CLng(1)
m_lOnBits(1) = CLng(3)
m_lOnBits(2) = CLng(7)
m_lOnBits(3) = CLng(15)
m_lOnBits(4) = CLng(31)
m_lOnBits(5) = CLng(63)
m_lOnBits(6) = CLng(127)
m_lOnBits(7) = CLng(255)
m_lOnBits(8) = CLng(511)
m_lOnBits(9) = CLng(1023)
m_lOnBits(10) = CLng(2047)
m_lOnBits(11) = CLng(4095)
m_lOnBits(12) = CLng(8191)
m_lOnBits(13) = CLng(16383)
m_lOnBits(14) = CLng(32767)
m_lOnBits(15) = CLng(65535)
m_lOnBits(16) = CLng(131071)
m_lOnBits(17) = CLng(262143)
m_lOnBits(18) = CLng(524287)
m_lOnBits(19) = CLng(1048575)
m_lOnBits(20) = CLng(2097151)
m_lOnBits(21) = CLng(4194303)
m_lOnBits(22) = CLng(8388607)
m_lOnBits(23) = CLng(16777215)
m_lOnBits(24) = CLng(33554431)
m_lOnBits(25) = CLng(67108863)
m_lOnBits(26) = CLng(134217727)
m_lOnBits(27) = CLng(268435455)
m_lOnBits(28) = CLng(536870911)
m_lOnBits(29) = CLng(1073741823)
m_lOnBits(30) = CLng(2147483647)

m_l2Power(0) = CLng(1)
m_l2Power(1) = CLng(2)
m_l2Power(2) = CLng(4)
m_l2Power(3) = CLng(8)
m_l2Power(4) = CLng(16)
m_l2Power(5) = CLng(32)
m_l2Power(6) = CLng(64)
m_l2Power(7) = CLng(128)
m_l2Power(8) = CLng(256)
m_l2Power(9) = CLng(512)
m_l2Power(10) = CLng(1024)
m_l2Power(11) = CLng(2048)
m_l2Power(12) = CLng(4096)
m_l2Power(13) = CLng(8192)
m_l2Power(14) = CLng(16384)
m_l2Power(15) = CLng(32768)
m_l2Power(16) = CLng(65536)
m_l2Power(17) = CLng(131072)
m_l2Power(18) = CLng(262144)
m_l2Power(19) = CLng(524288)
m_l2Power(20) = CLng(1048576)
m_l2Power(21) = CLng(2097152)
m_l2Power(22) = CLng(4194304)
m_l2Power(23) = CLng(8388608)
m_l2Power(24) = CLng(16777216)
m_l2Power(25) = CLng(33554432)
m_l2Power(26) = CLng(67108864)
m_l2Power(27) = CLng(134217728)
m_l2Power(28) = CLng(268435456)
m_l2Power(29) = CLng(536870912)
m_l2Power(30) = CLng(1073741824)
K(0) = &H428A2F98
K(1) = &H71374491
K(2) = &HB5C0FBCF
K(3) = &HE9B5DBA5
K(4) = &H3956C25B
K(5) = &H59F111F1
K(6) = &H923F82A4
K(7) = &HAB1C5ED5
K(8) = &HD807AA98
K(9) = &H12835B01
K(10) = &H243185BE
K(11) = &H550C7DC3
K(12) = &H72BE5D74
K(13) = &H80DEB1FE
K(14) = &H9BDC06A7
K(15) = &HC19BF174
K(16) = &HE49B69C1
K(17) = &HEFBE4786
K(18) = &HFC19DC6
K(19) = &H240CA1CC
K(20) = &H2DE92C6F
K(21) = &H4A7484AA
K(22) = &H5CB0A9DC
K(23) = &H76F988DA
K(24) = &H983E5152
K(25) = &HA831C66D
K(26) = &HB00327C8
K(27) = &HBF597FC7
K(28) = &HC6E00BF3
K(29) = &HD5A79147
K(30) = &H6CA6351
K(31) = &H14292967
K(32) = &H27B70A85
K(33) = &H2E1B2138
K(34) = &H4D2C6DFC
K(35) = &H53380D13
K(36) = &H650A7354
K(37) = &H766A0ABB
K(38) = &H81C2C92E
K(39) = &H92722C85
K(40) = &HA2BFE8A1
K(41) = &HA81A664B
K(42) = &HC24B8B70
K(43) = &HC76C51A3
K(44) = &HD192E819
K(45) = &HD6990624
K(46) = &HF40E3585
K(47) = &H106AA070
K(48) = &H19A4C116
K(49) = &H1E376C08
K(50) = &H2748774C
K(51) = &H34B0BCB5
K(52) = &H391C0CB3
K(53) = &H4ED8AA4A
K(54) = &H5B9CCA4F
K(55) = &H682E6FF3
K(56) = &H748F82EE
K(57) = &H78A5636F
K(58) = &H84C87814
K(59) = &H8CC70208
K(60) = &H90BEFFFA
K(61) = &HA4506CEB
K(62) = &HBEF9A3F7
K(63) = &HC67178F2

Private Function LShift(lValue, iShiftBits)
    If iShiftBits = 0 Then
        LShift = lValue
        Exit Function
    ElseIf iShiftBits = 31 Then
        If lValue And 1 Then
            LShift = &H80000000
            LShift = 0
        End If
        Exit Function
    ElseIf iShiftBits < 0 Or iShiftBits > 31 Then
        Err.Raise 6
    End If
    If (lValue And m_l2Power(31 - iShiftBits)) Then
        LShift = ((lValue And m_lOnBits(31 - (iShiftBits + 1))) * m_l2Power(iShiftBits)) Or &H80000000
        LShift = ((lValue And m_lOnBits(31 - iShiftBits)) * m_l2Power(iShiftBits))
    End If
End Function

Private Function RShift(lValue, iShiftBits)
    If iShiftBits = 0 Then
        RShift = lValue
        Exit Function
    ElseIf iShiftBits = 31 Then
        If lValue And &H80000000 Then
            RShift = 1
            RShift = 0
        End If
        Exit Function
    ElseIf iShiftBits < 0 Or iShiftBits > 31 Then
        Err.Raise 6
    End If
    RShift = (lValue And &H7FFFFFFE) \ m_l2Power(iShiftBits)
    If (lValue And &H80000000) Then
        RShift = (RShift Or (&H40000000 \ m_l2Power(iShiftBits - 1)))
    End If
End Function

Private Function AddUnsigned(lX, lY)
    Dim lX4
    Dim lY4
    Dim lX8
    Dim lY8
    Dim lResult
    lX8 = lX And &H80000000
    lY8 = lY And &H80000000
    lX4 = lX And &H40000000
    lY4 = lY And &H40000000
    lResult = (lX And &H3FFFFFFF) + (lY And &H3FFFFFFF)
    If lX4 And lY4 Then
        lResult = lResult Xor &H80000000 Xor lX8 Xor lY8
    ElseIf lX4 Or lY4 Then
        If lResult And &H40000000 Then
            lResult = lResult Xor &HC0000000 Xor lX8 Xor lY8
            lResult = lResult Xor &H40000000 Xor lX8 Xor lY8
        End If
        lResult = lResult Xor lX8 Xor lY8
    End If
    AddUnsigned = lResult
End Function

Private Function Ch(x, y, z)
    Ch = ((x And y) Xor ((Not x) And z))
End Function

Private Function Maj(x, y, z)
    Maj = ((x And y) Xor (x And z) Xor (y And z))
End Function

Private Function S(x, n)
    S = (RShift(x, (n And m_lOnBits(4))) Or LShift(x, (32 - (n And m_lOnBits(4)))))
End Function

Private Function R(x, n)
    R = RShift(x, CInt(n And m_lOnBits(4)))
End Function

Private Function Sigma0(x)
    Sigma0 = (S(x, 2) Xor S(x, 13) Xor S(x, 22))
End Function

Private Function Sigma1(x)
    Sigma1 = (S(x, 6) Xor S(x, 11) Xor S(x, 25))
End Function

Private Function Gamma0(x)
    Gamma0 = (S(x, 7) Xor S(x, 18) Xor R(x, 3))
End Function

Private Function Gamma1(x)
    Gamma1 = (S(x, 17) Xor S(x, 19) Xor R(x, 10))
End Function

Private Function ConvertToWordArray(sMessage)
    Dim lMessageLength
    Dim lNumberOfWords
    Dim lWordArray()
    Dim lBytePosition
    Dim lByteCount
    Dim lWordCount
    Dim lByte
    Const MODULUS_BITS = 512
    Const CONGRUENT_BITS = 448
    lMessageLength = Len(sMessage)
    ReDim lWordArray(lNumberOfWords - 1)
    lBytePosition = 0
    lByteCount = 0
    Do Until lByteCount >= lMessageLength
        lWordCount = lByteCount \ BYTES_TO_A_WORD
        lBytePosition = (3 - (lByteCount Mod BYTES_TO_A_WORD)) * BITS_TO_A_BYTE
        lByte = AscB(Mid(sMessage, lByteCount + 1, 1))
        lWordArray(lWordCount) = lWordArray(lWordCount) Or LShift(lByte, lBytePosition)
        lByteCount = lByteCount + 1

    lWordCount = lByteCount \ BYTES_TO_A_WORD
    lBytePosition = (3 - (lByteCount Mod BYTES_TO_A_WORD)) * BITS_TO_A_BYTE

    lWordArray(lWordCount) = lWordArray(lWordCount) Or LShift(&H80, lBytePosition)

    lWordArray(lNumberOfWords - 1) = LShift(lMessageLength, 3)
    lWordArray(lNumberOfWords - 2) = RShift(lMessageLength, 29)
    ConvertToWordArray = lWordArray
End Function

Public Function SHA256(sMessage)
    Dim HASH(7)
    Dim M
    Dim W(63)
    Dim a
    Dim b
    Dim c
    Dim d
    Dim e
    Dim f
    Dim g
    Dim h
    Dim i
    Dim j
    Dim T1
    Dim T2
    HASH(0) = &H6A09E667
    HASH(1) = &HBB67AE85
    HASH(2) = &H3C6EF372
    HASH(3) = &HA54FF53A
    HASH(4) = &H510E527F
    HASH(5) = &H9B05688C
    HASH(6) = &H1F83D9AB
    HASH(7) = &H5BE0CD19
    M = ConvertToWordArray(sMessage)
    For i = 0 To UBound(M) Step 16
        a = HASH(0)
        b = HASH(1)
        c = HASH(2)
        d = HASH(3)
        e = HASH(4)
        f = HASH(5)
        g = HASH(6)
        h = HASH(7)
        For j = 0 To 63
            If j < 16 Then
                W(j) = M(j + i)
                W(j) = AddUnsigned(AddUnsigned(AddUnsigned(Gamma1(W(j - 2)), W(j - 7)), Gamma0(W(j - 15))), W(j - 16))
            End If
            T1 = AddUnsigned(AddUnsigned(AddUnsigned(AddUnsigned(h, Sigma1(e)), Ch(e, f, g)), K(j)), W(j))
            T2 = AddUnsigned(Sigma0(a), Maj(a, b, c))
            h = g
            g = f
            f = e
            e = AddUnsigned(d, T1)
            d = c
            c = b
            b = a
            a = AddUnsigned(T1, T2)
        HASH(0) = AddUnsigned(a, HASH(0))
        HASH(1) = AddUnsigned(b, HASH(1))
        HASH(2) = AddUnsigned(c, HASH(2))
        HASH(3) = AddUnsigned(d, HASH(3))
        HASH(4) = AddUnsigned(e, HASH(4))
        HASH(5) = AddUnsigned(f, HASH(5))
        HASH(6) = AddUnsigned(g, HASH(6))
        HASH(7) = AddUnsigned(h, HASH(7))
    SHA256 = LCase(Right("00000000" & Hex(HASH(0)), 8) & Right("00000000" & Hex(HASH(1)), 8) & Right("00000000" & Hex(HASH(2)), 8) & Right("00000000" & Hex(HASH(3)), 8) & Right("00000000" & Hex(HASH(4)), 8) & Right("00000000" & Hex(HASH(5)), 8) & Right("00000000" & Hex(HASH(6)), 8) & Right("00000000" & Hex(HASH(7)), 8))
End Function

Open in new window

next, on your login processing page, you can do something like the following (this is an example lifted straight from my code:

    set rs = Server.CreateObject("ADODB.RecordSet")
    set conn = Server.CreateObject("ADODB.Connection")
    set cmd = Server.CreateObject("ADODB.Command")
    conn.Open connectionString 

   dim password
   password = SHA256( Request.Form("password") & hashKey )    '<-- hashKey is a variable that contains your salt, aka a bunch of random chars

    sql = "select * from tblUsers where email = ? and password = ?"
    with cmd
        .ActiveConnection = conn
        .CommandText = sql
        .Parameters.Append  .CreateParameter( "email", adVarChar, adParamInput, "50", Request.Form("email") )
        .Parameters.Append  .CreateParameter( "password", adVarChar, adParamInput, "500", password )
    end with
    rs.Open cmd

    if rs.BOF and rs.EOF then    
        '-- invalid login
        '-- successful login
    end if

Open in new window

remember, you'll need to set an INCLUDE on the SHA256 function. Also, if you don't use adovbs.inc, you'll need to specify the numerical equivalents in the command object.

Since this is all being done server side, the only way someone could get your hash key is if they had direct access to your server.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
how about encrypting existing passwords that are already there in the DB ?
Big MontyWeb Ninja at largeCommented:
you could write a script that selects all of your users into a recordset, runs the SHA256 function on the password field, then save it back to the database. since the data is coming directly from your database, no need for a command object (although there's nothing wrong with using one), you can just stick using a recordset.

something like:

sql = "select email, password from USERS"
set rs = conn.Execute( sql )

dim email
email = Request("email")
dim password
password = SHA256( Request("password") & hashKey )

if not rs.BOF and not rs.EOF then
    Response.Write "no users found"
    do while not rs.EOF
          sql = "update USERS set password = '" & password & "' where email = '" & email & "'"
          conn.Execute( sql )
end if

this assumes your USERS table only contains unique email addresses. If not, use whatever unique field you have in place of the email field
AleksAuthor Commented:
I will read this in more detail over the weekend, its sort of foreign to me.
Big MontyWeb Ninja at largeCommented:
saw an error in the code, try this instead. I also provided comments to describe what's going on:

dim email, password, sql, rs, hashKey        '-- declaring variables for use

hasKey = "psUoiibY&6b"    '-- to be used in the encrypting of the password, this can be any value

'-- these 2 lines will grab all of the users from your database so you can hash their password
sql = "select email, password from USERS"
set rs = conn.Execute( sql )

if not rs.BOF and not rs.EOF then     '-- no records were found for whatever reason
    Response.Write "no users found"
    do while not rs.EOF      '-- loop through all of the records
          '-- grab the email address and password from the database one record at a time
          email = rs("email")
          password = rs("password")    '--right now, this is not encrypted

           '-- encrypt password
           password = SHA256( password & hashKey )

          '-- now put the encrypted password back into the database
          sql = "update USERS set password = '" & password & "' where email = '" & email & "'"
          conn.Execute( sql )
end if

Open in new window

if you have users with duplicate email addresses in your table, this won't work, you'll need to switch over to your primary key value, for example userID or recordID, whatever you have in your table structure
AleksAuthor Commented:
I will apply this later next week
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.