Last active
September 18, 2025 20:51
-
-
Save chadbaldwin/2420a639f89a379e270ab79a602653b8 to your computer and use it in GitHub Desktop.
Revisions
-
chadbaldwin revised this gist
Sep 18, 2025 . 1 changed file with 25 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -48,6 +48,31 @@ FROM #clicky_data c; SELECT clicky_column = (SELECT [processing-instruction(q)] = TRANSLATE(REPLACE('--'+@crlf+c.random_text+@crlf+'--','?>','??') COLLATE Latin1_General_Bin2, @xml_bad, @xml_replace) FOR XML PATH(''), TYPE) FROM #clicky_data c; -- As an inline scalar function GO CREATE OR ALTER FUNCTION dbo.udf_GetTextAsXml ( @text nvarchar(MAX) ) RETURNS xml WITH INLINE = ON AS BEGIN RETURN ( SELECT [processing-instruction(q)] = TRANSLATE( REPLACE('--' + CHAR(13)+CHAR(10) + @text + CHAR(13)+CHAR(10) + '--' , '?>', '??' ) COLLATE Latin1_General_BIN2 , CONCAT('',0x01020304050607080B0C0E0F101112131415161718191A1B1C1D1E1F00) , '?????????????????????????????' ) FOR XML PATH(''), TYPE ) END; GO ------------------------------------------------------------ -
Sep 4, 2025 . 1 changed file with 2 additions and 2 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -4,8 +4,8 @@ CREATE TABLE #clicky_data ( ); INSERT #clicky_data (random_text) VALUES (OBJECT_DEFINITION(-495130372)) -- sys.dm_db_partition_stats , (OBJECT_DEFINITION(-411581074)) -- sys.dm_exec_query_stats -- , (N'This is a ?> test <? bleh') , (N'This '+NCHAR(0)+' is a '+NCHAR(18)+N' test '+NCHAR(20)+N' bleh') , (N'Fun with █ emojis 😄 smile') -
Sep 4, 2025 . 1 changed file with 22 additions and 14 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -9,7 +9,7 @@ VALUES (OBJECT_DEFINITION(-495130372)) -- -495130372 = sys.dm_db_partition_stats -- , (N'This is a ?> test <? bleh') , (N'This '+NCHAR(0)+' is a '+NCHAR(18)+N' test '+NCHAR(20)+N' bleh') , (N'Fun with █ emojis 😄 smile') , (NULL); GO ------------------------------------------------------------ -- My version @@ -55,22 +55,30 @@ GO ------------------------------------------------------------ -- Playing around with different ways to generate the character exclusion list DROP TABLE IF EXISTS #xml_bad; CREATE TABLE #xml_bad ( id int NOT NULL, charlist nvarchar(256) NOT NULL, ); INSERT #xml_bad VALUES (0, NCHAR(1)+NCHAR(2)+NCHAR(3)+NCHAR(4)+NCHAR(5)+NCHAR(6)+NCHAR(7)+NCHAR(8)+NCHAR(11)+NCHAR(12)+NCHAR(14)+NCHAR(15)+NCHAR(16)+NCHAR(17)+NCHAR(18)+NCHAR(19)+NCHAR(20)+NCHAR(21)+NCHAR(22)+NCHAR(23)+NCHAR(24)+NCHAR(25)+NCHAR(26)+NCHAR(27)+NCHAR(28)+NCHAR(29)+NCHAR(30)+NCHAR(31)+NCHAR(0)) , (1, CHAR(1)+CHAR(2)+CHAR(3)+CHAR(4)+CHAR(5)+CHAR(6)+CHAR(7)+CHAR(8)+CHAR(11)+CHAR(12)+CHAR(14)+CHAR(15)+CHAR(16)+CHAR(17)+CHAR(18)+CHAR(19)+CHAR(20)+CHAR(21)+CHAR(22)+CHAR(23)+CHAR(24)+CHAR(25)+CHAR(26)+CHAR(27)+CHAR(28)+CHAR(29)+CHAR(30)+CHAR(31)+CHAR(0)) , (2, (SELECT STRING_AGG(NCHAR([value]),N'') FROM GENERATE_SERIES(1,31) WHERE [value] NOT IN (13,10,9))+NCHAR(0)) , (3, 0x010002000300040005000600070008000B000C000E000F0010001100120013001400150016001700180019001A001B001C001D001E001F000000) , (4, CONVERT(varchar(512),0x01020304050607080B0C0E0F101112131415161718191A1B1C1D1E1F00)) , (5, CONCAT('',0x01020304050607080B0C0E0F101112131415161718191A1B1C1D1E1F00)) -- I'd never use these, but it's fun that they work, maybe code golf? 😄 , (6, CONCAT('',CONVERT(varbinary(512), N'ȁЃࠇఋ༎ᄐጒᔔᤘᬚᴜ'))+CHAR(0)) , (7, N''+NCHAR(0)); DECLARE @sample_string nvarchar(256) = N'This ?> '+NCHAR(0)+' is a '+NCHAR(18)+N' test '+NCHAR(20)+N' <? bleh 😄', @crlf nchar(2) = NCHAR(13)+NCHAR(10); SELECT x.id, x.charlist, DATALENGTH(x.charlist), LEN(x.charlist), HASHBYTES('md5', x.charlist), CONVERT(varbinary(512), x.charlist) , clicky_column = (SELECT [processing-instruction(q)] = TRANSLATE(REPLACE('--'+@crlf+@sample_string+@crlf+'--','?>','??') COLLATE Latin1_General_Bin2, x.charlist, REPLICATE(N'?',LEN(x.charlist))) FOR XML PATH(''), TYPE) FROM #xml_bad x; ------------------------------------------------------------ ------------------------------------------------------------ -
Sep 4, 2025 . 1 changed file with 157 additions and 6 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,8 +1,159 @@ DROP TABLE IF EXISTS #clicky_data; CREATE TABLE #clicky_data ( random_text nvarchar(MAX) NULL, ); INSERT #clicky_data (random_text) VALUES (OBJECT_DEFINITION(-495130372)) -- -495130372 = sys.dm_db_partition_stats , (OBJECT_DEFINITION(-411581074)) -- , (N'This is a ?> test <? bleh') , (N'This '+NCHAR(0)+' is a '+NCHAR(18)+N' test '+NCHAR(20)+N' bleh') , (N'Fun with █ emojis 😄 smile') , (NULL) GO ------------------------------------------------------------ -- My version ------------------------------------------------------------ /* Essentially a rip-off of the sp_whoisactive method. The main thing I changed is the use of TRANSLATE()+REPLICATE() instead of using REPLACE(REPLACE(REPLACE(... I also added a replacement to handle '?>' */ /* Notes: * NUL (0x00 / NCHAR(0) needs to go at the end of the bad string array otherwise it causes isues with TRANSLATE() * Conversion to Latin1_General_Bin2 collation needs to happen before TRANSLATE otherwise NUL won't get replaced */ DECLARE @xml_bad nvarchar(256) = CONCAT('',0x01020304050607080B0C0E0F101112131415161718191A1B1C1D1E1F00), -- NCHAR's 0-31 (excluding CR[0x0D], LF[0x0A], TAB[0x09]) @crlf nchar(2) = NCHAR(13)+NCHAR(10); -- 0x0D000A00 works too, or 0x0D0A for varchar DECLARE @xml_replace nvarchar(256) = REPLICATE(N'?',LEN(@xml_bad)); SELECT clicky_column = ( SELECT [processing-instruction(q)] = TRANSLATE( REPLACE( '--'+@crlf+c.random_text+@crlf+'--', '?>', '??' ) COLLATE Latin1_General_Bin2, @xml_bad, @xml_replace ) FOR XML PATH(''), TYPE ) FROM #clicky_data c; -- As a one liner SELECT clicky_column = (SELECT [processing-instruction(q)] = TRANSLATE(REPLACE('--'+@crlf+c.random_text+@crlf+'--','?>','??') COLLATE Latin1_General_Bin2, @xml_bad, @xml_replace) FOR XML PATH(''), TYPE) FROM #clicky_data c; GO ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ -- Playing around with different ways to generate the character exclusion list DECLARE @xml_bad0 nvarchar(256) = NCHAR(1)+NCHAR(2)+NCHAR(3)+NCHAR(4)+NCHAR(5)+NCHAR(6)+NCHAR(7)+NCHAR(8)+NCHAR(11)+NCHAR(12)+NCHAR(14)+NCHAR(15)+NCHAR(16)+NCHAR(17)+NCHAR(18)+NCHAR(19)+NCHAR(20)+NCHAR(21)+NCHAR(22)+NCHAR(23)+NCHAR(24)+NCHAR(25)+NCHAR(26)+NCHAR(27)+NCHAR(28)+NCHAR(29)+NCHAR(30)+NCHAR(31)+NCHAR(0); DECLARE @xml_bad1 nvarchar(256) = CHAR(1)+CHAR(2)+CHAR(3)+CHAR(4)+CHAR(5)+CHAR(6)+CHAR(7)+CHAR(8)+CHAR(11)+CHAR(12)+CHAR(14)+CHAR(15)+CHAR(16)+CHAR(17)+CHAR(18)+CHAR(19)+CHAR(20)+CHAR(21)+CHAR(22)+CHAR(23)+CHAR(24)+CHAR(25)+CHAR(26)+CHAR(27)+CHAR(28)+CHAR(29)+CHAR(30)+CHAR(31)+CHAR(0); DECLARE @xml_bad2 nvarchar(256); SELECT @xml_bad2 = STRING_AGG(NCHAR([value]),N'') FROM GENERATE_SERIES(1,31) WHERE [value] NOT IN (13,10,9); SET @xml_bad2 += NCHAR(0); DECLARE @xml_bad3 nvarchar(256) = 0x010002000300040005000600070008000B000C000E000F0010001100120013001400150016001700180019001A001B001C001D001E001F000000; DECLARE @xml_bad4 nvarchar(256) = CONVERT(varchar(512),0x01020304050607080B0C0E0F101112131415161718191A1B1C1D1E1F00); DECLARE @xml_bad5 nvarchar(256) = CONCAT('',0x01020304050607080B0C0E0F101112131415161718191A1B1C1D1E1F00); -- I'd never use these, but it's fun that they work, maybe code golf? 😄 DECLARE @xml_bad6 nvarchar(256) = CONCAT('',CONVERT(varbinary(512), N'ȁЃࠇఋ༎ᄐጒᔔᤘᬚᴜ'))+CHAR(0) DECLARE @xml_bad7 nvarchar(256) = N' '+NCHAR(0) DECLARE @sample_string nvarchar(256) = N'This '+NCHAR(0)+' is a '+NCHAR(18)+N' test '+NCHAR(20)+N' bleh 😄'; SELECT x.n, y.x, DATALENGTH(y.x), LEN(y.x), HASHBYTES('md5', y.x), CONVERT(varbinary(512), y.x) , clicky_column = (SELECT [processing-instruction(q)] = TRANSLATE(@sample_string COLLATE Latin1_General_Bin2, x.x, REPLICATE(N'?',LEN(x.x))) FOR XML PATH(''), TYPE) FROM (VALUES (0,@xml_bad0), (1,@xml_bad1), (2,@xml_bad2), (3,@xml_bad3), (4,@xml_bad4), (5,@xml_bad5), (6,@xml_bad6), (7,@xml_bad7)) x(n,x) CROSS APPLY (SELECT x = x.x) y ------------------------------------------------------------ ------------------------------------------------------------ -- Sandbox ------------------------------------------------------------ -- Playing around with other methods... -- sp_whoisactive method -- https://github.com/amachanic/sp_whoisactive/blob/master/sp_WhoIsActive.sql#L4039-L4079 /* Pros: * Handles control characters Cons: * Fails to handle `?>` - Simple fix to add replace * Very verbose syntax */ SELECT clicky_column = ( SELECT REPLACE ( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( N'--' + NCHAR(13)+NCHAR(10) + c.random_text + NCHAR(13)+NCHAR(10) + N'--' COLLATE Latin1_General_Bin2, NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?'), NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'), NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?'), NCHAR(0), N'' ) AS [processing-instruction(query)] FOR XML PATH(''), TYPE ) FROM #clicky_data c; GO ------------------------------------------------------------ -- CDATA /* Pros: * No escaping needed, doesn't encode anything (AFAIK) * Handles `?>` without issues * Simple / short syntax Cons: * Can only be used directly, cannot be encapsulated into a sub-query otherwise the clickability is lost * If `TYPE` is added, clickability is restored, but the CDATA text is interpolated and encoded * Fails to handle control characters * Concatenates all rows into a single XML object */ SELECT Tag = 1, Parent = 0, [q!1!!CDATA] = c.random_text FROM #clicky_data c FOR XML EXPLICIT; GO ------------------------------------------------------------ -- Special column name /* Pros: * Simple / short syntax Cons: * Fails to handle `?>` - Simple fix to add replace * Fails to handle control characters * Concatenates all rows into a single XML object * Forced column name, must use [XML_F52E2B61-18A1-11d1-B105-00805F49916B] */ SELECT [XML_F52E2B61-18A1-11d1-B105-00805F49916B] = c.random_text FROM #clicky_data c; GO ------------------------------------------------------------ -- Special column name /* Pros: * Simple / short syntax Cons: * Fails to handle `?>` - Simple fix to add replace * Fails to handle control characters * Concatenates all rows into a single XML object * Forced column name, must use [Microsoft SQL Server 2005 XML Showplan] */ SELECT [Microsoft SQL Server 2005 XML Showplan] = c.random_text FROM #clicky_data c; GO ------------------------------------------------------------ ------------------------------------------------------------ -
Sep 4, 2025 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,8 @@ DECLARE @xml_bad nvarchar(30) = 0x0000010002000300040005000600070008000B000C000E000F00010010001100120013001400150016001700180019001A001B001C001D001E001F00; -- NCHAR's 0-31 (excluding CR, LF, TAB) DECLARE @some_random_text nvarchar(MAX); SELECT TOP(1) @some_random_text = definition FROM sys.sql_modules ORDER BY NEWID(); SELECT clickly_column = (SELECT [processing-instruction(q)] = TRANSLATE(REPLACE(CONCAT(N'--', v.crlf, @some_random_text, v.crlf, N'--'),'?>','??') COLLATE Latin1_General_Bin2, @xml_bad, v.xml_replace) FOR XML PATH(''), TYPE)