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

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=F3id

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

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

Re: capture and save running sql query to database table (for

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.