sp_BlitzIndex: missing indexes column query processing more data than it should, causing error

[BrentOzar]

Shout out to Matt Maddocks and Dean MacFadyen for reporting this.

Version of the script
SELECT @Version = '8.21', @VersionDate = '20240701';

What is the current behavior?
Say you have missing indexes in two databases, GoodDB and BadDB. BadDB has column names that would break XML processing.

If you run sp_Blitz in GoodDB, you can get an error:

GoodDB database failed to process. XML parsing: line 1, character 14, illegal xml character

The root cause is that the query that has a CTE named "ColumnNamesWithDataTypes" in it is querying sys.dm_db_missing_index_details. It's correctly prefixing that DMV with the database name, twice, like this:

FROM ' + QUOTENAME(@DatabaseName) + N'.sys.dm_db_missing_index_details AS id_inner

But that DMV seems to return results for all databases, no matter which database you run it from. If you query GoodDB.sys.dm_db_missing_index_details, it will include rows from BadDB.

The solution is to add a WHERE clause filter to the DMV query as well:

AND id_inner.database_id = DB_ID(''' + QUOTENAME(@DatabaseName) + N''')

If the current behavior is a bug, please provide the steps to reproduce.
I wasn't able to reproduce the XML error, but I didn't dig too deeply in it. I just did testing to make sure that calling the missing index DMV by database name still produces rows from other databases:

There were a couple places we were querying sys.dm_missing_index_details, and in the others, they were already filtering by database_id. I just added db_id filters on the ColumnNamesWithDataTypes query, which seemed to be the only one missing 'em.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
The bug affects all versions, but it never would have been visible in the final output of sp_BlitzIndex. Other filters in the query already removed the missing indexes - however, the missing indexes still would have been processed, and if they had bad characters, they'd have thrown an error. This change just makes processing slightly faster and reduces the chance of throwing an error as long as the db_id filtering is done before the rest of the query's work.