Case Sensitive SQL

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.

vunet.us@gmail.com 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"

On 17 Aug, 18:54, vunet...@gmail.com 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.aspf...r />
tive.html

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)

On Aug 17, 2:58 pm, "Evertjan." <exjxw.hannivo...@interxnl.net> wrote:
>  wrote on 17 aug 2007 in microsoft.public.inetserver.asp.general:
> 
>
> 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.

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

> On Aug 17, 2:58 pm, "Evertjan." <exjxw.hannivo...@interxnl.net> wrote: 
>
> 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)