Case-Sensative Password Column in SQL Anywhere 10

[David Skelly]

This might help: http://sqlanywhere.blogspot.com/2008/02/todays-tip-case-sensitive-searching.html[^] Don't expect it to be that fast.

[PIEBALDconsult]

Passwords should not be case-sensitive, but you're welcome to if you want. You should not be storing clear-text passwords. I recommend hashing the password (I use SHA-1). That should also allow case-sensitivity. Two birds; one stone.

[MWRivera]

That sounds perfect for what I'm looking for. Do you have an example or a link? Thanks

[WoutL]

How do you mean 'passwords should not be case-sensitive'. I think they should be! But your'e right about not storing them as clear text. They should be hashed indeed.

Wout Louwers

[PIEBALDconsult]

Nothing in a user interface should be case-sensitive -- that's just my opinion after using OpenVMS for many years and then being thrown into Unix, Windows, VOS, etc. X| Case-sensitivity is user-hostile; it does not serve the user.

[PIEBALDconsult]

Here are my hashing methods:

public static string
Hash
(
string Subject
)
{
return ( Hash
(
System.Text.Encoding.Unicode.GetBytes ( Subject )
,
new System.Security.Cryptography.SHA1Managed()
) ) ;
}

public static string
Hash
(
string Subject
,
System.Security.Cryptography.HashAlgorithm Provider
)
{
return ( Hash
(
System.Text.Encoding.Unicode.GetBytes ( Subject )
,
Provider
) ) ;
}

public static string
Hash
(
byte[] Subject
)
{
return ( Hash
(
Subject
,
new System.Security.Cryptography.SHA1Managed()
) ) ;
}

public static string
Hash
(
byte[] Subject
,
System.Security.Cryptography.HashAlgorithm Provider
)
{
System.Text.StringBuilder result =
new System.Text.StringBuilder ( Provider.OutputBlockSize ) ;

foreach 
( 
    byte b 
in 
    Provider.ComputeHash ( Subject ) 
)
{
    result.Append ( b.ToString ( "X2" ) ) ;
}

return ( result.ToString() ) ;

}

[WoutL]

I never worked with Open-VMS. I think case-sensitve passwords are better for security.

Wout Louwers

[David Skelly]

PIEBALDconsult wrote:

Passwords should not be case-sensitive

I'm glad you're not in charge of security at my bank. I agree with the suggestion to hash the password in the database. However, be aware that the US Goverment has said that all federal applications should stop using SHA-1 as soon as practical as it is considered too weak and several authors have published attacks which exploit weaknesses in the algorithm.

[MWRivera]

Thanks for the feedback guys. After some discussion it's been asked of me to find out if there is any way of setting the actual column in the database up as a password column in some way. Is there any contraint/switch in sybase that will serve what I'm looking for? Thanks again

[David Skelly]

MWRivera wrote:

setting the actual column in the database up as a password column in some way

I'm not sure what you mean by this. Maybe this article will be of help: http://ianywheresolutions.net/developer/product_manuals/sqlanywhere/1000/en/html/dbdaen10/da-security-s-4649816.html[^] This obviously leaves you with the problem of how to secure the key used to encrypt and decrypt the password; I for one don't much like the idea of hard-coding it into the trigger like this. But it's a start. This is for SQLAnywhere, which I think you said you were using. If it is Sybase ASE there is a more sophisticated method for doing this sort of thing which is explained in the documentation.

[MWRivera]

Thanks for the link David, I'll check it out at the next opertunity I get. Mel

[MWRivera]

Hi David, Thanks for the link above. I was wondering if you or anyone else could help with a problem I've came into with it though. I was able to encrypt the password field using the following trigger when a new record was added to the table: ALTER TRIGGER "encrypt_new_user_pwd" BEFORE INSERT ORDER 1 ON "QAS"."tableName" REFERENCING NEW AS newPwd FOR EACH ROW BEGIN Set newPwd.pwdField = ENCRYPT(newPwd.pwdField , 'key') END The problem is I'm unable to decrypt the password of the newly created record (fieldID = 0002), using the following: SELECT CAST (DECRYPT(pwdField, 'key') AS VARCHAR(100)) FROM "QAS"."tableName" WHERE fieldID = '0002' In Sybase when trying to execute the above statement I get the following error: Interactive SQL The following error occurred while fetching results: Decryption error: Input must be a multiple of 16 bytes in length for AES SQLCODE=-851, ODBC 3 State="08001" Do you have any idea what is going wrong here? The pwdField is of type VARCHAR and size 15. Thanks, Mel