Throw FORMATMESSAGE() in the SQL Server 2012 Tool Bag

SQL Server 2012 chucks THROW into Try-Catch block error handling brewery.

THROW is part of the TRY-CATCH contraption. THROW has an ability to reliably relay an exception down the T-SQL call stack. However, the inability of a THROWn message to accept replacement values is a show stopper without FORMATMESSAGE() in the current implementation.

FORMATMESSAGE() will surely see more utilization as THROW catches on.

T-SQL’s FORMATMESSAGE() function is too often overlooked anyway. The script below shows that the Denali CTP3 RC0 RTM FORMATMESSAGE() is a little different beast than we’ve had before. Looks like it gets a lot more like a .NET String.Format() method. For one thing, it lets me stuff CASE and system functions in the parameter list with abandon. (If it would let me pass a column or variable from the local database as the format string it would be even better. I can work around that with nesting, i.e. FORMATMESSAGE() can be used in a parameter of a FORMATMESSAGE()).  More noticeably, it allows me to specify a format string as an alternative to referencing a message_id in sys.messages. Not that there would be anything you could do with the formatting string that you cannot do in sys.messages, just that sys.messages is in master so using messages from the table binds the application to the SQL instance. FORMATMESSAGE() has long been effective way to assure type when you must render disparate types into display column. FORMATMESSAGE() will see more utilization as THROW catches on. Error messaging, e.g. RAISERROR(), is the obvious use case. So much so that it is easy to overlook other great places to benefit from a low level (high performance?) system method:

  • code generation
  • dynamic SQL (almost the same principles as code generation)
  • cryptography – FORMATMESSAGE() can be used in-line as the clear text or authenticator but no implicit conversions like QUOTENAME() or REPLACE()
  • output formatting – columns can be aligned and trimmed for display
  • display formatting (almost the same principles as output formatting) 
  • messaging – a native alternative to XML
  • logging 
  • data cleansing
  • data conversion

Of course the most important usage will continue to be in error handling.

November 24 2011 Update – the T-SQL in this post continues to work with the RC0 bits (11.0.1750.32) but still no update to the documentation confirming any new powers for FORMATMESSAGE().

March 22nd 2012 Update – the RTM bits and local RTM Books Online the situation remains unchanged. That would make it official that any usage as described here does indeed work but is in fact undocumented for SQL Server 2012 RTM. I wonder what’s up with that?

Application stored procedures that experience runtime type and conversion exceptions are good candidates for FORMATMESSAGE() type conformity. My personal favorite example type conversion exception in this moment is:

SELECT CAST(-1234567890 AS NVARCHAR(10) )

which returns only the error message;

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type nvarchar.

Cracks me up! At the same time it is embarrassing to admit how often I have cast INTs to VARCHAR(10)s for formatting into a string. Negative numbers less than -999999999  need 11 characters for display. I have benignly ignored this fact for years. I remain confounded about how that is an arithmetic overflow though.

The same problem using FORMATMESSAGE() instead of  the mushy SQL Server native type system is no problem at all.

SELECT FORMATMESSAGE('%d',-1234567890);

and the result is -1234567890 of type NVARCHAR.

FORMATMESSAGE() also adds protection against the escape sequence or truncation attack vectors that aim to execute malicious T-SQL.

Is This Really FORMATMESSAGE()?

The documentation is very clear that FORMATMESSAGE works only with messages stored in sys.messages. The Denali documentation is not changed from the SQL Server 2008 documentation – so far. Nonetheless, the example successfully uses a message string instead of a message_id even though it is not documented to do so:

FORMATMESSAGE ( msg_number , [ param_value [ ,...n ] ] )

Notice that there is no option for text instead of a msg_number. Denali CTP3 RC0 Books OnLine even adds a special Note, “FORMATMESSAGE works only with messages created using sp_addmessage,” yet here I am with the CTP3 able to use a message instead. I guess that says things could change by RTM. On a SQL Server 2008 instance The script below will fail with the message

Msg 8116, Level 16, State 1, Line 17
Argument data type varchar is invalid for argument 1 of formatmessage function.

As mentioned earlier, SQL Server 2012 introduces THROW for use in Try-Catch blocks. A THROW statement or a system generated error in the TRY block transfers control to the CATCH BLOCK.  That’s about like RAISERROR. A THROW in the CATCH block throws the error down the call stack. Again no so difficult to get that behavior out of RAISERROR. The real difference is most obvious when an unexpected exception occurs in the CATCH block. A RAISERROR implementation is likely to throw that error on the floor while a THROW implementation will abort the batch. FORMATMESSAGE() may get more attention as THROW catches on, especially given the more elegant, i.e. less prone to failure and exploitation usage profile when unexpected data is encountered.

THROW does not support the printf formatting available with RAISERROR at all. Denali Books-On-Line – the web version – shows an example using the FORMATMESSAGE() function to format a message_id from sys.messages before passing it to the THROW.

FORMATMESSAGE(), at least in the guise of RAISERROR() has been in the database engine throughout the evolution of SQL Server. In SQL Server 2005/2008/2012 FORMATMESSAGE offers some help to ‘load’ up to 20 values of disparate type into an NVARCHAR. It is necessary to specify the type of the inputs using a subset of the printf formatting codes of the standard C language. (read all about printf in your favorite C header – hint: search local drives for a file containing the text “#define printf”).

Binary data remains more difficult to work with. More examples in the documentation would probably be helpful.

As an example, here is a FORMATMESSAGE testing script that uses FORMATMESSAGE to ‘dog food’ FORMATMESSAGE statements for each of the 42 combinations of printf flag and type supported by SQL Server.  Specify a value to match the data you supply, the @Type of that data and the desired @Format of the output string format:

-- printf formatting  %[[flag][width][.precision]]type
DECLARE @Format CHAR(1)         = ''   -- '',+,-,0,#,' '
      , @Width INT              = 6    -- display field size
      , @Precision INT          = 3    -- characters to display
      , @Type CHAR(1)           = 'X'  -- s, d, i, u, o, x, X
      , @Binary VARBINARY(128)  = 0x1239 
      , @Int INT                = 11                
      , @String NVARCHAR(128)   = N'test';  
DECLARE @$QL NVARCHAR(256) = ( SELECT FORMATMESSAGE( 
N'SELECT FORMATMESSAGE(''%%s -->%%%s*.*%s<--'', @$QL, %i, %i, %s)'
                      , s.Flag 
                      , f.Type
                      , @Width
                      , @Precision 
                      , CASE WHEN @Type = 's' 
                             THEN FORMATMESSAGE('''%s''', @String)
                             WHEN @Type = 'd'
                             THEN FORMATMESSAGE('%d', @Int)
                             WHEN @Type = 'i'
                             THEN FORMATMESSAGE('%i', @Int)
                             WHEN @Type = 'u'
                             THEN FORMATMESSAGE('%u', @Int)
                             WHEN @Type = 'o'
                             THEN FORMATMESSAGE('%o', @Int)
                             WHEN @Type = 'x'
                             THEN FORMATMESSAGE('%x', @Binary)
                             WHEN @Type = 'X'
                             THEN FORMATMESSAGE('%X', @Binary)
                             END )
   FROM (SELECT           ('') AS Flag
         UNION ALL SELECT ('+') 
         UNION ALL SELECT ('-') 
         UNION ALL SELECT ('0') 
         UNION ALL SELECT ('#') 
         UNION ALL SELECT (' ') ) AS s      
   CROSS JOIN (SELECT           ('s') AS Type
               UNION ALL SELECT ('d')
               UNION ALL SELECT ('i')
               UNION ALL SELECT ('u')
               UNION ALL SELECT ('o')
               UNION ALL SELECT (LOWER('x'))
               UNION ALL SELECT (UPPER('X')) ) as f
   WHERE CHARINDEX(@Format,s.Flag) > 0
   AND ASCII(f.Type) = ASCII(@Type) );
EXEC sp_executesql @$QL, N'@$QL NVARCHAR(256)', @$QL;

You get the statement generated and the result as output. A few of samples:

  • SELECT FORMATMESSAGE(‘%s –>% *.*X<–‘, @$QL, 6, 3, 1239)   –>   4D7<–
  • SELECT FORMATMESSAGE(‘%s –>%#*.*x<–‘, @$QL, 10, 6, 1239)  –>  0x0004d7<–
  • SELECT FORMATMESSAGE(‘%s –>%-*.*o<–‘, @$QL, 6, 3, 13)     –>015   <–
  • SELECT FORMATMESSAGE(‘%s –>%0*.*d<–‘, @$QL, 10, 6, 11)    –>    000011<–
  • SELECT FORMATMESSAGE(‘%s –>% *.*d<–‘, @$QL, 3, 6, 11)     –> 000011<–
  • SELECT FORMATMESSAGE(‘%s –>%+*.*i<–‘, @$QL, 4, 7, 11)     –>+0000011<–
  • SELECT FORMATMESSAGE(‘%s –>%+*.*s<–‘, @$QL, 6, 3, ‘test’) –>   tes<–

You can paste the result into a query window, change @$QL to blank or ‘test’ and try similar scenarios to fine tune or develop into more complex FORMATMESSAGE statements.

Too bad the script doesn’t work in any of the SQL Server 2008 or prior flavors…

Here is a script that generates a BACKUP CERTIFICATE statement. The point would be to store passwords and other sensitive values as ciphered-text yet be able to use them when needed, e.g. you’d want to be able to recall the passwords in the event a forward recovery from a corrupted certificate is needed.

--------------------------------------------------------------------------
-- Using FORMATMESSAGE to generate BACKUP CERTIFICATE STATEMENT
--------------------------------------------------------------------------
-- user defined table type for encrypted name/value pair
IF TYPE_ID('NAMEVALUETYPE') IS NULL
  CREATE TYPE NAMEVALUETYPE AS TABLE
      ( Name VARBINARY(8000) NOT NULL
      , Value VARBINARY(8000) NOT NULL );
GO
--temp key available to session only until session is closed
--not useful for persisted data unless source,identity & phrase persisted
IF KEY_GUID('#TestKey') is NULL
  CREATE SYMMETRIC KEY #TestKey 
  WITH ALGORITHM = AES_256 
     , KEY_SOURCE = 'testing 1,2,3'
     , IDENTITY_VALUE = 'This is only a test'
  ENCRYPTION BY PASSWORD = 'test';   
OPEN SYMMETRIC KEY #TestKey DECRYPTION BY PASSWORD = 'test';
GO
-- setup the required inputs
DECLARE @CertificateName NVARCHAR(128) = 'CertificateName'
      , @CipherType NCHAR(2) = 'MK' -- 'NA' (EKM), 'MK' , 'PW'
      , @DbName NVARCHAR(128) = 'DbName'
      , @BackupName VARBINARY(8000) = ENCRYPTBYKEY(key_guid('#TestKey')
                                                      , N'BackupName')     
      , @DMKPhraseName VARBINARY(8000) = ENCRYPTBYKEY(key_guid('#TestKey')
                                                      , N'DMKPhraseName')
      , @KeyPhraseName VARBINARY(8000) = ENCRYPTBYKEY(key_guid('#TestKey')
                                                      , N'KeyPhraseName')
      , @PublicKeyFileExt NVARCHAR(10)  = '.cer'
      , @PrivateKeyFileExt NVARCHAR(10) = '.prv'
      , @UseHash BIT                    = 1
      , @BackupNameBucket INT           
      , @BackupPath VARBINARY(8000)
      , @Backuptvp NAMEVALUETYPE
      , @DMKtvp NAMEVALUETYPE
      , @Keytvp NAMEVALUETYPE;
-- checksum truncation of the random lambda
SET @BackupNameBucket = ABS(CHECKSUM(HASHBYTES('SHA2_256'
    , RIGHT(CAST(DECRYPTBYKEY(@BackupName) AS NVARCHAR(448))
    , FLOOR(LEN(CAST(DECRYPTBYKEY(@BackupName) AS NVARCHAR(448)))/2)))));   
SET @BackupPath = ENCRYPTBYKEY( key_guid('#TestKey')
                              , N'Z:\BackupPath\', 1, @DbName); 
INSERT @Backuptvp (Name, Value) 
VALUES ( @BackupName    
       , ENCRYPTBYKEY( key_guid('#TestKey'), N'@Backuptvp.Value', 1
                     , CAST(DECRYPTBYKEY(@BackupName) AS NVARCHAR(448))));
IF @DMKPhraseName IS NOT NULL
  INSERT @DMKtvp (Name, Value) 
  VALUES ( @DMKPhraseName 
         , ENCRYPTBYKEY( key_guid('#TestKey'), N'@DMKtvp.Value', 1
                 , CAST(DECRYPTBYKEY(@DMKPhraseName) AS NVARCHAR(448))));
IF @KeyPhraseName IS NOT NULL AND @CipherType = 'PW'
  INSERT @Keytvp (Name, Value) 
  VALUES ( @KeyPhraseName 
         , ENCRYPTBYKEY( key_guid('#TestKey'), N'@Keytvp.Value', 1
                 , CAST(DECRYPTBYKEY(@KeyPhraseName) AS NVARCHAR(448))));
--------------------------------------------------------------------------
-- decryption with FORMATMESSAGE 
SELECT FORMATMESSAGE 
        ( 'USE %s;%sBACKUP CERTIFICATE TO FILE = ''%s%s%s'' %s;%s'
        , @DbName 
        , CASE WHEN @DMKPhraseName IS NOT NULL -- need to open master key
              THEN (SELECT FORMATMESSAGE 
                      ( 'OPEN MASTER KEY DECRYPTION BY PASSWORD = ''%s'';'
                      , CAST( DECRYPTBYKEY( Value
                          , 1
                          , CAST ( DECRYPTBYKEY( Name ) AS NVARCHAR(448) )
                          ) AS NVARCHAR(128) ) )
                    FROM @DMKtvp ) 
              ELSE '' END
        , CAST(DECRYPTBYKEY( @BackupPath, 1, @DbName ) AS NVARCHAR(1024) )  
        , CASE WHEN @UseHash = 1 
                THEN CAST( @BackupNameBucket AS NVARCHAR(448) )
                ELSE CAST( DecryptByKey( @BackupName ) AS NVARCHAR(448) ) 
                END 
      , @PublicKeyFileExt
      , CASE WHEN @CipherType <> 'NA'  
             THEN 
FORMATMESSAGE  
('WITH PRIVATE KEY ( FILE=''%s%s%s'', ENCRYPTION BY PASSWORD=''%s'' %s)'
      , CAST(DecryptByKey(@BackupPath, 1, @DbName ) AS NVARCHAR(1024)) 
      , CASE WHEN @UseHash = 1 
              THEN CAST( @BackupNameBucket AS NVARCHAR(448) )
              ELSE CAST( DecryptByKey( @BackupName ) AS NVARCHAR(448) ) 
              END 
      , @PrivateKeyFileExt
      , ( SELECT CAST( DECRYPTBYKEY( Value
                           , 1
                           , CAST ( DECRYPTBYKEY( Name ) AS NVARCHAR(448) )
                           ) AS NVARCHAR(128) )
          FROM @BackupTvp )
      , CASE WHEN @CipherType = 'PW'    
              THEN (SELECT FORMATMESSAGE 
                            ( ', DECRYPTION BY PASSWORD = ''%s'''
                            , CAST( DECRYPTBYKEY( Value
                                       , 1
                                       , CAST ( DECRYPTBYKEY( Name ) 
                                                  AS NVARCHAR(448) ) 
                                       ) AS NVARCHAR(128) ) )  
                    FROM @KeyTvp )
              ELSE '' END )
            ELSE '' END

        , CASE WHEN @DMKPhraseName IS NOT NULL -- open master key
                THEN 'CLOSE MASTER KEY;'
                ELSE '' END );
GO

Some sample output for the three different @CipherType values  (after pasting from the results window to the query window and adding a few CR-LFs):

-- @CipherType = 'NA', @DMKPhraseName = NULL, @UseHash = 0
USE DbName;
BACKUP CERTIFICATE TO FILE = 'Z:\BackupPath\BackupName.cer' ;

-- @CipherType = 'PW', @DMKPhraseName = NULL, @UseHash = 1
USE DbName;
BACKUP CERTIFICATE TO FILE = 'Z:\BackupPath\116889000.cer' 
WITH PRIVATE KEY ( FILE='Z:\BackupPath\116889000.prv'
                 , ENCRYPTION BY PASSWORD='@Backuptvp.Value' );

-- @CipherType = 'MK', @DMKPhraseName = 'phrase', @UseHash = 1
USE DbName;
OPEN MASTER KEY DECRYPTION BY PASSWORD = '@DMKtvp.Value';
BACKUP CERTIFICATE TO FILE = 'Z:\BackupPath\116889000.cer' 
WITH PRIVATE KEY ( FILE='Z:\BackupPath\116889000.prv'
                 , ENCRYPTION BY PASSWORD='@Backuptvp.Value' );
CLOSE MASTER KEY;

Check my EHAdmin project on github (https://github.com/bwunder/EHAdmin) for a more detailed study of THROW (see “tryin try-catch.sql”) .  As far as I can tell FORMATMESSAGE is much bigger news for the short term than THROW. And that Microsoft does not seem to have any interest in supporting the useful new functionality described even takes some gloss off of FORMATMESSAGE – at least for me.

Advertisements
This entry was posted in Code Review. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s