3

Preface: I am not referring to queries.

There are numerous places in SSMS that allow for filtering, such as the Object Explorer and Profiler, but these all treat filters as case-sensitive with no visible option otherwise, so if you search for contains 'ASDF' then values like "ASDF_MyEntity" are included but "asdf_MyEntity" are omitted.

enter image description here

For example, we have a lot of SQL Agent jobs on a large server, and I'm trying to use the Object Explorer to filter them by project name, which we always prefix the job names with. However, these appear as both uppercase and lowercase variants.

Another use case is searching for related stored procedure to a module among thousands of entries. If one is named inconsistently (e.g. PascalCase versus camelCase), the filtered search will omit it. This seems like quite an unnecessary hazard of phantoms for debugging.

Furthermore, the sorting in the Object Explorer places uppercase variants before lowercase (so uppercase Z comes before lowercase a) for every type of entity (e.g. table names, stored procedures, job names, etc.), so I either have to scroll around a lot, or check two different filters (or more if it's not all uppercase or all lowercase letters — 2len(name) times to be precise).

I realize I can just query jobs, tables, and other entities manually, but doing this is nonsensical given that SSMS exists, as its name is "SQL Server Management Studio."

Is there something I can do to make SSMS (and SQL Server Profiler) ignore case like every other Windows application? Perhaps I can change a collation setting locally?

Also, why was this decision made by Microsoft when implementing SSMS? I have found it to be only detrimental.

Furthermore, the loading splash screen for SSMS says "built on Visual Studio" which ignores case in Solution Explorer.

P.S. Using SSMS 2014 (version 12.0.5214.6). I cannot make any server-side changes, and my local development environment should match the target server environment for testing purposes.

  • 3
    SSMS does not enforce case-sensitivity unless server collation is case-sensitive. what is your server collation? – Bob Klimes Jan 28 at 16:23
  • 2
    well there is your answer. SSMS does case-sensitive search/filter because the instance is case-sensitve. – Bob Klimes Jan 28 at 17:44
  • 1
    @BobKlimes and Elaskanator: some of these statements regarding collations and case-sensitivity are incorrect. please see my answer for details :-). – Solomon Rutzky Jan 29 at 8:24
  • 1
    So, here's where I plug Contained Database Collations. Almost no one wants a case-sensitive catalog, and Contained Database Collations allow you to use whatever collation you want for your data, without all the headaches that come from not having a case-insensitive collation for object names and code. docs.microsoft.com/en-us/sql/relational-databases/databases/… – David Browne - Microsoft Jan 29 at 15:15
  • 1
    Right your data can be whatever collation you want, your metadata will use a case-insensitive collation. So SSMS filtering will work as OP wants, and you would never have two objects whose names differ only be case. And setting instance collation is usually done to enable data to use the same collation, because tempdb uses the instance collation. – David Browne - Microsoft Jan 29 at 15:44
8

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
  • First off, very nice! Also, do you have some more information on the SQL-Injection/Workaround with the hex values in the like operator? – Randi Vertongen Jan 29 at 11:27
  • 1
    Hi @RandiVertongen and thanks. Sure, I can provide more info. Is there something in particular that is unclear or needs further explanation? – Solomon Rutzky Jan 29 at 14:25
  • What I am interested in in particular is how you knew that changing it to a hex value bypassed the [ and %. But I think I got it now, I think it was done just because then, ] and % are not present in the hex string. Could you have also passed char() (with the correct char numbers for [ and %) as to pass [ and %? – Randi Vertongen Jan 29 at 14:49
  • 2
    @RandiVertongen Yes, you got the idea. Using 0xYYYY is not something that the parser was looking for. It is only looking for the literal values of [, _, and %. And exactly, I could have used + NCHAR(91) + N'Tt' + NCHAR(93) + to represent [Tt], but that seemed a lot bulkier than a continuous VARBINARY literal. But if you just wanted the %, then doing + NCHAR(37) + N' would be just fine. Remember to use NCHAR() and prefix literals with N since this is all NVARCHAR data. I will see about adding this info into my answer. – Solomon Rutzky Jan 29 at 14:59
  • 1
    @RandiVertongen Yer quite welcome :). And, I have updated with some clarification, most of which came from my previous comment. Please click on the "edited ...." link to see what changed. – Solomon Rutzky Jan 29 at 16:10
3

Using the profiler to capture the queries that runs when applying the Contains filter, I found the query:

    exec sp_executesql N'SELECT
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
        serverproperty(N''Servername'')
       AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/StoredProcedure[@Name='' + quotename(sp.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(sp.schema_id),'''''''') + '']'' AS [Urn],
sp.create_date AS [CreateDate],
ISNULL(ssp.name, N'''') AS [Owner],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(CAST(
 case 
    when sp.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = sp.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N''microsoft_database_tools_support'') 
        is not null then 1
    else 0
end          
             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 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'0'

Which contains the part:

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

And gives me the result:

Name    Schema  Urn CreateDate  Owner   IsEncrypted ImplementationType  IsNativelyCompiled
TestCs  dbo Server[@Name='DESKTOP-602CJ92\  2019-01-28 20:05:28.220 dbo 0   1   0

As @BobKlimes noted, which means it is not possible on CS Servers.

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

enter image description here

As a result, we have the same issue as you noted in the added link.

Unless you find where the query is stored and change it, I don't think there is a way around it.


Tried to add the collation hint using plan guides

The following query does give me what I want,as I added the COLLATE hint, so i tried creating a plan guide on the original query, with the collated query's query plan XML.

Query that gives me the needed filtering

exec sp_executesql N'SELECT
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
        serverproperty(N''Servername'')
       AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/StoredProcedure[@Name='' + quotename(sp.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(sp.schema_id),'''''''') + '']'' AS [Urn],
sp.create_date AS [CreateDate],
ISNULL(ssp.name, N'''') AS [Owner],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(CAST(
 case 
    when sp.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = sp.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N''microsoft_database_tools_support'') 
        is not null then 1
    else 0
end          
             AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%''COLLATE Latin1_General_CI_AS)',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'0'

Result:

Name    Schema  Urn CreateDate  Owner   IsEncrypted ImplementationType  IsNativelyCompiled
testCs  dbo Server[@Name='DESKTOP-602CJ92\  2019-01-28 20:05:23.830 dbo 0   1   0
TestCs  dbo Server[@Name='DESKTOP-602CJ92\  2019-01-28 20:05:28.220 dbo 0   1   0

Create the plan guide (XML not shown since too big) Followed this Answer

EXEC sp_create_plan_guide 
    @name = 'Filtering', 
    @stmt = N'SELECT
sp.name AS [Name],
SCHEMA_NAME(sp.schema_id) AS [Schema],
''Server[@Name='' + quotename(CAST(
        serverproperty(N''Servername'')
       AS sysname),'''''''') + '']'' + ''/Database[@Name='' + quotename(db_name(),'''''''') + '']'' + ''/StoredProcedure[@Name='' + quotename(sp.name,'''''''') + '' and @Schema='' + quotename(SCHEMA_NAME(sp.schema_id),'''''''') + '']'' AS [Urn],
sp.create_date AS [CreateDate],
ISNULL(ssp.name, N'''') AS [Owner],
CAST(CASE WHEN ISNULL(smsp.definition, ssmsp.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
CASE WHEN sp.type = N''P'' THEN 1 WHEN sp.type = N''PC'' THEN 2 ELSE 1 END AS [ImplementationType],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, ''OwnerId'')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = sp.object_id
WHERE
(sp.type = @_msparam_0 OR sp.type = @_msparam_1 OR sp.type=@_msparam_2)and(CAST(
 case 
    when sp.is_ms_shipped = 1 then 1
    when (
        select 
            major_id 
        from 
            sys.extended_properties 
        where 
            major_id = sp.object_id and 
            minor_id = 0 and 
            class = 1 and 
            name = N''microsoft_database_tools_support'') 
        is not null then 1
    else 0
end          
             AS bit)=@_msparam_3 and ISNULL(sm.uses_native_compilation,0)=@_msparam_4 and sp.name like N''%Test%'')',
    @type = 'SQL',
    @params = N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',
    @hints = @planXML
GO

While I can create the plan guide, does not seem to stick to the original query.

Commands completed successfully.

No dice

Name    Schema  Urn CreateDate  Owner   IsEncrypted ImplementationType  IsNativelyCompiled
TestCs  dbo Server[@Name='DESKTOP-602CJ92\  2019-01-28 20:05:28.220 dbo 0   1   0

Error found

SELECT *
FROM sys.plan_guides 
    CROSS APPLY sys.fn_validate_plan_guide (plan_guide_id)
WHERE name = 'Filtering2'

enter image description here

The same error happened when trying a different approach to adding the XML plan to a plan guide

DECLARE @xml_showplan nvarchar(max);  
SET @xml_showplan = (SELECT query_plan  
    FROM sys.dm_exec_query_stats AS qs   
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp  
    WHERE st.text LIKE N'%uses_native_compilation%' and  st.text like '%COLLATE%'

enter image description here

My guess is that it is short for:

NO_PLAN: query processor could not produce query plan because forced plan could not be verified to be legal for query

Source

I am probably doing something not possible with plan guides.

  • 1
    Nice attempt at hacking it though! – Elaskanator Jan 28 at 20:21
  • 2
    Excellent investigative work, and very creative approach to try the plan guide, even if it didn't work. Well done :). – Solomon Rutzky Jan 29 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.