Over a million developers have joined DZone.

Case Sensitivity In SQL Server

By default the Microsoft SQL Server is case insensitive. Sometimes this is okay and sometimes it is problematic. To understand it properly, we need an example.

· Database Zone

Sign up for the Couchbase Community Newsletter to stay ahead of the curve on the latest NoSQL news, events, and webinars. Brought to you in partnership with Coucbase.

Introduction

By default the Microsoft SQL Server is case insensitive. Sometimes this is okay and sometimes it is problematic. 

To understand it properly, we need an example.

We have a table of objects with the name of tbl_EmployeeLogin, like this:

Tbl_EmployeeLogin

EmployID LoginName PassWord
1 Joydeep Das joydeepdas
2 Rajesh Mishra JOYDEEP DAS
3 Deepasree Das jOyDeEpDaS

So this table has a username (LoginName) and password. If we take a closer look at this table all the passwords are “JoydeepDas” but in different cases. Some are in lower case, some are in upper case and some are mixed case.

So when we make a simple Select statement with Login name and Password, the password must check the case sensitivity before fetching data from table objects.

Step 1: Create the Table Objects First

IF OBJECT_ID(N'[dbo].[tbl_EmployeeLogin]', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_EmployeeLogin];
   END
GO
CREATE TABLE [dbo].[tbl_EmployeeLogin]
    (
       EmployID   INT                NOT NULL IDENTITY PRIMARY KEY,
       LoginName  VARCHAR(50)        NOT NULL,
       [PassWord] VARCHAR(50)        NOT NULL
     );
GO

INSERT INTO [dbo].[tbl_EmployeeLogin]
    (LoginName, [PassWord])
VALUES('Joydeep Das', 'joydeepdas'),
      ('Rajesh Mishra', 'JOYDEEPDAS'),
      ('Deepasree Das', 'jOyDeEpDaS');
GO

SELECT * FROM [dbo].[tbl_EmployeeLogin]
GO
EmployID    LoginName                                   PassWord

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

1           Joydeep Das                                         joydeepdas

2           Rajesh Mishra                                      JOYDEEPDAS

3           Deepasree Das                                      jOyDeEpDaS

(3 row(s) affected)

Step 2: Now Make a Simple Select Statement to Retrieve Records

SELECT *
FROM   [dbo].[tbl_EmployeeLogin]
WHERE  LoginName = 'Joydeep Das'
       AND [PassWord] = 'JOYDEEPDAS';

Output:

EmployID    LoginName                                          PassWord

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

1           Joydeep Das                                        joydeepdas
(1 row(s) affected)

If we take the closer look in the SELECT statement we find that the WHERE clause of the statement is not checking the case sensitivity of the password.

How We Fix It

We can fix it in number of ways.

Step 1: Using COLLATE

SELECT *

FROM   [dbo].[tbl_EmployeeLogin]

WHERE  LoginName = 'Joydeep Das'

       AND [PassWord] = 'JOYDEEPDAS'

           COLLATE SQL_Latin1_General_CP1_CS_AS;

Output:

EmployID    LoginName                                          PassWord

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

(0 row(s) affected)

Step 2: Using VERBINARY

SELECT *

FROM   [dbo].[tbl_EmployeeLogin]

WHERE  LoginName = 'Joydeep Das'

       AND CONVERT(VARBINARY(MAX), [PassWord]) =   

           CONVERT(VARBINARY(MAX),'JOYDEEPDAS');

Output:

EmployID    LoginName                                          PassWord

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

(0 row(s) affected)

Step 3: ALTERING TABLE

ALTER TABLE [dbo].[tbl_EmployeeLogin]

ALTER COLUMN [PassWord] VARCHAR(50)

COLLATE SQL_Latin1_General_CP1_CS_AS;

GO

SELECT *

FROM   [dbo].[tbl_EmployeeLogin]

WHERE  LoginName = 'Joydeep Das'

       AND [PassWord] = 'JOYDEEPDAS';

Output:

EmployID    LoginName                                          PassWord

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

(0 row(s) affected)

Hope you like it.


The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
microsoft sql server ,passing datatable in sql ,pass by value ,pass ,sql server ,COLLATE

Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}