UPDATE Trigger possible?

Last Post 18 Sep 2008 03:53 AM by dstoltz. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dstoltz
New Member
New Member

--
17 Sep 2008 09:19 AM
Is it possible to write an UPDATE trigger that would:

1) Check every UPDATE for any COLUMN, and TABLE
2) If a passed in value was "<script" for example, that it would
NOT do the update...

Any suggestions?
dstoltz
New Member
New Member

--
17 Sep 2008 11:11 AM
Thanks for the reply -

In other words, ANY update that is executed by say, ASP code, that updates, TABLE1, TABLE2, TABLE3, etc (no matter what table), and no matter what column in that table, I want to stop the update if any of the values (the parameters passed to the update trigger) contain the text "<script"

If I pass 3 values, to update 3 columns in a single table, how can my UPDATE trigger check all three values for "<script" ????

For instance, using your code:
IF EXISTS (SELECT * FROM Inserted WHERE value1 LIKE '%script%')
ROLLBACK TRAN

would work, but is there an easier way than this:
IF EXISTS (SELECT * FROM Inserted WHERE value1 LIKE '%script%' OR value2 LIKE '%script%' OR value3 LIKE '%script%')
ROLLBACK TRAN

You see what I mean - because I don't know how many things will be updated - sometimes it might be only 1 column, sometimes 20...

Would it be easier to somehow cursor through all the inserted values checking for %script% ???

I don't know - I hope I'm making sense....

Thanks for any further help!
dstoltz
New Member
New Member

--
18 Sep 2008 03:53 AM
My reasons: help prevent SQL injection....

I know all the other Application layer protections, but I read SQL Injection could also be defended with triggers....

That said, I'm trying to figure out how....the article didn't go into details....

The reason I'm looking for "<script" is because I have a client who has these botnots attacking his site, and injecting (appending) all kinds of malware scripts into the text fields of the database...

I think I have the application layer pretty well protected at this point, but I wanted to go the extra mile...
dstoltz
New Member
New Member

--
18 Sep 2008 08:52 AM
Yes it does...I know this isn't as safe as using a Stored Procedure with passes parameters...but the code I'm now using stands up to all the pen-test/scanner tools I've used, including Acunetix and Scrawlr.

That's the way I think I'm going to go...writing an SP for every needed query is kind of a pain, but I guess it's a lot better than being hacked!

Thanks!
SQLUSA
New Member
New Member

--
23 Sep 2008 06:15 AM
quote:

Originally posted by: dstoltz

That's the way I think I'm going to go...writing an SP for every needed query is kind of a pain, but I guess it's a lot better than being hacked!




Certainly, being hacked can endanger your job.

Stored procedures have lots of advantages. Try to avoid Dynamic SQL in a stored procedure.

If you must use Dynamic SQL, you have t parse the appropriate text parameters for SQL Injection Attack.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQL Server Training, SSAS, SSIS, SSRS: http://www.sqlusa.com/



Acceptable Use Policy
---