security checking on input string

[joe2079]

Hello all,
I'm very much an SQL noobie and would appreciate any help I can get on this one...

I need to do a security check on an input string whereby if any character outside the valid range appears, the string is rejected.
The valid range is defined as:
a-z, A-Z,0-9 and the characters <space> <comma> <apostrophe> <comma><full-stop><colon> and <semi-colon>.

So I am trying to create a stored procedure with logic comparable to the lines of this (very rough) pseudocode:

FOR EACH character in string
IF character is not valid
reject string;
(end of loop)
Accept string

What is the best way to do this in T-SQL??
I'm guessing looping through the characters is not the best option.

Many thanks for any help,
Joe

[iburyak]

Hello all,
I'm very much an SQL noobie and would appreciate any help I can get on this one...

I need to do a security check on an input string whereby if any character outside the valid range appears, the string is rejected.
The valid range is defined as:
a-z, A-Z,0-9 and the characters <space> <comma> <apostrophe> <comma><full-stop><colon> and <semi-colon>.

So I am trying to create a stored procedure with logic comparable to the lines of this (very rough) pseudocode:

FOR EACH character in string
IF character is not valid
reject string;
(end of loop)
Accept string

What is the best way to do this in T-SQL??
I'm guessing looping through the characters is not the best option.

Many thanks for any help,
Joe

Try something like this:

Declare @Str
Set @Str = 'adfa(f'
If Replace(Replace(@Str, ' ',''),',','') like '%[^A-Z,a-z,0-9,!,:,;,'',"]%'
print 'Bad'
else 
print 'Good'

Replace will take care of <space> and <comma>.
I am not sure what <full-stop> is
See http://www.asciitable.com/

Good Luck.

[ck9663]

Try something like this:

Declare @Str
Set @Str = 'adfa(f'
If Replace(Replace(@Str, ' ',''),',','') like '%[^A-Z,a-z,0-9,!,:,;,'',"]%'
print 'Bad'
else 
print 'Good'

Replace will take care of <space> and <comma>.
I am not sure what <full-stop> is
See http://www.asciitable.com/

Good Luck.

you might want to create a CHECK Constraint for this one. otherwise it'll keep on happening