Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Case Sensitivity In SQL Server

DZone's Guide to

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 ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

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.


Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

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

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}