SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


instead of trigger problem


instead of trigger problem

Author
Message
sukhoi971
sukhoi971
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 57
some virus are updating my tables with some malcisious script

i checked in google its a sql injection done by some bots, executing a stored procedure by a leak in asp script programming
its a big story,

the point is

i wrote a trigger to check if an update contains ".js" in a particular table column if it does
i dont want the data to get inserted if it is not there
i want to get inserted

so i wrote instead of trigger, but its not updating at all, can any 1 explain

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]
Piotr.Rodak
Piotr.Rodak
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1636 Visits: 1761
Do you have any error messages? Can you post code of the trigger?
Piotr

...and your only reply is slàinte mhath
sukhoi971
sukhoi971
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 57
iam unable to see the code via enterprise manager, any sql query to get the code ?

i got the trigger name by doing a query on sysobjects

actually its nothign great

create trigger trig1 on
joblist
instead of update
if(select * from joblist where jobcategory like '%.js%')
being
print 'Trying to insert virus '
end

somthing like that, i will drop the trigger and recreate it

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]
Piotr.Rodak
Piotr.Rodak
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1636 Visits: 1761
But you said you wrote the trigger, you must have its code then? Are you using SQL 2005? Why do you use Enterprise Manager then?
Look at sys.sql_modules catalog view.
Piotr

...and your only reply is slàinte mhath
Matt Miller (4)
Matt Miller (4)
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15739 Visits: 18768
you can simply turn on scripting the trigger in scripting options. Once you do that, ask it to script a CREATE on the relevant table, and you should have the trigger code.

I suspect you're not reissuing the insert command. If you don't specifically do an insert based on the inserted virtual table from within the INSTEAD OF trigger, nothing gets inserted.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
sukhoi971
sukhoi971
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 57
iam using sql server management studio
iam unable to see it the database/programmabilit/triggers section

my website had been subjected to sql injections
my website had been injected 4 times a day, my table fields were updated with <script src ww.abc./b.js tags

wanted to prevent this update ,wrote triggers for it

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]
Piotr.Rodak
Piotr.Rodak
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1636 Visits: 1761
ok these triggers are DDL triggers, looks you don't have any. To see a trigger on a table you must expand table node (in Tables) and there are triggers you need.

...and your only reply is slàinte mhath
sukhoi971
sukhoi971
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 57
thanks i never knew that, iam writing triggers for the first time i guess

i have deleted for that table, i have wrote similar trigger for another
table

iam pasting code

USE [joblist]
GO
/****** Object: Trigger [toempemails] Script Date: 06/11/2008 14:19:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create TRIGGER [toempemails]
on [dbo].[empemails] INSTEAD OF UPDATE
as
IF EXISTS (SELECT * FROM deleted WHERE companyname like '%.js')
begin
print 'trying to insert virus'
end

--select * from empemails

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]
Matt Miller (4)
Matt Miller (4)
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15739 Visits: 18768
You're essentially intercepting ALL updates, and not allowing them to happen. Per BOL:

In contrast with the AFTER trigger, the INSTEAD OF trigger fires before constraint processing and replaces the normal triggering action with the actions defined in the trigger. For example, an INSTEAD OF trigger attached to a view of historical sales data can prevent the data from being modified by replacing the insert, update, and delete triggering actions with a customized error message. Because the INSTEAD OF trigger supersedes the triggering action, the data modification that caused the trigger to execute in this example is never executed. The INSTEAD OF trigger code must include the appropriate INSERT, UPDATE, or DELETE statements if those actions are required. Executing the INSERT, UPDATE, or DELETE statement from inside the INSTEAD OF trigger code will not fire the same trigger again; instead, the insert, update, or delete action is performed.


you're not reissuing the update from within the trigger, so no update ever occurs. You'd have to add an UPDATE statement, based on the INSERTED table.

Something like (I was also curious why you're checking the DELETED table and not the INSERTED table):


USE [joblist]
GO
/****** Object: Trigger [toempemails] Script Date: 06/11/2008 14:19:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create TRIGGER [toempemails]
on [dbo].[empemails] INSTEAD OF UPDATE
as
BEGIN
IF EXISTS (SELECT * FROM deleted WHERE companyname like '%.js')
begin
print 'trying to insert virus'
end
ELSE
begin
update toempemails
set col1=inserted.col1,
col2=inserted.col2
--etc....
from toempemails
inner join inserted i on toempemails.ID=i.ID
end
end

--select * from empemails




Keep in mind that you might be tossing out a bunch of good rows, based on just one being bad, so you might care to rewrite the trigger to only apply to those rows without the '.js' extension. Just do that within the WHERE clause of the update.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
sukhoi971
sukhoi971
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 57
any easy way to just check 1 column, some tables are huge with 80,90 columns, it wud be so bad to type each column name

any easy way to just check 1 condition
if it is not virus code then let the whole table update

i guess you are right, i need to check inserted value

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search