mawdryn
asked on
Need to delay SQL injection while returning control immediately
Hi,
I'm trying to write a sql trigger for an application that requires a field to be changed after specific period of time has elapsed.
I understand that using waitfor and xp_cmdshell will not return control immediately, so while my trigger works, we have the unfortunate side effect of the application locking up until the delay time has passed.
Is there any way to delay injecting this code without interrupting normal program flow?
Thanks.
I'm trying to write a sql trigger for an application that requires a field to be changed after specific period of time has elapsed.
I understand that using waitfor and xp_cmdshell will not return control immediately, so while my trigger works, we have the unfortunate side effect of the application locking up until the delay time has passed.
Is there any way to delay injecting this code without interrupting normal program flow?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You don't say how or why xp_cmdshell not returning is part of the problem, but if it's any help, I use this to shell out and run things without waiting for them to finish;
CREATE PROCEDURE xp_cmdshell_nowait(@cmd varchar(255), @Wait int = 0) AS
SET NOCOUNT ON
--Create WScript.Shell object
DECLARE @result int, @OLEResult int, @RunResult int
DECLARE @ShellID int
EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult)
EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', Null, @cmd, 0, @Wait
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult)
--If @OLEResult <> 0 EXEC sp_displayoaerrorinfo @ShellID, @OLEResult
EXECUTE @OLEResult = sp_OADestroy @ShellID
GO
In fact here are some other ways I use this might come in handy for you. I have a trigger on a table that does this;
IF object_id('tempdb..##p_mpt
BEGIN
DECLARE @hr INT
DECLARE @oPKG INT
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr = 0
EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSQLServer("MYSERV
IF @hr = 0
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr = 0
EXEC @hr = sp_OADestroy @oPKG
END
It will start the DTS Package "DTS Manager Job" as a different user than the one who is running the UPDATE/INSERT
The "DTS Manager Job" executes
msdb..sp_start_job @job_name = 'Start Printer Interface'
The SQL Agent job "Start Printer Interface" runs the procedure;
p_printer_interface
that procedure has this at the beginning
create table ##p_mpt_sync (col1 int)
Then it processes any unprocessed requests, shells out and starts a few background processes using xp_cmdshell_nowait and updates some process logs, then exits automatically dropping ##p_mpt_sync.
The result of all this is that the application can insert 1 or 1000 rows into a table and the application will return immediatly. If the global temporary table ##p_mpt_sync exists, the trigger will do nothing because the manager job is currently running. If the table does not exist, it forces it to start and returns to the calling program.
The job "Start Printer Interface" is also scheduled to run every 15 minutes just in case some timing issues allowed transaction requests to be made while the job was running that it did not service.
SQL 2K5 has a bunch of built in "Service Broker" functions that will replace all of that but I haven't used them yet.
BTW: This whole thing really has nothing to do with printers. It runs a bunch of TCP/IP clients that connect to remote devices and listen for data.
I have also used it to implement triggers to start jobs that transfer data to remote servers "out-of-band" for near real-time remote updates without causing delays for the application making data changes.
CREATE PROCEDURE xp_cmdshell_nowait(@cmd varchar(255), @Wait int = 0) AS
SET NOCOUNT ON
--Create WScript.Shell object
DECLARE @result int, @OLEResult int, @RunResult int
DECLARE @ShellID int
EXECUTE @OLEResult = sp_OACreate 'WScript.Shell', @ShellID OUT
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('CreateObject %0X', 14, 1, @OLEResult)
EXECUTE @OLEResult = sp_OAMethod @ShellID, 'Run', Null, @cmd, 0, @Wait
IF @OLEResult <> 0 SELECT @result = @OLEResult
IF @OLEResult <> 0 RAISERROR ('Run %0X', 14, 1, @OLEResult)
--If @OLEResult <> 0 EXEC sp_displayoaerrorinfo @ShellID, @OLEResult
EXECUTE @OLEResult = sp_OADestroy @ShellID
GO
In fact here are some other ways I use this might come in handy for you. I have a trigger on a table that does this;
IF object_id('tempdb..##p_mpt
BEGIN
DECLARE @hr INT
DECLARE @oPKG INT
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr = 0
EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSQLServer("MYSERV
IF @hr = 0
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr = 0
EXEC @hr = sp_OADestroy @oPKG
END
It will start the DTS Package "DTS Manager Job" as a different user than the one who is running the UPDATE/INSERT
The "DTS Manager Job" executes
msdb..sp_start_job @job_name = 'Start Printer Interface'
The SQL Agent job "Start Printer Interface" runs the procedure;
p_printer_interface
that procedure has this at the beginning
create table ##p_mpt_sync (col1 int)
Then it processes any unprocessed requests, shells out and starts a few background processes using xp_cmdshell_nowait and updates some process logs, then exits automatically dropping ##p_mpt_sync.
The result of all this is that the application can insert 1 or 1000 rows into a table and the application will return immediatly. If the global temporary table ##p_mpt_sync exists, the trigger will do nothing because the manager job is currently running. If the table does not exist, it forces it to start and returns to the calling program.
The job "Start Printer Interface" is also scheduled to run every 15 minutes just in case some timing issues allowed transaction requests to be made while the job was running that it did not service.
SQL 2K5 has a bunch of built in "Service Broker" functions that will replace all of that but I haven't used them yet.
BTW: This whole thing really has nothing to do with printers. It runs a bunch of TCP/IP clients that connect to remote devices and listen for data.
I have also used it to implement triggers to start jobs that transfer data to remote servers "out-of-band" for near real-time remote updates without causing delays for the application making data changes.
ASKER
Hi Kselvia,
according to the MSDN....
Quote: 'xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.'
When I tried using the xp_cmdshell, I used it to run a batch file that called osql after waiting for a few minutes with the dos 'sleep.exe' command. (not the best solution, I know, but it's all I could come up with at the time)
I have already developed a workaround that uses a trigger to add a timestamp into a temporary table which is then monitored by a sql agent job.
Thanks for your response, I'll be sure to try out your suggestion if I have to do something like this again in the future.
according to the MSDN....
Quote: 'xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.'
When I tried using the xp_cmdshell, I used it to run a batch file that called osql after waiting for a few minutes with the dos 'sleep.exe' command. (not the best solution, I know, but it's all I could come up with at the time)
I have already developed a workaround that uses a trigger to add a timestamp into a temporary table which is then monitored by a sql agent job.
Thanks for your response, I'll be sure to try out your suggestion if I have to do something like this again in the future.
ASKER
I figured I would have to go down that path, but thought I'd throw the question out there.