Find Illegal Characters in UserName Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Find Illegal Characters in UserName Field

Author

Topic

ekelly
Starting Member

2 Posts

Posted - 2007-06-12 : 11:11:17

I'm trying to write a query that will return me all the usernames that break my rules. The rules are all usernames must be a combination of letters or numbers with no spaces and the only special characters allowed are a dash or underscore.

I tried this;

select username,* from usertable where username not like '%[^-_a-zA-Z1-9]%'

This isn't working for me. Records with a simpy name like "JohnC" get returned with my sql above.

Any help would be great.

phdiwakar
Starting Member

15 Posts

Posted - 2007-06-12 : 11:28:09

ekelly,
You are using two negatives
NOT LIKE
and
^ in the pattern.

try

select username,* from usertable where username like '%[^-_a-zA-Z1-9]%'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-12 : 11:28:37

I think you need to use multiple conditions

Where username not like '%[^-_a-zA-Z1-9]%' and (charindex('-',username)>0 or charindex('_',username)>0)

Madhivanan

Failing to plan is Planning to fail

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-12 : 11:31:46

You may need escape character for '_'.

Kristen
Test

22859 Posts

Posted - 2007-06-12 : 12:23:17

"numbers ... like '%[^-_a-zA-Z1-9]%'"

"numbers" = excluding Zero ?

Kristen

ekelly
Starting Member

2 Posts

Posted - 2007-06-12 : 13:05:43

Thanks guys. I think it was the double negative issue. Working now.

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-12 : 15:11:39

quote:
Originally posted by ekelly

I'm trying to write a query that will return me all the usernames that break my rules. The rules are all usernames must be a combination of letters or numbers with no spaces and the only special characters allowed are a dash or underscore.

I tried this;

select username,* from usertable where username not like '%[^-_a-zA-Z1-9]%'

This isn't working for me. Records with a simpy name like "JohnC" get returned with my sql above.

Any help would be great.

For the record, I hate websites that won't allow me to use special characters. It's weakening the strength of a username and/or password. There's no reason why you shouldn't allow all characters.

Kristen
Test

22859 Posts

Posted - 2007-06-12 : 16:50:49

In the case of usernames, rather than passwords, there are lots of places where it is likely to be used - within web pages, as Request and Post parameters, within <INPUT> tags.

With the best will in the world you could PLAN to encode them wherever they were used, and deal with the encoding when it happens to get transmitted through to an SProc or whatever, and so on.

But it is safer, and probably cheaper [i.e. development cost], to not allow characters that might break those scenarios in the first place.

So often a Business Decision decrees that avoiding possible security issues means using a narrow character set for fields such as User Name.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources