SSMS and SQLCMD: Prevent T-SQL Batch From Not Only Executing, but Also From Parsing (Cruel Joke #3)

(last updated: 2021-08-13 @ 19:50 ET / 2021-08-13 @ 23:50 UTC )

In a previous post, Prevent Full Script Execution (Understanding and Using PARSEONLY and NOEXEC), I explained how to easily disable a script using the PARSEONLY session setting. That same method can be used to disable one or more sections within a script instead of the entire script. But in either case, “disabling” doesn’t mean that the script, or section of code, will be skipped entirely as if it wasn’t there. It will still be parsed by SQL Server as that is not something that can be turned off. This means that you could still see parsing errors related to undeclared variables, syntax errors, etc.

Then one day I tried something silly that I figured wouldn’t work but wanted to try anyway (because quite often you don’t know until you know), and it actually did work (for the most part). I found a way to fully disable an entire T-SQL batch, and there really isn’t any indication that it happened. However, this “technique” is more limited than PARSEONLY as it only works on individual batches, and it only works in some environments.

The Thing That Does Nothing

What is this mysterious “technique”, you ask? Well, it’s none other than everyone’s favorite batch separator: GO. Yes, you read that correctly, GO. Anyone who works with SQL Server and has submitted queries via client tools such as SQL Server Management Studio (SSMS), sqlcmd, and others has used GO to separate batches of query statements. Some people put GO between most statements and that really isn’t necessary. There are some statements that require being the only statement in a batch, such as CREATE PROCEDURE and CREATE VIEW, in which case GO will be used (assuming that you will be running the whole script in one execution and not merely highlighting each section to execute individually, in which case GO isn’t needed).

The Official Word

Let’s take a look at the documentation to see what the behavior is supposed to be:

SQL Server Utilities Statements – GO

There are two things in particular that are of importance: syntax and where applicable.

Syntax

A positive integer, eh? I guess we can rule out "-1", but what about "0"? While 0 is typically considered neither positive nor negative, there are some cases — some philosophical (e.g. Is zero positive or negative? ), some due to forgetting to check the minimum value of user input — when it’s a little more ambiguous. So, what would happen if we asked the client to send the T-SQL batch to SQL Server, well, never? It seems a little silly, but that’s hardly a reason to not try something, right?

Where Applicable

Apparently that list has not been updated to include: Visual Studio, Visual Studio Code, and Azure Data Studio.

GO is also recognized by many 3rd party utilities, including an open source, lightweight ADO.NET-based (i.e. not ODBC) replacement for sqlcmd that I wrote: Simple SQL Exec.

Why does this list of SQL Server clients matter? Well, due to GO being processed by the clients instead of SQL Server, there is the possibility of there being different behavior between the various clients.

Tests

Review

First let’s review what PARSEONLY ON does.

PARSEONLY is a session setting that is processed at parse time, prior to compilation (and execution, of course). This means that the location of the SET PARSEONLY statement within the batch is irrelevant since this setting controls whether or not to proceed to the next step / phase of the execution process. It can be at the beginning, middle, or end, and it doesn’t matter: the batch is being parsed no matter what.

In the following example, there is no output because PARSEONLY is set to ON within the batch:

-- No output:
GO
SET NOCOUNT, PARSEONLY OFF;
GO
SELECT 1;
SET PARSEONLY ON;
SELECT 2;
GO
SET PARSEONLY OFF;
GO

If there are parsing errors (syntax errors, undeclared variables, etc), those will still be reported, even if processing ends at the completion of this phase due to PARSEONLY being set to ON. For example:

-- Parse error:
GO
SET NOCOUNT, PARSEONLY OFF;
GO
SELECT 1;
SET PARSEONLY ON;
SELECT @UndeclaredVariable;
GO
SELECT 1/0; -- execution/runtime error
SELECT NoSuchColumn FROM sys.objects; -- compilation error
GO
SET PARSEONLY OFF;
GO
/*
Msg 137, Level 15, State 2, Line XXXXX
Must declare the scalar variable "@UndeclaredVariable".
*/

There are no runtime errors as the code is not being executed. There aren’t even any compilation errors as the code isn’t being compiled either. But, parsing errors cannot be avoided as parsing cannot be disabled.

Or, can it?

But first, let’s confirm that batch separators are handled by client tools and not SQL Server by placing the typical one in dynamic SQL:

-- SQL Server error:
EXEC (N'
SELECT 1;
GO
SELECT 2;
');
/*
Msg 102, Level 15, State 1, Line XXXXX
Incorrect syntax near 'GO'.
*/

The Main Event

Now we finally get to play with batch repetition. If you haven’t tried this before, adding a positive integer after the batch separator will send the preceding batch of statements to SQL Server that many times:

PRINT 'Candyman.';
GO 5

returns the following¹ in the "Messages" tab:

Beginning execution loop
Candyman.
Candyman.
Candyman.
Candyman.
Candyman.
Batch execution completed 5 times.

Now, let’s execute the same query batch that we tried a moment ago that received a parse error due to the undeclared variable, but this time we will add a "0" to the GO :

-- No output, but SSMS error:
GO
SET NOCOUNT, PARSEONLY OFF;
GO
SELECT 1;
SET PARSEONLY ON;
SELECT @UndeclaredVariable;
GO 0
SET PARSEONLY OFF;
GO
/*
(no "Results" tab; "Messages" tab is empty)
("Query completed with errors." in bottom status bar of SSMS)
*/

This time there’s no parse error because the batch of statements was never sent to SQL Server for processing. The client skipped that set of statements and moved on to the next batch of statements. To further illustrate this point, the following example shows text that should definitely return errors, even if PARSEONLY is enabled, yet does not error:

-- No output, but SSMS error (no indication of error in SQLCMD):
GO
SET NOCOUNT, PARSEONLY OFF;
GO
This should definitely produce an error, right?
1) "PARSEONLY" has not been set to ON.
2) More importantly, this is not even remotely close to 
   being valid T-SQL.
GO 0
/*
(no "Results" tab; "Messages" tab is empty)
("Query completed with errors." in bottom status bar of SSMS)
*/

And, if you either execute those statements manually (i.e. interactive mode) in sqlcmd or copy them to a SQL script and use that as an input file to sqlcmd, not only will any batch followed by GO 0 be skipped, but there won’t be any indication of an error.

"C:Program FilesMicrosoft SQL ServerClient SDKODBC170ToolsBinnSQLCMD.EXE" -E -i VoidBatchInSQLCMD-InvalidTSQL.sql

Another way of proving that parsing is not happening is to use the session setting that we saw at the beginning, PARSEONLY, which we know is processed during parsing:

-- If GO 0 prevents parsing, then PARSEONLY will also be prevented
GO
SET NOCOUNT, PARSEONLY OFF;
GO
SELECT 1; -- this will execute
GO 
SET PARSEONLY ON; -- This will NOT execute...
GO 0              -- because this line skips the batch
SELECT 2; -- this will execute because SET PARSEONLY ON did not
GO

The query above returns a result set for both SELECT statements (instead of just the first one) because the SET PARSEONLY ON statement is never sent to SQL Server.

Just to be Sure

Even though it’s highly unlikely that GO -1 would work, the simple fact that GO 0 does work proves that we really shouldn’t rely on assumptions.

GO
SELECT 3;
GO -1
/*
A fatal scripting error occurred.
Incorrect syntax was encountered while parsing GO.
*/

Ok. So, at least we have confirmed our suspicions. It’s always better to be certain, and to have actual proof.

Conclusion

The batch separator, typically GO, tells the client tool to send all of the preceding statements to SQL Server. Adding a positive integer after the batch separator should cause the client tool to send the batch that number of times to SQL Server (there’s no guarantee that any particular client tool will support this, but most should). The batch separator (and hence the optional repetition) is functionality provided entirely by the client. SQL Server neither sees the batch separator, nor does it handle any repetition. In fact, attempting to use a batch separator in dynamic SQL will result in an error.

As we have seen, using GO 0 will completely skip the preceding batch (it won’t even be parsed because it isn’t sent to SQL Server), and there won’t even be a runtime error (so it can’t be trapped or detected). This behavior has nothing to do with SQL Server itself (only clients), and does not appear to be intentional. Most likely this is just due to an oversight that was never tested because who would do this naturally (outside of someone like me trying to see what they can get away with)?

While this “technique” can certainly be used to make enemies at work, it’s also possible to use GO 0 in place of block comments (i.e. /* ... */ ). Since the text preceding GO 0, up to the prior GO or the beginning of the script (or selection if you are executing highlighted code), is skipped, you can have large comments that don’t even get sent to the server, unlike block comments. HOWEVER, this is not recommended due to a) differing behavior between clients, and b) the possibility that this behavior might get fixed at some point (though maybe not likely given that it’s been around for 16 years and more impactful bugs aren’t getting fixed).

To GO or Not To GO, That is a Configuration

To be fair, GO is the batch separator by default and thus by convention. It is possible to configure most client tools to use a different batch separator — in SSMS go to the "Tools" -> "Options" -> "Query Execution" -> "Batch separator", and for sqlcmd use the -c switch — but this option is probably seldomly used (I’ve never used it, nor seen anyone else use it, in my 20 years of working with SQL Server; and now it doesn’t seem to work in the most recent version of sqlcmd, reported here: SQLCMD 2019 not accepting different batch separator).

So, this “technique” is really about the behavior of the batch separator (and only in some client tools) and not specifically about GO. Meaning, if you were to configure your SSMS and/or sqlcmd to use w00t as the batch separator, then w00t 0 would cause the batch to never execute.

Behavior by Client

I have tested with the following clients:

• SQL Server Management Studio (SSMS): 18.9.2
• Azure Data Studio (ADS): 1.31.1
• sqlcmd utility: 11.0.2100.60 NT x64 and 15.0.4083.2 NT
• osql utility: 14.0 NT and 15.0 NT (came with SQL Server 2017 and 2019, respectively)
• Visual Studio: 2019 (16.10.4)
• Visual Studio Code: 1.58.2
• Older stuff
  • Query Analyzer: 8.00.194 (came with SQL Server 2000)
  • isql utility: 8.00.194 (came with SQL Server 2000)
  • Microsoft SQL Server Management Studio Express: 9.00.4035.00 (came with SQL Server 2005)
  • Visual Studio: 2015

Across all of those clients, there are only three distinct behaviors (not including “not supported”), and two of those are quite similar. The following list shows which behaviors can be found in each of those clients:

• Batched is skipped
  • No indication of any error at all
    • sqlcmd utility
  • Generic error message is shown² but execution is not stopped
    • SQL Server Management Studio (SSMS)
    • Visual Studio
• Batch is not skipped (executed once; same as GO or GO 1 )
  • isql utility
  • osql utility
  • Azure Data Studio
  • Visual Studio Code
• N/A (batch iteration is not supported)
  • Query Analyzer