Sql-server – SSMS – How to do case-insensitive searches in Object Explorer

It is technically possible to do this case-insensitive filtering, though only due to a bug in SSMS. I don't have SSMS 2014 (version 12.x), but I did get it working in SSMS 17.9.1 and 18.0 Preview 6, connected to an instance with a default collation of Hebrew_100_BIN2.

Attempt Uno (no)

Seeing that the query uses LIKE, I figured I would try using single-character range wildcards (i.e. [ and ]). Passing in a pattern of [Tt][Ee][Ss][Tt] did not work, however, due to certain characters getting escaped. It seems that [, %, and _ all get wrapped in [ and ] to turn them into literals (i.e. no longer having special meaning).

Attempt Dos (Yes)

Seeing that the value used with the LIKE operator (i.e. the value entered into the UI) was concatenated into the query instead of passed in as a variable (i.e. tbl.name like N''%Test%'' instead of tbl.name like N''%'' + @_msparam_XX + N''%''), I started thinking, "Isn't there a reason why it's dangerous to do this with user input? Hmm, I wonder ..." And so I passed in a ' and it was escaped into ''. Normally this is exactly what should happen with a single-quote. However, this is being used in Dynamic SQL, so it's nested one-level deeper than where '' is properly escaped. A string within a string needs a single-quote to be escaped as '''', or double-escaped, because '' merely ends the string within a string.

For example, when filtering on "Tables", the generated code (the one line that matters) is:

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N''%Test%'')

If I had passed in "Te'st", the generated code would have been:

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N''%Te''st%'')

and yes, it does get an "unclosed quotation mark" error.

I tried adding a COLLATE clause, but all collations have underscores, so Latin1_General_100_CI_AS became Latin1[_]General[_]100[_]CI[_]AS. D'oh!

We can't pass in either [ or % without those being wrapped in [ and ], BUT, we can concatenate stuff onto the N'% string, such as the output of a function. And, we can bypass the escaping of the [ and % characters by passing them in as hex / VARBINARY values, and then CONVERT them back into NVARCHAR once inside.

So, when filtering on Stored Procedures, we are starting with normally generated code of:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%'')',N'@_msparam_0 nvarchar(4000),@_msparam_1 

In order to make this work, we need to pass in a string having the following structure (in place of "Test"):

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

We could use -- at the end instead of AND 'x' <> ', but the AND construct ensures that if there is additional parts of the query that following this part, and are on the same line, that it continues to function as expected.

To generate our "escaped" filter, we can use the following:

SELECT CONVERT(VARBINARY(MAX), N'[Tt][Ee][Ss][Tt]%'); -- must end with "%"
-- 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500

Pasting that hex value into our SQL Injection work-around, we get:

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

If we set our filter to the value directly above, the generated code will be:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%'' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND ''x'' <> ''%'')',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'P',@_msparam_1=N'RF',@_msparam_2=N'PC',@_msparam_3=N'0',@_msparam_4=N'1'

And the generated code is passed into sp_executesql. The statement that is executed shows up in Profiler as:

AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N'%' + CONVERT(NVARCHAR(MAX), 0x5B00540074005D005B00450065005D005B00530073005D005B00540074005D002500) AND 'x' <> '%')

This technique works because 0xYYYY.... is not something that the parser is looking for. It's only looking for the literal values of [, _, and %, and we aren't passing those in. We are just passing in something that will be translated into those once the Dynamic SQL is executed.

But it doesn't need to be a VARBINARY string that is passed in. Any function that passes back a string will work. You just need to concatenate the ending of N'% with something, followed by what comes just before %'), hence: ' + {something_ending_with_%'} AND 'x' <> '. I could have used + NCHAR(91) + N'Tt' + NCHAR(93) + to represent [Tt], but that seemed a lot bulkier than a continuous VARBINARY literal. However, if you just want a %, then doing + NCHAR(37) + N' would be just fine. Remember to use NCHAR() and prefix literals with N since this is all NVARCHAR data.

Attempt Tres (Mucho Yes)

So, you don't want to have to create the [Tt][Ee][Ss][Tt] string each time, and convert it to VARBINARY. I get that. Oh, and you have no access to add / change objects in SQL Server. Ok. But you do have access to tempdb, right? If you can create a function in tempdb, then this can be even easier. Just execute the following:

USE [tempdb];
IF (OBJECT_ID(N'dbo.ObjectFilter') IS NOT NULL)
BEGIN
  DROP FUNCTION dbo.ObjectFilter;
END;

GO
CREATE FUNCTION dbo.ObjectFilter(@Name [sysname], @Filter [sysname])
RETURNS BIT
AS
BEGIN
  IF (@Name LIKE N'%' + @Filter + N'%' COLLATE Latin1_General_100_CI_AS_SC)
  BEGIN
    RETURN 1;
  END;

  RETURN 0;  
END;
GO

Then set your filter to:

' AND tempdb.dbo.ObjectFilter(sp.name, N'Test') = 1 AND 'x' <> '

Generated code will be:

CAST(tbl.is_external AS bit)=@_msparam_5 and tbl.name like N'%' AND tempdb.dbo.ObjectFilter(sp.name, N'Test') = 1 AND 'x' <> '%')

You will have to adjust the column alias since tables uses tbl.name instead of sp.name. But now you can pass in any string and it will compare using a case-insensitive collation!!

And, this function will stick around until the instance is restarted. So you will only need to execute the above T-SQL every once in a while.

{ drops mic ... walks off stage } ?

Additional Notes:

1. Keep in mind that this technique works only because there is a bug in SSMS. If that bug is ever fixed, then this work-around will likely stop working. Please see the "UPDATE" section at the bottom for links to the bug report I filed about this, as well as the enhancement suggestion I filed requesting a "case-insensitive" check-box, per filterable property.

2. From Randi's answer:

   Unless you find where the query is stored and change it, ...

   There is no point in looking for the queries. They are coming from the DLLs that make up SSMS. I think C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Microsoft.SqlServer.Management.Sdk.Sfc.dll might be one of them, but not sure.

3. From Bob's comment on the question:

   SSMS does not enforce case-sensitivity unless server collation is case-sensitive.

   Well, it depends. There are two levels of collations — instance-level and database-level — and the one that matters depends on what meta-data someone is trying to filter. Instance-level collation affects filtering instance-level meta data (e.g. Logins, Linked Servers, etc) and meta data in system databases. Database-level collation affects filtering user database meta data (e.g. Users, Tables, etc). In a Contained / Partially-Contained database, filtering the database-level meta data will always use the collation: Latin1_General_100_CI_AS_WS_KS_SC.

   This separation of levels explains the following finding noted in Randi's answer:

   It also appears to not be possible on CS DB's on CI Servers:

4. From Elaskanator's comment on the question:

   Currently it is set to a binary collation (Latin1_General_BIN) which is case-sensitive.

   Just FYI: this is, unfortunately, a very common misconception, but No, Binary Collations are not Case-Sensitive

UPDATE

I have submitted the following feedback items to Microsoft:

1. BUG: SSMS: Object Explorer Filtering allows for SQL Injection (oops)
2. SUGGESTION: SSMS: Allow forcing case-insensitive matching in Object Explorer filters