Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    30

    Arrow Unanswered: Encryption Problem

    I am Using SQl 2000. I use Vb as front end. I am using a simple encryption logic to encrypt password and save it in the Table. Both the encrypt and decrypt functions i have written in VB. The password is saved in the encrypt form in the table. The problem is when i decrypt the password it decrypts wrongly in some cases. In most of the cases if the password is alpha type , the decryption is right but if i use numeric passwords, while decrypting it gives different values. The ascii values are same. Is it because of SQL collation type in the Table Latin1_General_BIN

    The code used for encrypting and decrypting is as given below

    -----------------
    ' FUNCTION : EncryptWord
    ' Purpose : Encrypts the Password depending on the first
    ' character of the Login Name
    ' Input : The Login Name , The Password
    ' Output : The encrypted password
    '--------------------------------------
    Public Function EncryptWord(ByVal argLoginName As String, ByVal argPassword As String) As String
    Dim strEncWord As String
    Dim cntr As Byte
    Dim strLoginName As String
    Dim strPassword As String
    strLoginName = Trim$(argLoginName)
    strPassword = Trim$(argPassword)
    If Len(strPassword) = 0 Then Exit Function
    For cntr = 1 To Len(strPassword)
    strEncWord = strEncWord & Chr(Abs(Asc(Mid(strPassword, cntr, 1)) + Asc(Left(strLoginName, 1)) + cntr))
    Next cntr

    EncryptWord = Trim$(strEncWord)
    End Function

    '------------------------------------------------------------------------
    ' FUNCTION : DecryptWord
    ' Purpose : Decrypts the Password depending on the first
    ' character of the Login Name
    ' Input : The Login Name , The Password
    ' Output : The Decrypted password
    '------------------------------------------------------------------------

    Public Function DecryptWord(ByVal argLoginName As String, ByVal argPassword As String) As String
    On Error Resume Next
    Dim strEncWord As String
    Dim cntr As Byte
    Dim strLoginName As String
    Dim strPassword As String
    strLoginName = Trim$(argLoginName)
    strPassword = Trim$(argPassword)
    If Len(strPassword) = 0 Then Exit Function
    For cntr = 1 To Len(strPassword)
    Debug.Print Abs(Asc(Mid(strPassword, cntr, 1)))
    strEncWord = strEncWord & Chr(Abs(Asc(Mid(strPassword, cntr, 1)) - Asc(Left(strLoginName, 1)) - cntr))

    Next cntr
    DecryptWord = Trim$(strEncWord)
    End Function

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Generally, for passwords, it is unnecessary to ever decrypt the password.

    The usual technique is to store the encrytped password in the database, and then when someone logs in the password they supply is encrypted using the same algorithm and compared to the stored value.

    These are known as one-way encryption schemes, and because they do not ever need to be unencrypted they can be very secure. I have a one-way encryption method configured as a Function if you are interested.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2004
    Posts
    30
    I would appreciate the one way encryption function.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is the function. Store your encrypted passwords as 10 character strings. When someone logs in, use the same function to encrypt the password they supply, and the result should match the value associated with their login.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The performance of this Forum is really starting to suck.

    Here is the function code. For some reason, I can't get the file uploaded.

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Encrypt_Password]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[Encrypt_Password]
    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

    CREATE FUNCTION [dbo].[Encrypt_Password]
    (@RawPassword varchar(20))
    Returns varchar(20)
    as
    BEGIN
    --Function dbo.EncryptPassword
    --Bruce Lindman, 11/19/2002
    --
    --This function returns a 20 character encryption string derived from a supplied password.
    --It uses a non-linear deterministic number generation algorithm known as the Linear Congruential Method
    --to generate pseudo-random numbers from the Ascii values of the password characters, and these
    --random numbers are then converted back into Ascii characters to form the the encrypted string.
    --Because the algorithm is non-linear and uses the password itself as the initial key value, it should be
    --practically impossible to reverse engineer the process.
    --These variables used for testing
    --declare @RawPassword varchar(20)
    --set @RawPassword = 'Pa$$w0rD'
    --set @RawPassword = '!!!!!!!!!!' --A low ascii value password
    --set @RawPassword = '' --A high ascii value password
    declare @counter int --we'll use this to step through the password character by character
    declare @seed decimal(10, 9) --The derived seed value for the random number generator
    declare @EncryptedPassword varchar(20)
    set @EncryptedPassword = ''
    declare @Modulo int --The divisor in the random number generator
    set @Modulo = 100000000
    declare @Multiplier int --The multiplier in the random number generator
    declare @AsciiValue numeric
    --Extend the password to 20 characters by repeating it, separated by the character x
    --The x character ensures that password ABC does not return the same value when doubled,
    --as ABCABC, but passwords that are doubled with a padded x character will return the same
    --encrypted value. ABC returns the same value as ABCxABC or ABCxABCxABC.
    while datalength(@RawPassword) < 20
    begin
    set @RawPassword = @RawPassword + 'x' + @RawPassword
    end
    --I think it is unavoidable that for any function F() there exists a pair of values A, B such
    --that F(A) = F(B).
    --Derive the seed value for the random number function from the password itself
    set @counter = 0
    set @seed = 1
    while @counter < datalength(@RawPassword)
    begin
    set @counter = @counter + 1
    --Use the ascii value of each character to revise the seed value
    set @AsciiValue = ascii(substring(@RawPassword, @Counter, 1))
    set @seed = @seed * (@AsciiValue/1000)
    --We don't want any leading zeros in our decimal value, or the seed may get too small
    while @seed < 0.1 set @seed = @seed * 10
    end
    --We'll derive the multiplier from the seed value, following the principle that a good multiplier
    --should be 1 digit less than the Modulo, and should follow the pattern ....x21 where x is an even number
    set @Multiplier = round(@seed * @Modulo/100, 0) * 200 + 21
    --Now encrypt the password
    set @counter = 0
    while @counter < datalength(@RawPassword)
    begin
    set @counter = @counter + 1
    set @AsciiValue = ascii(substring(@RawPassword, @Counter, 1))
    --This next statement is the guts of the random number generator
    --It creates a new seed value between 0 and 1
    set @seed = cast(cast(1 + (@seed + @AsciiValue/1000) * @Multiplier * @Modulo as bigint) % @Modulo as numeric)/@Modulo
    --Now use the first three digits of the seed value to lookup an ascii character between 1 and 255 and append it to the encrypted password
    set @EncryptedPassword = @EncryptedPassword + char(1 + cast(round(@seed * 1000, 0) as int) % 254)
    end
    Return @EncryptedPassword
    end
    Last edited by blindman; 11-12-04 at 10:30.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, lets try it as a text file. (Why a database forum won't accept files with an sql extension, I have no idea.)
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Nov 2004
    Posts
    30
    Thanks for the Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •