Dynamic SQL, NULL, and EXEC

2018-10-01 7 Comments

Dave Mason - SQL Server - TSQL - EXEC

I've been a fan of dynamic T-SQL for a long time. I ran into something new, though, that threw me for a loop. It started with a stored procedure that builds a dynamic query string, based on a dozen or so input parameters that are all optional. If an input parameter is NULL, it gets excluded from the query logic. Here's a typical example with three parameters:

CREATE PROCEDURE dbo.GetSomeData
 @Param1 INT,
 @Param2 BIGINT,
 @Param3 UNIQUEIDENTIFIER
AS
DECLARE @QUERY VARCHAR(8000);

SET @QUERY = 'SELECT blah, blah, blah' + CHAR(13) + CHAR(10) +
 'FROM TableX AS x '  + CHAR(13) + CHAR(10) +
 'JOIN TableY as y ON x.Xid = y.Xid '  + CHAR(13) + CHAR(10) +
 'WHERE 1 = 1 '  + CHAR(13) + CHAR(10)

IF @Param1 IS NOT NULL
BEGIN
 SET @Query = @Query + 'AND y.Param1 = ' + CAST(@Param1 AS VARCHAR(8000)) + CHAR(13) + CHAR(10);
END

IF @Param2 IS NOT NULL
BEGIN
 SET @Query = @Query + 'AND y.Param2 = ' + CAST(@Param2 AS VARCHAR(8000)) + CHAR(13) + CHAR(10);
END

IF @Param3 IS NOT NULL
BEGIN
 SET @Query = @Query + 'AND y.Param3 = ' + CAST(@Param3 AS VARCHAR(8000)) + CHAR(13) + CHAR(10);
END

EXEC (@QUERY);

As you may know, you have to take care when building the @QUERY string. If a single NULL value gets introduced into the string, the entire string becomes NULL and the EXEC() function returns nothing, much like this:

DECLARE @Name VARCHAR(32) = NULL;
DECLARE @QUERY VARCHAR(8000) = 'SELECT 1 AS Foo, 2 AS Bar, ' + @Name + ' AS [Name];';

EXEC (@QUERY);
GO


Commands completed successfully.

The variation I recently encountered was just slightly different. There were separate variables for @SELECT, @FROM, and @WHERE. All three variables were concatenated and passed to the EXEC() function. Here's an example that uses two variables for simplicity:

DECLARE @SELECT VARCHAR(8000) = 'SELECT 1 AS Foo, 2 AS Bar';
DECLARE @WHERE VARCHAR(8000) = ' WHERE 1 = 1';

EXEC (@SELECT + @WHERE);
GO

Foo         Bar
----------- -----------
1           2

(1 row affected)

So far, so good. Now for the twist. If one of the concatenated variables passed to EXEC() is NULL, it is ignored:

DECLARE @SELECT VARCHAR(8000) = 'SELECT 1 AS Foo, 2 AS Bar';
DECLARE @WHERE VARCHAR(8000) = NULL;

EXEC (@SELECT + @WHERE);
GO

Foo         Bar
----------- -----------
1           2

(1 row affected)

In my opinion, the above shouldn't have worked. I can't explain it. This is the type of edge case mystery that might normally consume hours or days to solve. Thankfully, a debugging session helped me understand what was happening without wasting too much time.

In conclusion, I want to point out a few things that might be obvious to you. Dynamic T-SQL is difficult (duh!). Be careful with both NULLs and the EXEC() function. Lastly, and most importantly, the examples presented here are vulnerable to SQL injection attacks. Best practice is to parameterize queries with sp_executesql.

7 comments:

  1. First of all: you're evil. But you know that ;)

    Secondly: what did you discover from the debugger? I've played around with this and the only thing I've learned is that the NULL variable doesn't get converted to whitespace.

    Thanks for the post, though; as much as I shun dynamic SQL I'm always looking for gotchas with NULLs. Every time we think we've explicitly nailed three-valued logic and its edge cases (INTERSECT/IIF etc…) something like this happens :p

    ReplyDelete
  2. Hi, did you check the setting of SET_CONCAT_NULL_YIELDS_NULL in your database?
    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-concat-null-yields-null-transact-sql?view=sql-server-2017
    Best wishes, Ireneus

    ReplyDelete
    Replies
    1. Hi Ireneus. CONCAT_NULL_YIELDS_NULL was 1 (ON). I can duplicate the example in this post regardless of whether the setting is ON or OFF.

      Great question. Thanks for dropping by!

      Delete
  3. I don't get what you are trying to achieve here.
    NULLs are killing strings. Yes. We know that.
    EXEC @Query is vulnerable to injection attacks. Yes. We know that. That's why it hasn't been good practice for a decade.
    sp_executesql isn't breaking because a parameter is NULL. Big surprise! Why should it? That's the whole point of making a function: To improve a situation as compared to using native language items.

    The only interesting thing here is what you omit: How did you go about your "debugging session"? There I might have learned a thing or two.

    ReplyDelete
    Replies
    1. My goal was to share a "gotcha" I encountered in the wild. Was that not clear?

      Delete
  4. In your initial example, your approach for building the filtering conditions leaves the potential for SQL injection attacks. Look into the proper means of parameterizing dynamic SQL by using sp_executeSQL instead.

    ReplyDelete