No case sensitivity with SQL query?

No case sensitivity with SQL query?

Post by Sascha Noethe » Fri, 16 Feb 2001 08:37:23



Hi,

I'm using an SQL query to check username and password. At the first page,
people enter their username and password into a web form and then submit it
to the second page.

Now I want the SQL query to have an empty result if the entered values for
username and/or password do not match the values for username and password
of any recordset in my users table in the database.

So on the second page I take the values for username and password from the
web form

 Request.Form("UserName")
and
 Request.Form("Password")

for using them in the SQL query as

    varUserName
and
    varPassword

Now my SQL query looks like this:

    SELECT *

    FROM UsersTable

    WHERE UserName = varUserName AND Password = 'varPassword'

It works as far as I have to enter the correct username and password
combination, otherwhise the query result will be empty. That's what I want
:-)

But the problem is: I can use either high or low case letters when entering
username and password into my web form and it works any way!

How can I make sure that not only the correct username and password are
entered but also the correct high and low case letters are used?

Please help!

Sascha

 
 
 

No case sensitivity with SQL query?

Post by BP Margoli » Fri, 16 Feb 2001 09:15:25


Sascha,

Please always post which version of SQL Server you are using.

Assuming SQL Server 2000 ...

create table test
(
 c1 varchar(5) collate Latin1_General_CI_AI
)
go

insert into test values ('a')
insert into test values ('A')

select *
from test
where c2 collate Latin1_General_CS_AI = 'a'

----------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Hi,

> I'm using an SQL query to check username and password. At the first page,
> people enter their username and password into a web form and then submit
it
> to the second page.

> Now I want the SQL query to have an empty result if the entered values for
> username and/or password do not match the values for username and password
> of any recordset in my users table in the database.

> So on the second page I take the values for username and password from the
> web form

>  Request.Form("UserName")
> and
>  Request.Form("Password")

> for using them in the SQL query as

>     varUserName
> and
>     varPassword

> Now my SQL query looks like this:

>     SELECT *

>     FROM UsersTable

>     WHERE UserName = varUserName AND Password = 'varPassword'

> It works as far as I have to enter the correct username and password
> combination, otherwhise the query result will be empty. That's what I want
> :-)

> But the problem is: I can use either high or low case letters when
entering
> username and password into my web form and it works any way!

> How can I make sure that not only the correct username and password are
> entered but also the correct high and low case letters are used?

> Please help!

> Sascha


 
 
 

No case sensitivity with SQL query?

Post by Sascha Noethe » Fri, 16 Feb 2001 09:28:20


Hello,

Quote:> Please always post which version of SQL Server you are using.

ooops, sorry ... my first posting in this group ;-)

Quote:> Assuming SQL Server 2000 ...

... furthermore I have to say that I am not using SQL server yet :-(

I will be using MS SQL Server 7 soon but now I am using an Access2000
database with DSN connection.

I thought my problem would be about SQL syntax and I did not find many other
groups where I thought that my question would fit.

As you are asking for SQL Server version do you think this could be a
problem with the database configuration and not with my code?

Sascha

------

> create table test
> (
>  c1 varchar(5) collate Latin1_General_CI_AI
> )
> go

> insert into test values ('a')
> insert into test values ('A')

> select *
> from test
> where c2 collate Latin1_General_CS_AI = 'a'

> ----------------------------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > Hi,

> > I'm using an SQL query to check username and password. At the first
page,
> > people enter their username and password into a web form and then submit
> it
> > to the second page.

> > Now I want the SQL query to have an empty result if the entered values
for
> > username and/or password do not match the values for username and
password
> > of any recordset in my users table in the database.

> > So on the second page I take the values for username and password from
the
> > web form

> >  Request.Form("UserName")
> > and
> >  Request.Form("Password")

> > for using them in the SQL query as

> >     varUserName
> > and
> >     varPassword

> > Now my SQL query looks like this:

> >     SELECT *

> >     FROM UsersTable

> >     WHERE UserName = varUserName AND Password = 'varPassword'

> > It works as far as I have to enter the correct username and password
> > combination, otherwhise the query result will be empty. That's what I
want
> > :-)

> > But the problem is: I can use either high or low case letters when
> entering
> > username and password into my web form and it works any way!

> > How can I make sure that not only the correct username and password are
> > entered but also the correct high and low case letters are used?

> > Please help!

> > Sascha

 
 
 

No case sensitivity with SQL query?

Post by benw » Fri, 16 Feb 2001 18:21:23


These best solution would be to hash the user name and password and then
compare the user's input with the column.  If a match then it was correct
else not.  The password is not stored this, would solve the case issue but
it does involve dabbling with the Crypto API.


> Hello,

> > Please always post which version of SQL Server you are using.

> ooops, sorry ... my first posting in this group ;-)

> > Assuming SQL Server 2000 ...

> ... furthermore I have to say that I am not using SQL server yet :-(

> I will be using MS SQL Server 7 soon but now I am using an Access2000
> database with DSN connection.

> I thought my problem would be about SQL syntax and I did not find many
other
> groups where I thought that my question would fit.

> As you are asking for SQL Server version do you think this could be a
> problem with the database configuration and not with my code?

> Sascha

> ------

> > create table test
> > (
> >  c1 varchar(5) collate Latin1_General_CI_AI
> > )
> > go

> > insert into test values ('a')
> > insert into test values ('A')

> > select *
> > from test
> > where c2 collate Latin1_General_CS_AI = 'a'

> > ----------------------------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > Hi,

> > > I'm using an SQL query to check username and password. At the first
> page,
> > > people enter their username and password into a web form and then
submit
> > it
> > > to the second page.

> > > Now I want the SQL query to have an empty result if the entered values
> for
> > > username and/or password do not match the values for username and
> password
> > > of any recordset in my users table in the database.

> > > So on the second page I take the values for username and password from
> the
> > > web form

> > >  Request.Form("UserName")
> > > and
> > >  Request.Form("Password")

> > > for using them in the SQL query as

> > >     varUserName
> > > and
> > >     varPassword

> > > Now my SQL query looks like this:

> > >     SELECT *

> > >     FROM UsersTable

> > >     WHERE UserName = varUserName AND Password = 'varPassword'

> > > It works as far as I have to enter the correct username and password
> > > combination, otherwhise the query result will be empty. That's what I
> want
> > > :-)

> > > But the problem is: I can use either high or low case letters when
> > entering
> > > username and password into my web form and it works any way!

> > > How can I make sure that not only the correct username and password
are
> > > entered but also the correct high and low case letters are used?

> > > Please help!

> > > Sascha

 
 
 

No case sensitivity with SQL query?

Post by Tibor Karasz » Fri, 16 Feb 2001 22:00:10


Sasha,

Each product has its way of handling case sensitivity. BP, wisely, posted a solution
that works in SQL Server 2000 *and* is ANSI SQL (using COLLATE keyword in SELECT
statement).
But chance is that Access doesn't support that. SQL Server 7 does not for sure.

In SQL Server 7.0, you can convert to binary and compare binary. Same technique
(perhaps not same code) should be doable in other products:

In SQL Server 2000, you have default collation at server level, database level, table
level. All can be overridden, down to column level. Also, you can override the
collation for data in a query using the COLLATE clause.

In SQL Server 7, you can get case sensitive comparison in query by converting to
binary. Below should explain:

Use Northwind

--Create copy of Orders table
SELECT * Into #Orders FROM Orders

--Create Index on ShipName
CREATE INDEX o_sh ON #Orders(ShipName)

--Make sure that we get one Orders.ShipName
--which only differs in case
UPDATE #Orders
SET ShipName = 'save-a-lot markets'
WHERE OrderID = 10627

--Convert string to binary:
SELECT OrderID, ShipName
 FROM #Orders
 WHERE CAST(ShipName AS BINARY(80))
     = CAST(N'save-a-lot markets' AS BINARY(80))

--Enable usage of index
SELECT OrderID, ShipName
 FROM #Orders
 WHERE CAST(ShipName AS BINARY(80))
     = CAST(N'save-a-lot markets' AS BINARY(80))
 AND ShipName = N'save-a-lot markets'

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil at: http://www.sqlserverfaq.com
Please reply to the newsgroup only, not by email.


> Hello,

> > Please always post which version of SQL Server you are using.

> ooops, sorry ... my first posting in this group ;-)

> > Assuming SQL Server 2000 ...

> ... furthermore I have to say that I am not using SQL server yet :-(

> I will be using MS SQL Server 7 soon but now I am using an Access2000
> database with DSN connection.

> I thought my problem would be about SQL syntax and I did not find many other
> groups where I thought that my question would fit.

> As you are asking for SQL Server version do you think this could be a
> problem with the database configuration and not with my code?

> Sascha

> ------

> > create table test
> > (
> >  c1 varchar(5) collate Latin1_General_CI_AI
> > )
> > go

> > insert into test values ('a')
> > insert into test values ('A')

> > select *
> > from test
> > where c2 collate Latin1_General_CS_AI = 'a'

> > ----------------------------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > Hi,

> > > I'm using an SQL query to check username and password. At the first
> page,
> > > people enter their username and password into a web form and then submit
> > it
> > > to the second page.

> > > Now I want the SQL query to have an empty result if the entered values
> for
> > > username and/or password do not match the values for username and
> password
> > > of any recordset in my users table in the database.

> > > So on the second page I take the values for username and password from
> the
> > > web form

> > >  Request.Form("UserName")
> > > and
> > >  Request.Form("Password")

> > > for using them in the SQL query as

> > >     varUserName
> > > and
> > >     varPassword

> > > Now my SQL query looks like this:

> > >     SELECT *

> > >     FROM UsersTable

> > >     WHERE UserName = varUserName AND Password = 'varPassword'

> > > It works as far as I have to enter the correct username and password
> > > combination, otherwhise the query result will be empty. That's what I
> want
> > > :-)

> > > But the problem is: I can use either high or low case letters when
> > entering
> > > username and password into my web form and it works any way!

> > > How can I make sure that not only the correct username and password are
> > > entered but also the correct high and low case letters are used?

> > > Please help!

> > > Sascha

 
 
 

1. Question on Scope of Case-Sensitivity on a Case-Sensitive SQL Server 7.0/2000

We are planning on setting up a development SQL Server with Case-Sensitive
sort order.  We have never had this configuration before and I have a few
questions, that BOL did not answer:

1.  Are all database object ( table, procedure and column names, etc.. )
case sensitive as well?

2.  Are SQL Server reserved words case sensitive (i.e. SELECT, INSERT, etc).

3.  Are built in functions like GETDATE() and SUSER_SNAME() case sensitive?

Thanks,

Tom

2. latch time out ??? SQL Server bug ?

3. case sensitivity in query

4. Datetime and ADO

5. Case sensitivity in a parameter query

6. SQL and MEMO fields

7. Case Sensitivity of Queries

8. Not enough memory error

9. How to control CASE-SENSITIVITY in query?

10. Case sensitivity with queries

11. How can I avoid case sensitivity in queries ?

12. Case (in)sensitivity in queries other than upper()...

13. Case (in)-sensitivity & preserving case