Strange SQL Query

Question

I came across this bit of tsql in some code I inherited:

DECLARE @a NVARCHAR(1000) ,
    @b NVARCHAR(4)
SET @b = ( SELECT   RIGHT(CAST(SQL_VARIANT_PROPERTY('#', 'basetype') AS VARCHAR(7)),
                          4)
         )
SET @a = SUBSTRING(object_definition(OBJECT_ID('sys.sp_columns')), 399, 6)
    + ' ' + @b + '(' + REPLACE(REPLACE(REPLACE('^523101239923^42310123^623^5',
                                               '^', '11'), '3',
                                       ' + ' + @b + '('), '2', ')')
    + ') '' '''
EXEC sp_executesql @a 

It outputs the result "secrets" to the query window (as a joke maybe?). I have no idea why this code was made or who made it. Can somebody attempt to explain to me how it works? Does it have any use or can I rip it out?

Answer 1

Just breaking it down for you a little,

select sql_variant_property('#','basetype') = 'varchar'
select right('varchar',4) = 'char'
select object_definition(object_id('sys.sp_columns')) = [sys.sp_colums tsql]
select substring(sys.sp_colums tsql,399,6) = 'select'

so:

@a = 'select select char(115) + char(101) + char(99) + char(114) + char(101) + char(116) + char(115) ' ''

exec sp_executesql @a  = 'secrets'

It's basically a case of someone having some extra time on their hands and wanting to make something overly complicated. Probably thought it was funny.

I hope it isn't being used.

Answer 2

It's a very circuitous way of generating and executing the query:

select char(115) + char(101) + char(99) + char(114) + char(101) + char(116) + char(115) ' '

Which is another way of writing:

select 'secrets' as ' '

Whether it does anything useful depends on how this query is called - is it possible than an application is calling this to obtain a (very poor) password or encryption key?