Case Sensitive SQL

Discussion in 'ASP General' started by vunet.us@gmail.com, Aug 17, 2007.

  1. Guest

    So, I came across the problem every developer comes across with once:
    case in SQL. My users login with their selected user name and when SQL
    checks for user name value, case is ignored. For example, user1 is
    equal to User1. This causes some problems in cases where I validate
    user names for authentication to access some secure files. Anyway,
    what can I do to make sure User1 cannot log in if database stores
    user1?
    Suggestions are very appreciated.
    Thanks.
    , Aug 17, 2007
    #1
    1. Advertising

  2. daddywhite Guest

    On 17 Aug, 18:54, wrote:
    > So, I came across the problem every developer comes across with once:
    > case in SQL. My users login with their selected user name and when SQL
    > checks for user name value, case is ignored. For example, user1 is
    > equal to User1. This causes some problems in cases where I validate
    > user names for authentication to access some secure files. Anyway,
    > what can I do to make sure User1 cannot log in if database stores
    > user1?
    > Suggestions are very appreciated.
    > Thanks.


    This should solve all your problems:

    http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html
    daddywhite, Aug 17, 2007
    #2
    1. Advertising

  3. wrote:
    > So, I came across the problem every developer comes across with once:
    > case in SQL. My users login with their selected user name and when SQL
    > checks for user name value, case is ignored. For example, user1 is
    > equal to User1. This causes some problems in cases where I validate
    > user names for authentication to access some secure files. Anyway,
    > what can I do to make sure User1 cannot log in if database stores
    > user1?
    > Suggestions are very appreciated.
    > Thanks.


    Are you talking about SQL Server? What version? Are you aware that you can
    change to a case-sensitive collation? Depending on the version you can do
    this at the column level.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Aug 17, 2007
    #3
  4. Evertjan. Guest

    wrote on 17 aug 2007 in microsoft.public.inetserver.asp.general:

    > So, I came across the problem every developer comes across with once:
    > case in SQL. My users login with their selected user name and when SQL
    > checks for user name value, case is ignored. For example, user1 is
    > equal to User1. This causes some problems in cases where I validate
    > user names for authentication to access some secure files. Anyway,
    > what can I do to make sure User1 cannot log in if database stores
    > user1?


    You don't.

    When searching for the right unique record, you should choose for case
    insensitivity, as the user is not to be trusted with case sensetive
    usernames and the sql WHERE clause is case insensitive [in most engines].

    Subsequent vbs testing of the password with:

    if fields("password") = request.form("password") then

    is case sensitive unless you do

    if ucase(fields("password")) = ucase(request.form("password")) then

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
    Evertjan., Aug 17, 2007
    #4
  5. Guest

    On Aug 17, 2:58 pm, "Evertjan." <> wrote:
    > wrote on 17 aug 2007 in microsoft.public.inetserver.asp.general:
    >
    > > So, I came across the problem every developer comes across with once:
    > > case in SQL. My users login with their selected user name and when SQL
    > > checks for user name value, case is ignored. For example, user1 is
    > > equal to User1. This causes some problems in cases where I validate
    > > user names for authentication to access some secure files. Anyway,
    > > what can I do to make sure User1 cannot log in if database stores
    > > user1?

    >
    > You don't.
    >
    > When searching for the right unique record, you should choose for case
    > insensitivity, as the user is not to be trusted with case sensetive
    > usernames and the sql WHERE clause is case insensitive [in most engines].
    >
    > Subsequent vbs testing of the password with:
    >
    > if fields("password") = request.form("password") then
    >
    > is case sensitive unless you do
    >
    > if ucase(fields("password")) = ucase(request.form("password")) then
    >
    > --
    > Evertjan.
    > The Netherlands.
    > (Please change the x'es to dots in my emailaddress)


    I like this solution: if fields("password") = request.form("password")
    then...
    I use SQL Server but with no full control as it is a hosting package.
    I am afraid I won't be able to set columns to be case-sensitive even
    if this could be a good solution.
    But so far I stick with the solution above (which I thought of before
    but wasn't sure...).
    Thank you.
    , Aug 17, 2007
    #5
  6. Evertjan. Guest

    wrote on 17 aug 2007 in microsoft.public.inetserver.asp.general:

    > On Aug 17, 2:58 pm, "Evertjan." <> wrote:
    >> wrote on 17 aug 2007 in microsoft.public.inetserver.asp.general:
    >>
    >> > So, I came across the problem every developer comes across with
    >> > once: case in SQL. My users login with their selected user name and
    >> > when SQL checks for user name value, case is ignored. For example,
    >> > user1 is equal to User1. This causes some problems in cases where I
    >> > validate user names for authentication to access some secure files.
    >> > Anyway, what can I do to make sure User1 cannot log in if database
    >> > stores user1?

    >>
    >> You don't.
    >>
    >> When searching for the right unique record, you should choose for
    >> case insensitivity, as the user is not to be trusted with case
    >> sensetive usernames and the sql WHERE clause is case insensitive [in
    >> most engines].
    >>
    >> Subsequent vbs testing of the password with:
    >>
    >> if fields("password") = request.form("password") then
    >>
    >> is case sensitive unless you do
    >>
    >> if ucase(fields("password")) = ucase(request.form("password")) then

    >
    > I like this solution: if fields("password") = request.form("password")
    > then...
    > I use SQL Server but with no full control as it is a hosting package.
    > I am afraid I won't be able to set columns to be case-sensitive even
    > if this could be a good solution.
    > But so far I stick with the solution above (which I thought of before
    > but wasn't sure...).
    > Thank you.


    You could also vbs test the same way if the username, found
    caseINsensitively by WHERE, is still a match casesenitively,
    but I would advice against that for the above reasons and because the
    preset uniqueness of the username field in the database would perhaps be
    in question.

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
    Evertjan., Aug 17, 2007
    #6
  7. "daddywhite" <> wrote in message
    news:...
    > On 17 Aug, 18:54, wrote:
    >> So, I came across the problem every developer comes across with once:
    >> case in SQL. My users login with their selected user name and when SQL
    >> checks for user name value, case is ignored. For example, user1 is
    >> equal to User1. This causes some problems in cases where I validate
    >> user names for authentication to access some secure files. Anyway,
    >> what can I do to make sure User1 cannot log in if database stores
    >> user1?
    >> Suggestions are very appreciated.
    >> Thanks.

    >
    > This should solve all your problems:
    >
    > http://sqlserver2000.databases.aspfaq.com/how-can-i-make-my-sql-queries-case-sensitive.html
    >


    Or, alternatively:

    ------------------

    CREATE TABLE #users (
    [id] int identity(1,1) not null,
    [login] varchar(50) not null,
    [pwd] varchar(50) not null
    )
    Declare @login varchar(50), @pwd varchar(50)
    SET NOCOUNT ON
    INSERT INTO #users ([login], [pwd])
    VALUES ('mm', 'aBcDeFGhh')

    Set @login = 'mm'
    Set @pwd = 'aBcDeFGhh'

    SELECT * FROM #users
    WHERE ([login] = @login) AND ([pwd] = @pwd)
    AND (BINARY_CHECKSUM([login]) = BINARY_CHECKSUM(@login))
    AND (BINARY_CHECKSUM([pwd]) = BINARY_CHECKSUM(@pwd))

    DROP TABLE #users
    ------------------

    -Mark
    Mark J. McGinty, Aug 18, 2007
    #7
  8. Mark J. McGinty wrote:
    > SELECT * FROM #users
    > WHERE ([login] = @login) AND ([pwd] = @pwd)
    > AND (BINARY_CHECKSUM([login]) = BINARY_CHECKSUM(@login))
    > AND (BINARY_CHECKSUM([pwd]) = BINARY_CHECKSUM(@pwd))
    >
    > DROP TABLE #users
    > ------------------
    >

    Right, but that of course disallows the use of any indexes on those columns
    .... which of course may not be a problem on a sufficiently small number of
    rows.
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Aug 18, 2007
    #8
  9. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Mark J. McGinty wrote:
    >> SELECT * FROM #users
    >> WHERE ([login] = @login) AND ([pwd] = @pwd)
    >> AND (BINARY_CHECKSUM([login]) = BINARY_CHECKSUM(@login))
    >> AND (BINARY_CHECKSUM([pwd]) = BINARY_CHECKSUM(@pwd))
    >>
    >> DROP TABLE #users
    >> ------------------
    >>

    > Right, but that of course disallows the use of any indexes on those
    > columns ... which of course may not be a problem on a sufficiently small
    > number of rows.


    How so, they are still in the WHERE predicate? The case-insensitive
    comparison is still required, otherwise character transpositions would
    compare as equal...

    I created a composite index on both fields, here's the query plan:

    |--Index Seek(OBJECT:([misc].[dbo].[logintest].[IX_logintest]),
    SEEK:([logintest].[login]=[@login] AND [logintest].[pwd]=[@pwd]),
    WHERE:(binary_checksum([logintest].[login])=binary_checksum([@login])
    AND binary_checksum([logintest].[pwd])=binary_checksum([@pwd]))
    ORDERED FORWARD)

    What's more, binary_checksum is deterministic, so indexable computed columns
    for the checksum values could be created. In fact, a single checksum
    computed for both would be just as good, and slightly more efficient:

    SELECT * FROM #users
    WHERE ([login] = @login) AND ([pwd] = @pwd)
    AND (BINARY_CHECKSUM([login], [pwd]) = BINARY_CHECKSUM(@login, @pwd))


    Of course, none of this approaches the bummers of storing passwords in plain
    text. If the OP stored a hash of the password instead of its text, the case
    sensitivity issue becomes entirely moot.


    -Mark



    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    Mark J. McGinty, Aug 18, 2007
    #9
  10. Mark J. McGinty wrote:
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    >> Mark J. McGinty wrote:
    >>> SELECT * FROM #users
    >>> WHERE ([login] = @login) AND ([pwd] = @pwd)
    >>> AND (BINARY_CHECKSUM([login]) = BINARY_CHECKSUM(@login))
    >>> AND (BINARY_CHECKSUM([pwd]) = BINARY_CHECKSUM(@pwd))
    >>>
    >>> DROP TABLE #users
    >>> ------------------
    >>>

    >> Right, but that of course disallows the use of any indexes on those
    >> columns ... which of course may not be a problem on a sufficiently
    >> small number of rows.

    >
    > How so, they are still in the WHERE predicate?

    You're right. My eyes skimmed over this part: "([login] = @login) AND ([pwd]
    = @pwd)"
    That part allows the index to be used.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Aug 19, 2007
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Boban Dragojlovic

    DropDownList values are case sensitive?

    Boban Dragojlovic, Nov 6, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    2,225
    Boban Dragojlovic
    Nov 6, 2003
  2. Martin Dechev
    Replies:
    0
    Views:
    527
    Martin Dechev
    Apr 2, 2004
  3. Replies:
    0
    Views:
    510
  4. Replies:
    1
    Views:
    2,465
    Mark P
    Apr 6, 2007
  5. Xah Lee
    Replies:
    4
    Views:
    941
Loading...

Share This Page