Have you ever wanted to find something that was referenced in the body of a SQL query?

Maybe you need to know what queries you will have to modify for an upcoming table rename.  Or maybe you want to see how many queries on your server are running 

SELECT *

Below are two templates you can use to search across the text of SQL queries on your server.

Prefer learning by watching?  Check out this week’s post on YouTube.

1. Searching Stored Procedures, Functions, and Views

If the queries you are interested in are part of a stored procedure, function, or view, then you have to look no further than the 

sys.sql_modules

 view.

This view stores the query text of every module in your database, along with a number of other properties.

You can use something like the following as a template for searching through the query texts of these database objects:

USE [<database name>];
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
o.type_desc AS ObjectType,
DB_NAME(o.parent_object_id) AS DatabaseName,
s.name as SchemaName,
o.name as ObjectName,
r.Definition
FROM
sys.sql_modules r
INNER JOIN sys.objects o
ON r.object_id = o.object_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE
-- put your search keyword here
r.Definition LIKE '%SELECT%'

For example, I recently built a query for searching stored procedures and functions that might contain SQL injection vulnerabilities.

Using the starting template above, I added some filtering in the WHERE clause to limit my search to queries that follow common coding patterns that are vulnerable to SQL injection:

USE [<database name>];
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
o.type_desc AS ObjectType,
DB_NAME(o.parent_object_id) AS DatabaseName,
s.name as SchemaName,
o.name as ObjectName,
r.Definition
FROM
sys.sql_modules r
INNER JOIN sys.objects o
ON r.object_id = o.object_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE
-- Remove white space from query texts
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
r.Definition,CHAR(0),''),CHAR(9),''),CHAR(10),''),CHAR(11),''),
CHAR(12),''),CHAR(13),''),CHAR(14),''),CHAR(160),''),' ','')
LIKE '%+@%'
AND
( -- Only if executes a dynamic string
r.Definition LIKE '%EXEC(%'
OR r.Definition LIKE '%EXECUTE%'
OR r.Definition LIKE '%sp_executesql%'
)

2. Searching Ad-Hoc SQL Queries

Searching across ad-hoc queries is a little tougher.  Unless you are proactively logging the query texts with extended events or some other tool, there is no way to definitively search every ad-hoc query text.

However, SQL Server does create (or reuse) an execution plan for each query that executes.  Most of those plans are then added to the execution plan cache.

Execution plans are eventually removed from the cache for various reasons, but while they exist we can easily search their contents, including searching through that plan’s query text.

As a starting point, you can use the following code to retrieve SQL query texts that are currently stored in the plan cache:

USE [<database name>];
GO
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)') AS [Query],
   query_plan AS [QueryPlan]
FROM 
sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
WHERE
-- put your search keywords here
stmt.value('(@StatementText)[1]', 'varchar(max)') LIKE '%SELECT%'

Although the template above searches for the query texts in our execution plans, you can also use it to search for other query plan elements, such as elements that indicate if you have non-sargable query.

I used this technique recently to search for ad-hoc queries that might be vulnerable to SQL injection.  I modified the template above to search the input parameter values instead of the query texts, flagging any values that look like they might have some injection code in them:

USE [<database name>];
GO
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
   stmt.value('(@StatementText)[1]', 'varchar(max)') AS [Query],
   query_plan AS [QueryPlan],
   stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') AS [ParameterValue] 
FROM 
sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
WHERE
-- if single quotes exist in a parameter
stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') like '%''%'
OR stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') like '%sys.objects%'
OR stmt.value('(.//ColumnReference/@ParameterCompiledValue)[1]', 'varchar(1000)') like '%[0-9]=[0-9]%'

So while using this technique won’t allow you to search across 100% of ad-hoc queries, it should be able to search the ones that run most frequently and appear in your plan cache.

Intrigued by how I’m searching query texts for SQL injection vulnerabilities? Attend my online webcast on Tuesday November 14, 2017 at 1PM Eastern at the PASS Security Virtual Group to learn about these queries and how protect yourself from SQL injection.