SQl select statement whitespace issue

Question

Values in SQL are USERNAME=ADMIN PASSWORD=ADMIN

SELECT * FROM TBL_USER 
WHERE USERNAME='ADMIN' 
AND PASSWORD COLLATE LATIN1_GENERAL_CS_AS=N'ADMIN'

The above query works fine.

2) If I add a space in front of the password.

SELECT * FROM TBL_USER 
WHERE USERNAME='ADMIN' 
AND PASSWORD COLLATE LATIN1_GENERAL_CS_AS=N'   ADMIN'

This is also correct as it returns a message saying incorrect password.

3) If I add a space in to the end of the password:

SELECT * FROM TBL_USER 
WHERE USERNAME='ADMIN' 
AND PASSWORD COLLATE LATIN1_GENERAL_CS_AS=N'ADMIN '

This query should fail but it doesn't it retrieves data.

Can anyone help me in this.The third condition should fail since the value in table is 'admin' and the value provided is 'admin ' (with whitespaces at end).

`N'ADMIN'` ? What DBMS are you using ? – Denys Séguret Sep 14 '15 at 07:05 — Denys Séguret, Sep 14 '15 at 07:05
I'm using Sql. 'N' is used for nvarchar type column. – Abdul Rahman Sep 14 '15 at 07:08 — Abdul Rahman, Sep 14 '15 at 07:08
"SQL" is not a DBMS product, it's a query language. – Sep 14 '15 at 07:28 — , Sep 14 '15 at 07:28

score 4 · Answer 1 · edited May 23 '17 at 12:14

4

Instead of using = operator use LIKE (without % wildcard)

SELECT * FROM TBL_USER WHERE USERNAME='ADMIN' 
AND PASSWORD COLLATE LATIN1_GENERAL_CS_AS LIKE N'ADMIN '

And here's why: SQL WHERE clause matching values with trailing spaces

edited May 23 '17 at 12:14

Community

1
1

answered Sep 14 '15 at 07:24

Nikhil Vartak

5,002
3
26
32

2

For this specific scenario `like` would work perfectly well. However supposing the stored password was `thisismysupersecurepassword` and someone entered the password value of `%` - this solution is going to fail quite spectacularly. – codingbadger Sep 14 '15 at 07:44
@Barry Appreciate for pointing that out. Went through your suggestion again. Thinking that DATALENGTH would also fail If '12345 ' was entered. Seems like there is no perfect solution to handle this scenario except for having 'no-spaces allowed' password policy on GUI side. – Nikhil Vartak Sep 14 '15 at 07:56

score 2 · Answer 2 · answered Sep 14 '15 at 07:25

This is the expected behaviour of trailing spaces

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

I suggest you add another condition to your where clause:

And DATALENGTH(Password) = DATALENGTH(N'ADMIN ')

This will add another check to ensure the input value length is the same as the Database value.

Full example:

Declare @tblUser table
(
Username nvarchar(50),
Password nvarchar(50)
)
Insert into @tblUser
Values (N'ADMIN',N'ADMIN')


select *
From @tblUser
Where Username = N'ADMIN'
And Password Collate LATIN1_GENERAL_CS_AS = N'ADMIN'

select *
From @tblUser
Where Username = N'ADMIN'
And Password Collate LATIN1_GENERAL_CS_AS = N' ADMIN'

select *
From @tblUser
Where Username = N'ADMIN'
And Password Collate LATIN1_GENERAL_CS_AS = N'ADMIN '
And DATALENGTH(Password) = DATALENGTH(N'ADMIN ')

Why not use LIKE operator simply. See my answer. – Nikhil Vartak Sep 14 '15 at 07:26 — Nikhil Vartak, Sep 14 '15 at 07:26

score 0 · Answer 3 · answered Sep 14 '15 at 07:06

0

You can use trim function

SELECT * FROM TBL_USER WHERE USERNAME=trim('ADMIN') AND PASSWORD COLLATE LATIN1_GENERAL_CS_AS=N trim('ADMIN')

answered Sep 14 '15 at 07:06

Mukesh Kalgude

4,814
2
17
32

Is there is a trimend function? Because I'm allowing user to have whitesapces at front for password. – Abdul Rahman Sep 14 '15 at 07:11
If you are allowing whitesapces to user then not issue you can directly check for exact matching – Mukesh Kalgude Sep 14 '15 at 07:13
As mentioned above, the third query sholud fail,since there is no white space at the back of "admin" in sql table. But the third query retrieves it. – Abdul Rahman Sep 14 '15 at 07:17
IMO trimming or length checking would not work. What if user purposely had suffixed white space in password during registration? (I know this a boundary condition and most sites don't allow such passwords, still!). – Nikhil Vartak Sep 14 '15 at 07:23

score 0 · Accepted Answer · answered Sep 14 '15 at 07:18

0

This will work for you

SELECT * FROM TBL_USER 
WHERE USERNAME='ADMIN' AND PASSWORD COLLATE LATIN1_GENERAL_CS_AS=N'ADMIN ' And LEN(PASSWORD) = LEN(Replace('admin ', ' ' , '_'))

As it will fail if the user uses spaces at the end of the password.

answered Sep 14 '15 at 07:18

Ewan

1,067
8
15

score 0 · Answer 5 · answered Sep 14 '15 at 07:29

0

You can do a right trim in your check.

SELECT * FROM TBL_USER 
WHERE USERNAME='ADMIN' AND PASSWORD COLLATE LATIN1_GENERAL_CS_AS=RTRIM(N'ADMIN ')

answered Sep 14 '15 at 07:29

sww

54
4

SQl select statement whitespace issue

5 Answers5