Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Case Sensitive SQL

Reply
Thread Tools

Case Sensitive SQL

 
 
vunet.us@gmail.com
Guest
Posts: n/a
 
      08-17-2007
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.

 
Reply With Quote
 
 
 
 
daddywhite
Guest
Posts: n/a
 
      08-17-2007
On 17 Aug, 18:54, (E-Mail Removed) 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.aspfa...sensitive.html

 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-17-2007
http://www.velocityreviews.com/forums/(E-Mail Removed) 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"


 
Reply With Quote
 
Evertjan.
Guest
Posts: n/a
 
      08-17-2007
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)
 
Reply With Quote
 
vunet.us@gmail.com
Guest
Posts: n/a
 
      08-17-2007
On Aug 17, 2:58 pm, "Evertjan." <(E-Mail Removed)> 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.

 
Reply With Quote
 
Evertjan.
Guest
Posts: n/a
 
      08-17-2007
wrote on 17 aug 2007 in microsoft.public.inetserver.asp.general:

> On Aug 17, 2:58 pm, "Evertjan." <(E-Mail Removed)> 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)
 
Reply With Quote
 
Mark J. McGinty
Guest
Posts: n/a
 
      08-18-2007

"daddywhite" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) ps.com...
> On 17 Aug, 18:54, (E-Mail Removed) 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.aspfa...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




 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-18-2007
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"


 
Reply With Quote
 
Mark J. McGinty
Guest
Posts: n/a
 
      08-18-2007

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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]),
WHEREbinary_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"
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-19-2007
Mark J. McGinty wrote:
> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
check file exists with case sensitive on a case insensitive filesystem Xah Lee Perl Misc 4 04-05-2009 11:21 PM
case insensitive find on case sensitive stl map benhoefer@gmail.com C++ 1 04-06-2007 08:42 PM
Outputcache shared property is case sensitive? eriera1@hotmail.com ASP .Net 0 07-29-2004 07:37 PM
Re: Results from fileinfo - how to return, case-sensitive Martin Dechev ASP .Net 0 04-02-2004 01:33 PM
DropDownList values are case sensitive? Boban Dragojlovic ASP .Net 0 11-06-2003 03:30 PM



Advertisments