Spaces where there shouldn''t be

Question

Post reply

Spaces where there shouldn''t be

Susan-322874

SSCrazy

Points: 2300
More actions
May 2, 2007 at 3:00 pm

#118444

Hi,
I ran an update statement against a table with password = 'dba'
The weird thing was that no matter how many spaces I added in my select statement, it would still show the password..
i.e.
Update tbl_realtors
set password = 'dba'

    SELECT company_name, realtor_id, first_name, last_name
    FROM tbl_realtors (NOLOCK)
    WHERE email = 'jean@sql.com'
    AND password = 'dba          '

So I updated it again, but the same thing happened.. Is this normal behavior or is there a setting/configuration I'm missing?
Thanks
Susan

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

What is the datatype of the column? I'll bet it's VARCHAR. That will drop trailing blanks.

-SQLBill

Sergiy SSC Guru Points: 110208 More actions · Answer 2

'dba ' = 'dba'

' ' = ''

But

' dba' < 'dba'

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 3

To get exact match you mast store passwords in VARBINARY strings, not VARCHAR.

_____________
Code for TallyGenerator

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

It looks to be the other way around. The field is CHAR and so the spaces come back in the select statement because the field is a fixed length.

Susan-322874 SSCrazy Points: 2300 More actions · Answer 5

Susan-322874

SSCrazy

Points: 2300

May 3, 2007 at 3:08 pm

#704219

The field is a varchar(20)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Bad call.

Not apropriate datatype for storing passwords.

_____________
Code for TallyGenerator

mrpolecat SSCertifiable Points: 7014 More actions · Answer 7

my bust, I read too quickly and thought the select was returning the password padded with spaces, not that you weren't getting the expected result when you padded the where clause with spaces.

to illustrate Sergiy's point

begin

declare @c1 varbinary(20),@c2 varbinary(20)

set @c1 = convert(varbinary,'dba')

set @c2 = convert(varbinary,'dba ')

if @c1 = @c2

print 'true'

if @c1 <> @c2

print 'false'

end

begin

declare @c1 varchar(20),@c2 varchar(20)

set @c1 = convert(varchar,'dba')

set @c2 = convert(varchar,'dba ')

if @c1 = @c2

print 'true'

if @c1 <> @c2

print 'false'

end

mikedotnet SSC Enthusiast Points: 104 More actions · Answer 8

Besides the fact that passwords are stored as human readable text, could someone explain why it is better to store passwords in VARBINARY versus VARCHAR?

Sergiy SSC Guru Points: 110208 More actions · Answer 9

- uncertain matching (see initial post)

- collation dependency

VARBINARY passwords are as human readable as VARCHAR. Just need to use simple CONVERT function.

Encrypting of passwords is a subject for another discussion.

_____________
Code for TallyGenerator

FP-401493 Right there with Babe Points: 723 More actions · Answer 10

Simple test ...

declare @fp varchar(10)

set @fp = 'dba ';

select len(@fp)

set @fp = ' dba ';

select len(@fp)

set @fp = ' dba .';

select len(@fp)

Verdict : in varchars leading spaces are neglected.

FP

True Love is Like A Ghost. Everyone Talks About It & Nobody have seen it.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

I think you mean that trailing spaces are neglected...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)