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 *]{.lang:default .highlight:0 .decode:true .crayon-inline}
Below are two templates you can use to search across the text of SQL queries on your server.
Watch this week's video 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]{.lang:default .highlight:0 .decode:true .crayon-inline} 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.
Photo by 
Photo by 
Photo by