SQL Server Performance

Regarding UserName and Password check against a table

Discussion in 'Getting Started' started by ravinder1483, Feb 7, 2011.

  1. ravinder1483 New Member

    Hi,I have to write a stored proc which accepts @USERNAME and @PASSWORD as a parameter.I have to implement a logic that if the supplied information is correct then it will call another proc else it will return from there.Can anybody tell me how to implement password check considering case sensitivity.
    ravinder1483, Feb 7, 2011
    #2

  2. FrankKalis Moderator

    Welcome to the forum!
    Surely a good procedure would need more than just checks for case sensitivity, but in its most simplest form you could use something like this:
    DECLARE @t TABLE (UserName varchar(50), pwd varchar(50))

    INSERT INTO @t
    (UserName, pwd)
    VALUES
    ('abc', 'ThisIsAFancyPassworD');

    DECLARE @s varchar(30)
    SELECT @s = 'ThisIsAFancyPassword'

    --Succeeds, while it should fail. No collation
    SELECT
    *
    FROM
    @t T
    WHERE
    T.UserName = 'abc' AND
    T.pwd = @s

    SELECT @s = 'ThisIsAFancyPassword'

    --Fails
    SELECT
    *
    FROM
    @t T
    WHERE
    T.UserName = 'abc' AND
    T.pwd COLLATE Latin1_General_CS_AS = @s

    SELECT @s = 'ThisIsAFancyPassworD'

    --Succeeds
    SELECT
    *
    FROM
    @t T
    WHERE
    T.UserName = 'abc' AND
    T.pwd COLLATE Latin1_General_CS_AS = @s
    Anyway, have a look at collations in the Books Online. You might need another one if you need to support unicode
    FrankKalis, Feb 7, 2011
    #3

Share This Page