capture and save running sql query to database table (for logging)

[=?ISO-8859-1?Q?Gear=F3id?=]

Hey,

This may sound odd, but is there anyway to catch the current or just
run query from inside a trigger? Kinda like how profiler displays the
query just as you've run it, along with all the statistical data...
But I'm just looking to capture the query itself and save it in a
logging table.

I just need to save an executing query in certain circumstances (if
detected an attempted sql injection attack) for logging purposes.

On MS SQL Server 2005

Hope someone can help...

Thanks!

Gearóid

[Plamen Ratchev]

You may not be able to do that in a trigger. In SQL Server 2005 you can get
the current statement with this query:

SELECT [text]
FROM sys.dm_exec_requests AS R
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS S
WHERE session_id = @@SPID;

However, running this inside a trigger returns the SQL statement to create
the trigger.

The only statement that will actually output the current query is:

DBCC INPUTBUFFER(@@SPID);

But you cannot really store the result set from DBCC to a table.

One way to accomplish what you need is to set up a server side trace.

BTW, if the purpose of this is to prevent SQL injection attacks, you can go
the other way around and fix the code to prevent them rather than audit.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[=?ISO-8859-1?Q?Gear=F3id?=]

Thanks for the reply, I'll take a look into your suggestions.

But yeah - the injections are happening from older, badly written
classic ASP pages with lots of dynamic sql, which we're looking to
rewrite and fix up, and will also be migrating to .NET pretty soon
anyway. This was kinda just meant as a last resort catch while we're
fixing the pages.

[Plamen Ratchev]

Actually you can save the output from DBCC INPUTBUFFER to a table. Of
course, since a trigger is invoked only on INSERT/UPDATE/DELETE you cannot
audit SELECT statements (for that you can still use server side trace).

Here is a sample trigger that will save the SQL data modification statements
against a table.

-- SQL log table
CREATE TABLE SQLLog (
language_event NVARCHAR(100),
parameters INT,
event_info NVARCHAR(4000),
event_time DATETIME DEFAULT CURRENT_TIMESTAMP);

-- Sample table to audit actions for
CREATE TABLE Foo (
keycol INT PRIMARY KEY,
datacol CHAR(1));

-- Sample data
INSERT INTO Foo VALUES (1, 'a');
INSERT INTO Foo VALUES (2, 'b');
INSERT INTO Foo VALUES (3, 'c');

GO

-- Audit trigger
CREATE TRIGGER LogMySQL
ON Foo
AFTER INSERT, UPDATE, DELETE
AS
INSERT INTO SQLLog (language_event, parameters, event_info)
EXEC('DBCC INPUTBUFFER(@@SPID);');
GO

-- Performs some logged actions
GO

INSERT INTO Foo VALUES (4, 'd');

GO

DELETE Foo
WHERE keycol = 1;

GO

UPDATE Foo
SET datacol = 'f'
WHERE keycol = 2;

GO

-- Perform non-logged action
-- SELECT cannot be logged
SELECT datacol
FROM Foo
WHERE keycol = 4;

GO

-- Check what we have in the log
SELECT event_info, event_time
FROM SQLLog;

/*

-- Results

event_info event_time
-------------------------------- -----------------------
INSERT INTO Foo VALUES (4, 'd'); 2008-04-24
22:24:31.153
DELETE Foo WHERE keycol = 1; 2008-04-24
22:24:31.170
UPDATE Foo SET datacol = 'f' WHERE keycol = 2; 2008-04-24 22:24:31.170

*/

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Plamen Ratchev]

It is good to note here that DBCC INPUTBUFFER requires the user executing to
be member of the sysadmin fixed server role. One way to handle this is to
specify user or login with sufficient privileges in EXEC (you can use EXEC
AS LOGIN or USER:

INSERT INTO SQLLog (language_event, parameters, event_info)
EXEC('DBCC INPUTBUFFER(@@SPID);') AS LOGIN = 'admin_login';

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[=?ISO-8859-1?Q?Gear=F3id?=]

Thanks a million Plamen by the way, for your time and help.
Appreciate it. Forgot to say it before!