473,320 Members | 1,823 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.

Aug 17 '07 #1
7 2955
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.aspfa...sensitive.html

Aug 17 '07 #2
vu******@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"
Aug 17 '07 #3
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)
Aug 17 '07 #4
On Aug 17, 2:58 pm, "Evertjan." <exjxw.hannivo...@interxnl.netwrote:
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 '07 #5
wrote on 17 aug 2007 in microsoft.public.inetserver.asp.general:
On Aug 17, 2:58 pm, "Evertjan." <exjxw.hannivo...@interxnl.netwrote:
> 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)
Aug 17 '07 #6
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"
Aug 18 '07 #7
Mark J. McGinty wrote:
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:Of**************@TK2MSFTNGP04.phx.gbl...
>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"
Aug 19 '07 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

32
by: Elliot Temple | last post by:
Hi I have two questions. Could someone explain to me why Python is case sensitive? I find that annoying. Also, why aren't there multiline comments? Would adding them cause a problem of some...
9
by: Paul Smith | last post by:
This doesn't seem like it should be *that* difficult, but after quite some time trying to figure it out, I'm still banging my head against the wall. My objective is to examine the exact...
2
by: Matthew Louden | last post by:
I am using VBScript for ASP. The control value must be case sensitive? The following example should print the "fname" value, but if if I change "submit" back to "SUBMIT". Then it works fine. <%...
5
by: Madjid Nasiri | last post by:
Hi, I am basic in oracle. My Old programs write with Delphi and Databases: Access, Paradox, MySQL, Microsoft SQL. I write my code (SQL code) case-insensitivae, but now i need use oracle database....
2
by: J. Muenchbourg | last post by:
I'm doing a few tests with simple .net scripts, and I noticed that I display the following error message at ErrMessage.Text if I don't enter "BLUE" in capital letters into my input textbox: ...
15
by: Thomas Scheiderich | last post by:
I thought I read that the case for the variable names is important. For example Dim Wheel As Integer Wheel here is a different variable from WHEEL. Is this correct?
4
by: xAvailx | last post by:
Hello: I didn't find any documentation that notes save point names are case sensitive, but I guess they are... Stored Proc to reproduce: /* START CODE SNIPPET */ If Exists (Select * From...
38
by: Bart | last post by:
Why is C case sensitive? I know it's a bit late to change it now but there would seem to be far more advantages in ignoring letter case in source code. In real life such a situation would be...
2
by: Lucky | last post by:
Hi guys, I'm having problem with case sensitive collation of SQL Database. one my client is having case sensitive database. While developing the Data Layer i didn't consider this scenario. the...
11
by: Rafe | last post by:
Hi, I'm working within an application (making a lot of wrappers), but the application is not case sensitive. For example, Typing obj.name, obj.Name, or even object.naMe is all fine (as far as...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.