Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

SSMS: Object Explorer Filtering allows for SQL Injection (oops)

When applying filters in Object Explorer (in any "Filter Settings" dialog) in SSMS 17.9.1 and 2018 Preview 6 (and possibly versions prior to 17), it is possible to inject SQL into the query executed by SSMS, simply by using a single quote / apostrophe. Yes, the app code clearly escapes a single apostrophe into two apostrophes, but the string containing the apostrophe is concatenated into a Dynamic SQL string executed via sp_executesql. The string entered via the UI to filter on is intended to be used within a string literal within the Dynamic SQL string. In this case I filtered Stored Procedures, selected "Contains" as the Operator, and passed in "Test" (without the quotes). It generated a Dynamic SQL string containing:

and sp.name like N''%Test%'')'

I then updated the filter Value to be "Te'st" (without the quotes), and it generated the following Dynamic SQL:

and sp.name like N''%Te''st%'')'

which, as soon as it was executed, resulted in an "unclosed quotation mark" error. Full error details are:

-----------------------------------------------------------------
TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Unclosed quotation mark after the character string ')
OPTION (FORCE ORDER)
'.
Incorrect syntax near ')
OPTION (FORCE ORDER)
'. (Microsoft SQL Server, Error: 105)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.00.3045&EvtSrc=MSSQLServer&EvtID=105&LinkId=20476
-----------------------------------------------------------------

That allows me to pass in the following Value for Name such that I could get a case-insensitive comparison in a database that has a binary default collation:

' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND 'x' <> '

Once injected and executed, the resulting string evaluates to:

and sp.name like N'%' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND 'x' <> '%')

And the CONVERT evaluates to:

[Tt][Ee][Ss][Tt]%

which means that the query becomes:

and sp.name like N'%[Tt][Ee][Ss][Tt]%' AND 'x' <> '%')

The reason for doing the VARBINARY literal is that the "%" and "[" characters are escaped by SSMS and become "[%]" and "[[]", respectively. Hence, this was a way to sneak those characters in ;-).

Full details, including an even easier way of accomplishing the case-insensitive comparison without having to use "[" and "]" around each character, can be found in my answer to the following question on DBA.StackExchange:

SSMS - How to do case-insensitive searches in Object Explorer ( https://dba.stackexchange.com/a/228324/30859 )

4 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

Solomon Rutzky shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
planned  ·  Matteo Taveggia responded  · 

Thanks for reporting this issue. We’ll take a look at it.

2 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base