Friday, October 7, 2011

Loopty-Loop

A recent encounter with my arch-enemy, The Tangled Loop, left me weary and scarred, I haven't yet fully recovered.  During the battle, I witnessed unspeakable things, lines of code that mangled T-SQL into something unrecognizable.  I feel guilty exposing you all to this, but it must be done, for you must know what to be on the lookout for.  The Tangled Loop is still out there, somewhere, he may surface in your workplace next.  What you're about to see is real, actual code that a developer wanted to deploy to our production server.

The code below, and I use the term "code" loosely, is intended to loop (yes, that's right, loop) through a table, and for certain rows, replace certain string combinations with something else.  Go ahead, look at it.  I'll give you moment to stop vomiting and catch your breath.

-------------------------------------
-- declare variables
-------------------------------------
DECLARE @conversion TABLE (
  fromvalue VARCHAR(50),
  tovalue   VARCHAR(50))
DECLARE @rowsToUpdate TABLE (
  idtoupdate    INT,
  paramtoupdate VARCHAR(255))
DECLARE @paramName VARCHAR(20)
DECLARE @ruleID INT
DECLARE @ruleParam VARCHAR(255)
DECLARE @delim CHAR
DECLARE @delimIndex INT
DECLARE @delimWork VARCHAR(255)
DECLARE @oldValue VARCHAR(50)
DECLARE @newValue VARCHAR(50)
DECLARE @newValues VARCHAR(255)

---------------------------------------
---- Initialize variables
---------------------------------------
INSERT INTO @conversion
VALUES      ('E',
             'EMAIL')

INSERT INTO @conversion
VALUES      ('S',
             'SMS')

INSERT INTO @conversion
VALUES      ('X',
             'DO_NOT_Send')

INSERT INTO @conversion
VALUES      ('EMAIL',
             'EMAIL')

INSERT INTO @conversion
VALUES      ('SMS',
             'SMS')

INSERT INTO @conversion
VALUES      ('DO_NOT_Send',
             'DO_NOT_Send')

SET @delim = ','
SET @paramName = 'CP='

-------------------------------------
-- Procedure body
-------------------------------------
-- find all rows to process
INSERT INTO @rowsToUpdate
SELECT correspondencetemplateconfigurationselectionruleid,
       ruleparam
FROM   dbo.correspondencetemplateconfigurationselectionrule
WHERE  ruleparam LIKE @paramName + '%'

-- loop through the rows to process
SET rowcount 1

SELECT @ruleID = idtoupdate,
       @ruleParam = paramtoupdate
FROM   @rowsToUpdate

WHILE @@ROWCOUNT <> 0
  BEGIN
      PRINT N'Change: ' + CAST(@ruleParam AS VARCHAR(255))

      -- The rule parameter is like: CP=a,b,c  First we pull out the value portion (a,b,c)
      SET @delimWork = Substring(@ruleParam, Len(@paramName) + 1,
                       Len(@ruleParam))
      SET @newValues = @paramName

      -- loop through value list by delimiter (ex:  if values are a,b,c then we process a then b then c)
      SELECT @delimIndex = Charindex(@delim, @delimWork, 0)

      WHILE @delimIndex > 0
        BEGIN
            SELECT @oldValue = Ltrim(Rtrim(Substring(@delimWork, 0, @delimIndex)
                                     ))

            -- convert the old value to new new value
            SET @newValue = NULL

            SELECT @newValue = tovalue
            FROM   @conversion
            WHERE  fromvalue = @oldValue

            IF ( @newValue IS NOT NULL )
              BEGIN
                  -- save new value to new list of values
                  SET @newValues = @newValues + Ltrim(Rtrim(@newValue)) + @delim
              END

            -- setup for the next loop of delimited values
            SELECT @delimWork = Substring(@delimWork, @delimIndex + 1, Len(
                                @delimWork)
                                )

            SELECT @delimIndex = Charindex(@delim, @delimWork, 0)
        END

      -- need to pull the last item out of the value list
      IF Len(@delimWork) > 0
        BEGIN
            SET @oldValue = @delimWork
            -- convert the old value to new new value
            SET @newValue = NULL

            SELECT @newValue = tovalue
            FROM   @conversion
            WHERE  fromvalue = @oldValue

            IF ( @newValue IS NOT NULL )
              BEGIN
                  -- save new value to new list of values
                  SET @newValues = @newValues + Ltrim(Rtrim(@newValue))
              END
        END

      PRINT 'To: ' + @newValues

      PRINT '------------------------------------------'

      -- Update the table
      UPDATE dbo.correspondencetemplateconfigurationselectionrule
      SET    ruleparam = @newValues
      WHERE  correspondencetemplateconfigurationselectionruleid = @ruleID

      -- setup next loop of rows to update
      DELETE FROM @rowsToUpdate
      WHERE  idtoupdate = @ruleID

      SELECT @ruleID = idtoupdate,
             @ruleParam = paramtoupdate
      FROM   @rowsToUpdate
  END

SET rowcount 0 


We all know looping is not preferred within T-SQL, but this example takes it to the extreme.  Not only does it loop through the rows of a table, but when doing the string replacements, it loops through the strings, ONE CHARACTER AT A TIME!

When The Tangled Loop launched this attack, I grabbed the nearest T-SQL reference book to use as a shield.  This brief respite allowed me the time to muster up a counter-attack:

UPDATE dbo.correspondenceconfiguration
SET    param = 'CP=' + REPLACE((SELECT map.newvalue AS [data()]
                                FROM   (SELECT '|E|'   AS oldvalue,
                                               'EMAIL' AS newvalue
                                        UNION ALL
                                        SELECT '|S|' AS oldvalue,
                                               'SMS' AS newvalue
                                        UNION ALL
                                        SELECT '|X|'         AS oldvalue,
                                               'DO_NOT_Send' AS newvalue) AS map
                                WHERE  REPLACE(REPLACE(
                                       correspondenceconfiguration.param + '|',
                                               '=',
                                               '=|'), ','
                                       , '|,|')
                                       LIKE '%' + map.oldvalue + '%'
                                FOR XML PATH('')), ' ', ',')
FROM   dbo.correspondenceconfiguration
WHERE  param LIKE 'CP=%'


The server was saved from a terrible fate, but unfortunately, The Tangled Loop escaped, and is still out there, somewhere.  I'm sure we haven't seen the last of him.

0 comments:

Post a Comment