Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

bind-address mysql multiple, sanibleone xxxx, ftp://192.168.100.100/, www.xxxcon, which comes first ob_start or session, wwwxxx/58/2010, xxxxdup, xxxxdup, mailx informatii, should producers of software-based services, such as atms, be held liable for economic injuries suffered when their systems fail?

Links

XODOX
Impressum

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

Posted on 2008-04-24 11:12:29 by ghealy

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

Report this message

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

Posted on 2008-04-24 15:30:54 by 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

Report this message

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

Posted on 2008-04-24 17:14:56 by ghealy

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.

Report this message