|
Home > Archive > MS SQL Server > January 2006 > Trigger for illegal characters from web input?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Trigger for illegal characters from web input?
|
|
| __Stephen 2006-01-30, 11:23 am |
| I have a "memo" column in a table and users every once in a while put in ' "
& [ ]
It tends to reek havoc on the ASP page that views the comments left before.
Would it be a good idea to use a trigger on the insert to look for those bad
input items ?
And if so how do you trap for ' anyway?
TIA
| |
| Rick Sawtell 2006-01-30, 11:23 am |
|
"__Stephen" < srussell@transaction
graphics.com> wrote in message
news:O2nusKbJGHA.3944@tk2msftngp13.phx.gbl...
>I have a "memo" column in a table and users every once in a while put in '
>" & [ ]
>
> It tends to reek havoc on the ASP page that views the comments left
> before.
>
> Would it be a good idea to use a trigger on the insert to look for those
> bad input items ?
>
> And if so how do you trap for ' anyway?
>
> TIA
>
A better idea is to use a regular expression on your web page and have it
check for those. If that can't be done, then do it in the biz layer. If
not there, then do it in a sproc. Trigger is probably the last option you
would want to choose.
When you say memo field, I am assuming a text or ntext datatype. If that is
the case, then in TSQL you would be looking at using the PATINDEX function.
If it is a char, varchar, nchar, nvarchar, then you could do either a
PATINDEX or a CHARINDEX looking for bad data.
Rick Sawtell
MCT, MCSD, MCDBA
| |
| __Stephen 2006-01-30, 11:23 am |
|
"Rick Sawtell" <Quickening@msn.com> wrote in message
news:%23QfhoWbJGHA.2896@TK2MSFTNGP09.phx.gbl...
> A better idea is to use a regular expression on your web page and have it
> check for those. If that can't be done, then do it in the biz layer. If
> not there, then do it in a sproc. Trigger is probably the last option
> you would want to choose.
>
> When you say memo field, I am assuming a text or ntext datatype. If that
> is the case, then in TSQL you would be looking at using the PATINDEX
> function. If it is a char, varchar, nchar, nvarchar, then you could do
> either a PATINDEX or a CHARINDEX looking for bad data.
Column is Varchar(255).
How would I add a RegEx to the insert SP then? At best I'm getting the
postion of the "bad" char with patindex().
Thanks.
| |
| Hugo Kornelis 2006-01-30, 8:23 pm |
| On Mon, 30 Jan 2006 09:22:45 -0600, __Stephen wrote:
>I have a "memo" column in a table and users every once in a while put in ' "
>& [ ]
>
>It tends to reek havoc on the ASP page that views the comments left before.
>
>Would it be a good idea to use a trigger on the insert to look for those bad
>input items ?
>
>And if so how do you trap for ' anyway?
>
>TIA
>
Hi Stephen,
You don't need a trigger - a CHECK constraint suffices to keep the
unwanted characters out.
ALTER TABLE YourTable
ADD CONSTRAINT NoIllegalChars
CHECK (YourColumn NOT LIKE '%[''"&[\]]%' ESCAPE '')
As you see, checking for ' is done by doubling it; adding a ] in the
list of illegal characters is done by using a \ as escape character.
--
Hugo Kornelis, SQL Server MVP
|
|
|
|
|