«
»

We had a need to do a case sensitive SQL comparison this week. As part of a login process a user is prompted for an answer to their security question. Now I really don’t think forcing users to enter their employer’s name in the exact case does anything to keep out hackers, but the client wanted it done this way.

Of course we could pull the values into ColdFusion and do a case sensitive comparison with Compare(). But the code was already setup to do the comparison at the SQL level, which is usually a better idea anyway if it can be done.

There are several ways to tell MS SQL to do a case sensitive comparison. One is to convert the values to binary:

... WHERE convert(varbinary(255), answer) = convert(varbinary(255), '#Form.answer#')

This will not use the index on answer, if there is one. To make sure SQL Server uses any available indexes, add a normal comparison:

... WHERE convert(varbinary(255), answer) = convert(varbinary(255), '#Form.answer#') AND answer = '#Form.answer#'

You could also use the BINARY_CHECKSUM function, it will return different results for different case strings:

... WHERE BINARY_CHECKSUM(answer) = BINARY_CHECKSUM('#Form.answer#')

You’ll still have the issue with the indexes if you use this method.

Another method, the one I chose to use, is to specify the collation for the values being compared. Collation refers to a set of rules that determine how data is sorted and compared. Usually SQL Server is setup to use a case insensitive collation. We can change that for our comparison:

... WHERE answer COLLATE SQL_Latin1_General_CP1_CS_AS = '#Form.answer#' COLLATE SQL_Latin1_General_CP1_CS_AS

If you are going to do a lot of case sensitive comparisons against this field, you may want to go into Enterprise Manager and set the collation for this field from the db default to one that is case sensitive, such as ‘SQL_Latin1_General_CP1_CS_AS’.

Setting the collation in SQL Server

In MySQL, you can use the BINARY keyword to force a case sensitive comparison:

... WHERE answer = BINARY '#Form.answer#'

This entry was posted on 3 July 2007 at 9:53 am and is filed under SQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

5 Comments

  1. Mark Kruger says:

    Ryan,

    that’s an outstanding tip. I love it.

    -Mark

    3 July 2007, 10:24 am

  2. Seb says:

    “WHERE (L.UserID = @UserID) AND (L.[Password] = @Password)”

    I have a stored proceudre that does a password check using WHERE statement (above), so isnt case sensitve. Would prefer not to do a non-case sensitive WHERE statement because of performance.

    Do you have a method where after a the non-case sensitive WHERE statement is used, could then be followed by a case sensitive verification that it indeed matches?

    24 September 2007, 7:34 am

  3. Lango says:

    My suggestion would be to change your where clause to only compare user id and then do your password comparison after. Assuming your are doing this in stored procedure so you are not passing around passwords.

    For example:

    CREATE PROCEDURE [dbo].[AuthenticateUser]
    (@UserName varchar(15),@Password varchar(15))AS
    BEGIN
    SET NOCOUNT ON
    DECLARE @Result int –0: failed
    –1: success
    DECLARE @ActualPassword varchar(12)
    SELECT @ActualPassword = [Password]
    FROM MyUsers
    WHERE Username = LTRIM(RTRIM(@UserName))
    if @@RowCount>0
    BEGIN
    if convert(varbinary(255),@Password) = convert(varbinary(255),@ActualPassword)
    select @Result = 1
    END
    return @Result;
    END

    27 May 2008, 7:36 am

  4. Sanjat says:

    SELECT     U_Name, U_Print_Name, U_Code, U_Pass
    FROM         M_USER
    WHERE     (U_Name = 'v')  AND (CONVERT(varbinary(255), 'v') = CONVERT(varbinary(255), U_Pass))

    14 April 2009, 6:53 am

  5. Sanjat says:

    Check Password in sql
    SELECT     U_Name, U_Print_Name, U_Code, U_Pass
    FROM         M_USER
    WHERE     (U_Name = 'v')  AND (CONVERT(varbinary(255), 'v') = CONVERT(varbinary(255), U_Pass))

    14 April 2009, 6:54 am