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)
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:
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 )
Thanks for reporting this issue. We’ll take a look at it.
Just FYI: this is still broken in SSMS 18.4 (15.0.18206.0).
Just FYI: this is still broken in SSMS 18.3.1 (15.0.18183.0).
Just FYI: this is still broken in SSMS 18.2 (15.0.18142.0).
I realize that the release notes for the RTM / GA release of SSMS 18.0 states that this bug was fixed, but technically speaking, it is not 100% fixed. It takes a few additional steps now, but I am still able to inject my own T-SQL, thanks to another bug that I found while testing this :-)
This feature, er, bug ;-) still exists in SSMS 18.0 Preview 7.
Please see related suggestion:
SSMS: Allow forcing case-insensitive matching in Object Explorer filters ( https://feedback.azure.com/forums/908035-sql-server/suggestions/36679522-ssms-allow-forcing-case-insensitive-matching-in-o )