SSMS: Prevent Copy and Paste of Text in “Messages” tab (Cruel Joke #2)

(last updated: 2024-02-07 @ 9:20 ET / 2024-02-07 @ 14:20 UTC )

While researching some odd behavior in SQL Server Management Studio (SSMS) I ran across something even stranger. I was testing the effect of character 0 (sometimes referred to as "NUL") in the "Messages" tab. For those who have not worked with the C programming language (or similar languages), character 0 (usually indicated by the \0 escape sequence) is the “null terminator” for strings. Meaning, this character marks the end of this string and anything after it should not be displayed. For example, executing the following T-SQL:

DECLARE @Test VARCHAR(10) = 'AB' + CHAR(0) + 'CD';

SELECT @Test AS [Contents],
       LEN(@Test) AS [LEN],
       DATALENGTH(@Test) AS [DATALENGTH];

returns:

Contents     LEN     DATALENGTH
AB           5       5

The "CD" characters in the variable are clearly there based on the values returned by LEN and DATALENGTH, but they aren’t displayed due to coming after the “null terminator”.

However, for whatever reason, the PRINT statement has gone rogue and plays by its own rules.

Basic Behavior

I tested the following using:

• SSMS 18.4 with SQL Server 2019 (RTM-GDR) (KB4517790) – 15.0.2070.41
• SSMS 18.5 with SQL Server:
  • 2012 (SP4-GDR) (KB4057116) – 11.0.7462.6
  • 2014 (SP3-CU4) (KB4500181) – 12.0.6329.1
  • 2016 (SP2-CU12) (KB4536648) – 13.0.5698.0
  • 2017 (RTM-CU18) (KB4527377) – 14.0.3257.3
  • 2019 (RTM-GDR) (KB4517790) – 15.0.2070.41
• SSMS 19.2(.56.2) and 19.3(.4.0) with SQL Server:
  • 2017 (RTM-CU31-GDR) (KB5029376) – 14.0.3465.1
  • 2022 (RTM-CU10-GDR) (KB5033592) – 16.0.4100.1

PRINT

DECLARE @String VARCHAR(20) = '~{' + CHAR(0x00) + '}~';

PRINT '------------------------------------------';
PRINT 'PRINT: ' + @String;
PRINT '------------------------------------------';
GO

That returns:

------------------------------------------
PRINT: ~{}~
------------------------------------------

At least that’s what we see (more on that in a moment).

RAISERROR

DECLARE @String VARCHAR(20) = '~{' + CHAR(0x00) + '}~';

PRINT '------------------------------------------';
RAISERROR('RAISERROR: %s', 16, 1234567, @String);
PRINT '------------------------------------------';
GO

That returns:

------------------------------------------
Msg 50000, Level 16, State 135, Line XXXXX
RAISERROR: ~{
------------------------------------------

Interesting side note: I used a value of "1234567" for the "State" parameter, yet the value in the error message is "135". How did that happen? Well, that value has a range of 0 to 255. Values above 255 just wrap around and start back at 0 (meaning, passing in 256 would return 0). We can use the modulus operator to calculate this:

SELECT 1234567 % 256;
-- 135

Addendum to the side note: it seems that allowing values over 255 started in SQL Server 2014 given that only SQL Server 2012 returned the following error:

Msg 2756, Level 16, State 1, Line XXXXX
Invalid value 1234567 for state. Valid range is from 0 to 255.

THROW

DECLARE @String VARCHAR(20) = 'THROW: ~{' + CHAR(0x00) + '}~';

PRINT '------------------------------------------';
THROW 8675309, @String, 1234568;
PRINT '------------------------------------------';
GO

That returns:

------------------------------------------
Msg 8675309, Level 16, State 136, Line XXXXX
THROW: ~{

To be clear, the bottom line of dashes doesn’t print due to THROW aborting the batch, hence the second PRINT statement never executes.

Side note: the "State" parameter works the same here as it does for RAISERROR (I even increased the initial value by 1 and the resulting "State" value in the error message increased by 1):

SELECT 1234568 % 256;
-- 136

And, just like we saw with RAISERROR, only SQL Server 2012 returns an error (the same error):

Msg 2756, Level 16, State 1, Line XXXXX
Invalid value 1234568 for state. Valid range is from 0 to 255.

 

In Practice

Now that we have seen that this behavior is specific to the PRINT statement, let’s see what we can do with it in a more realistic scenario. If it was only that text after a “null terminator” wasn’t displayed, then I would just file a bug report and move on. But, there’s a more interesting (and potentially more fun) side-effect of this behavior. Even though you can’t see it, the “null terminator” character is actually there, it’s just non-printable (meaning: it doesn’t take up any space, but it’s there). But, that character can’t be copied to the clipboard (or, it can’t be pasted – either way, the net-effect is the same). Hence, placing this character in PRINT statements prevents any text after the character from being copied and pasted.

Block of Text

If you have a block of text (where, if someone was going to copy it, they would start at the top / it wouldn’t make sense to skip anything), then you can simply make CHAR(0) the first character. For example:

PRINT CHAR(0) + '------------------------------------------';
PRINT 'This is a test. Character 0 (Code Point U+0000; 0x00 in hex; not';
PRINT 'the character "0" which is 0x30 in hex) at the beginning of the';
PRINT 'first line above prevents copying any of this text, assuming'
PRINT 'that someone will be selecting all of it (i.e. Control-A).';
PRINT '------------------------------------------';
GO

Selecting everything and hitting Ctrl + C:

will paste as follows (nothing there!):

​

Selecting only some of the text and hitting Ctrl + C:

will paste as follows:

This is a test. Character 0 (Code Point U+0000; 0x00 in hex; not
the character "0" which is 0x30 in hex) at the beginning of the
first line above prevents copying any of this text, assuming
that someone will be selecting all of it (i.e. Control-A)

List of Items / Log Entries

If you have either a list of items or a log of entries for actions or checks performed (where it might make more sense to grab a subset of the lines), then you would need to make CHAR(0) the first character of every line. For example:

DECLARE @Now NVARCHAR(50),
        @Index INT = 0;

WHILE (@Index < 10)
BEGIN
    SET @Now = CONVERT(NVARCHAR(50), GETDATE(), 121);

    PRINT NCHAR(0) + @Now + N': something, something, dark side.';

    WAITFOR DELAY '00:00:00.003';
    SET @Index += 1;
END;

Please note that I changed the variable, function, and string literal to NVARCHAR to show that the behavior is the same as it is for VARCHAR (as shown in the previous examples).

Selecting everything and hitting Ctrl + C:

will paste as follows (nothing there!):

​

Selecting only some of the text and hitting Ctrl + C:

will paste as follows:

20-05-14 21:56:18.470: something, something, dark side.

 

SSMS or SQL Server?

Assuming that this is a bug, where is the bug? Is it in SSMS? Or could it be in SQL Server? SSMS might be able to handle this better, but why is PRINT even returning any characters past CHAR(0) when RAISERROR and THROW don’t? SSMS isn’t the only way that people interact with SQL Server, so to be thorough (and fair) we should at least test against SQLCMD.EXE. SQLCMD is often used in automation, so if someone is capturing output, it would be good to know if and how this behavior affects what is captured.

For those who are less familiar with DOS / batch / CMD scripting, the following three constructs are used in the remaining examples:

• ^ is the line-continuation character. It allows multiple lines to execute as if entered on a single line. I use this to avoid having long lines that would otherwise require horizontal scrolling when posted in a code block.
• && is placed between two commands and causes the second command (the one on the right) to execute only if the first command (the one on the left) completes successfully.
• > captures regular (non-error) output from the command to the left of this character into the file specified to the right of it.

SQLCMD

I tested against the following versions:

• 11.0.2100.60
• 14.0.3026.27 (32-bit)
• 14.0.3257.3
• 15.0.2000.5
• 16.0.4100.1

SQLCMD -W -Q "PRINT 'A~{' + CHAR(0x00) + '}~'; PRINT 'Line 2';" ^
-o _messages_sqlcmd.txt && type _messages_sqlcmd.txt

returns:

A~{
Line 2

Now that’s different. It seems that SQLCMD filters out CHAR(0) and everything after it (per PRINT statement).

What’s different between SSMS and SQLCMD that could possibly account for this difference in behavior? There are three possibilities that I can think of:

1. The language each program is written in (I believe C++ for SQLCMD and C# / .NET for SSMS): C++ would definitely stop at code point U+0000, but I’m not entirely sure (yet — see below) about C#.
2. If not filtered out by the source language, perhaps SQLCMD was written to explicitly to end strings if / when it encounters code point U+0000.
3. The middleware / driver: SQLCMD connects via ODBC while SSMS connects via .NET SqlClient.

We will need to run a couple few more tests in order to narrow down where the issue is. With SQLCMD (most likely) being written in a language that naturally handles the “null terminator”, we have no way of determining if ODBC is passing along the \0 or filtering it out before the C++ code even gets the message. And, without the source code, we can’t see if SQLCMD contains any code that would handle \0 (the presence of such code would imply, though not confirm, that the \0 character is neither filtered out by ODBC nor handled naturally by the source language).

We definitely need to test an ODBC connection outside of C++. And, while we can assume that neither .NET (at least not C#) nor .NET SqlClient filter out code point U+0000, it also wouldn’t hurt to verify that behavior outside of SSMS.

.NET SqlClient

I’ll start out testing the .NET SqlClient because it’s the easiest test to set up given that I already wrote an open source command-line utility, SimpleSqlExec (available on GitHub), that mirrors much of the functionality of SQLCMD but without the dependency on ODBC:

SimpleSqlExec.exe -Q "PRINT 'B~{' + CHAR(0x00) + '}~'; PRINT 'Line 2';" ^
-mf _messages_ssexec.txt && type _messages_ssexec.txt

returns the following:

B~{ }~
Line 2

That output is closer to what we see in SSMS, in that code point U+0000 did not terminate the string, but there is now a space between the curly brackets. Is that really a space or is it still code point U+0000? Opening that file in Notepad++ shows us the following:

B~{␀}~
Line 2

Ah, so code point U+0000 did come through, just like in SSMS.

ODBC and .NET / C#

I don’t have an existing tool to test ODBC with (that I know of), but it’s easy enough to create.

Below you will find the C# code I used to create a simple console app to test an ODBC connection in .NET (instead of C++), as well as .NET in general. If you want to test it but don’t have Visual Studio, you can run it in LINQPad and the result is the same.

The code first prints out a few lines, one of which contains U+0000, to verify the behavior of the language regardless of any database or anything else. Then, it connects to SQL Server via ODBC and prints any messages that it captures.

using System;
using System.Data.Odbc;

class Program
{
    static void Main(string[] args)
    {
        string connectionString =
            "Driver={SQL Server};Server=(local);Trusted_Connection=Yes;";
        string query = 
            "PRINT 'C~{' + CHAR(0x00) + '}~'; PRINT 'Line 2';";

        Console.Out.WriteLine("C# print: TE\u0000ST!");
        Console.Out.WriteLine("Line 2");
        Console.Out.WriteLine("---------------------------------");

        using (OdbcConnection connection =
            new OdbcConnection(connectionString))
        {
            connection.InfoMessage += GetMessages;

            using (OdbcCommand command = connection.CreateCommand())
            {
                command.CommandText = query;
                connection.Open();

                Console.Out.WriteLine(command.ExecuteScalar());

                connection.Close();
            }

        }
    }

    public static void GetMessages(object sender,
        OdbcInfoMessageEventArgs e)
    {
        Console.Out.WriteLine(e.Message);
    }
}

Running the following:

OdbcTest.exe > _messages_odbc.txt && type _messages_odbc.txt

returns:

C# print: TE ST!
Line 2
---------------------------------
[...]Changed database context to 'master'.
[...]Changed language setting to us_english.
[...]C~{
[...]Line 2

Please note that in each of the output lines above, [...] was originally [Microsoft][ODBC SQL Server Driver][SQL Server].

As we can see in the output above, the lines above the line of dashes show that, in general, C# does not filter out code point U+0000 (again it appears to be a space). HOWEVER, we also see that an ODBC connection does filter out code point U+0000 (and everything after it in that particular string).

Opening this output file in Notepad++ (to check what that space between the curly brackets is) shows us the following:

C# print: TE␀ST!
Line 2
---------------------------------

Please note that I did not copy the output below the line of dashes into the listing shown above as it’s not relevant at this point.

Once again we see that the "NUL" character come through as is because C# doesn’t treat it as special.

 

Conclusion

Here is what we have learned:

1. SQL Server, for whatever reason (maybe intentional, maybe a bug), does not stop sending a string for output via the PRINT upon reaching code point U+0000 (i.e. the “null terminator”, \0, CHAR(0)), although it does for SELECT, RAISERROR, and THROW.
2. C# does not stop processing a string when encountering code point U+0000 (i.e. it will pass it along).
3. The .NET SqlClient does not stop processing a string when encountering code point U+0000 (i.e. it will pass it along).
4. ODBC does stop processing a string when encountering code point U+0000 (i.e. it will filter it, and everything after it for that string, out).
5. DOS / a Command Prompt window, will display code point U+0000 as a space (and it will copy and paste as a space).
6. Notepad++ will display code point U+0000 as ␀ (but it will copy and paste as a space).
7. SQLCMD, which uses ODBC, will stop processing a string when encountering code point U+0000.
8. SSMS, which uses the .NET SqlClient, treats code point U+0000 as a non-printable character (i.e. it doesn’t take up any visible space, but it is in the output).
9. In SSMS, when copying text that contains code point U+0000 to the clipboard, only text that comes before code point U+0000 will actually copy to the clipboard. Hence, placing code point U+0000 (i.e. CHAR(0)) at the beginning of a block of text, or at the beginning of each PRINT statement, will effectively prevent that text from being copied from the "Messages" tab.
10. The effect code point U+0000 has when displayed by other programs is specific to each program.

---