Searching SQL Query text for keywords

CraigWG

Hi,

We are having some pen testing done against our systems and some of the code is 15-20 years old.  One of the sql injection statements ran an update on all the rows but the testers are not sure which one it was.

I am obviously keen to find out what the statement was, I have searched against dm_exec_query_stats but it looks like they refreshed a day after the statement was ran. My next thought was that SQL Monitor may have a record, is there any way i can search for any SQL statements that contain a table keyword?

Thanks,

Craig

Alex B

Hi @CraigWG,

There is unfortunately not the ability to search the queries at this time - this is something the team are currently investigating options for, but it is not currently possible I'm afraid.

And though we don't support direct querying of the data repository I can say that the _QueryText in the [Cluster_SqlServer_TopQueries_Instances] table is Compressed and can be decompressed using the DECOMPRESS function if your data repository is on SQL Server 2016 (I believe) or later (works in 2017 at least) though you have to cast the column a few ways like this (here querying the [Cluster_SqlServer_TopQueries_Instances_View] that correlates it to a specific entity) which you can then filter with a WHERE down to the cluster and SQL Server level:

SELECT TOP (1000) [Id]
      ,[IdCollectionDate]
      ,[Cluster_SqlServer_TopQueries_ObjectName]
      ,CAST(DECOMPRESS(CAST(CAST([Cluster_SqlServer_TopQueries_QueryText]  AS varchar(MAX)) AS varbinary(MAX))) AS varchar(max)) as decompressedText
      ,[CollectionDate]
      ,[CollectionDate_DateTime]
      ,[Cluster_Name]
      ,[Cluster_SqlServer_Name]
      ,[Cluster_SqlServer_TopQueries_DatabaseName]
      ,[Cluster_SqlServer_TopQueries_SqlHandle]
      ,[Cluster_SqlServer_TopQueries_StatementEnd]
      ,[Cluster_SqlServer_TopQueries_StatementStart]
  FROM [rgmon103].[data].[Cluster_SqlServer_TopQueries_Instances_View]

Hopefully that may help, but if not there's unfortunately nothing else that I can think of that can be done.

Kind regards,

Alex