Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Evil Code

/*
A couple of years ago, I wrote the Simple-Talk Prettifier. This is really no more than a stored procedure that renders SQL code  as HTML. I then suggested that the code should be allowed on Simple-Talk, and had the humiliation of seeing the contemptuous ease with which Neil and the Red Gate testers found all sorts of examples of code that it failed to render properly. I'd tested it with a lot of code, but had no idea of the scale of testing that goes on for a product such as SQL Refactor, SQL Prompt or SQL Doc. Much to my indignation, they used Evil SQL. Evil SQL?  Hmm.. Stuff like this. */
 
CREATE TABLE ## ( # INT )
DECLARE @ INT  set @=9
INSERT  INTO ##
        ( # )
        SELECT  @%2
SELECT  *
FROM    ##
DROP TABLE ##
/* can you imagine that this would execute perfectly happily? Try it!  */
 
/* The team that writes these tools at Red Gate come across all sorts of oddities. The other day, they showed me a couple of strange things that inspired me to write this. */
--first create a GetDate schema
CREATE SCHEMA GETDATE
--and a GetDate table to go in it.
CREATE TABLE GetDate.GetDate
(
GetDate DATETIME,
[GetDate GetDate] DATETIME
)
GO
--and a function called GetDate
CREATE FUNCTION GetDate()
RETURNS TABLE
AS RETURN
(
     SELECT GetDate() AS [GetDate]
)
GO
-- Now we can write some startlingly silly code
INSERT INTO GetDate.GetDate(GetDate.GetDate.GetDate.GetDate, [GetDate GetDate])
     SELECT GetDate() AS GetDate, GetDate FROM GetDate()
--but we can do far far siller stuff if we wanted purely because there is no restriction on what goes between angle-brackets      
GO
CREATE FUNCTION [GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate]()
RETURNS TABLE
AS RETURN
(
     SELECT GetDate() AS [GetDate]
)
GO
 
INSERT INTO GetDate.GetDate(GetDate.GetDate.GetDate.GetDate, [GetDate GetDate])
     SELECT GetDate() AS GetDate, GetDate FROM [GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate]()
 
DROP FUNCTION GETDATE,[GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate
GetDate.GetDate.GetDate.GetDate]
DROP TABLE GETDATE.GetDate
DROP SCHEMA GETDATE
 
/* but this is boring. We are much better off using a poem by Odgen Nash as the name of a table. Whoever said that having object-naming policies was a good thing? */
CREATE FUNCTION
   [Candy
    Is dandy
    But liquor
    Is quicker.]()
RETURNS TABLE
AS RETURN
(
     SELECT GetDate() AS [GetDate]
)
GO
Select * from
    [Candy
    Is dandy
    But liquor
    Is quicker.]()
   
/* but you try leaving out a space!  I leave to the reader the exercise of having an entire database of different tables, all of which use exactly the same poem for all its object names. This is serious obfuscation. */ 
 
/* we could be a bit more creative and see if we can execute a verse of Macauley's famous poem 'Horatius'. */
 
--create a table with a slightly unusual name
create table [many a stately market-place;
    From many a fruitful plain;
    From many a lonely hamlet,]
    (
[The horsemen and the footmen
    Are pouring in amain] int,
    [, hid by beech and pine,] varchar(100)
)   
--put a value into this table
insert into [many a stately market-place;
    From many a fruitful plain;
    From many a lonely hamlet,] ([The horsemen and the footmen
    Are pouring in amain], [, hid by beech and pine,])
    Select 1,'an eagle’s nest, hangs on the crest
    Of purple Apennine;'
 
/* now, with that preparation work done, we can execute the third verse */
Select   
    [The horsemen and the footmen
    Are pouring in amain]
    From [many a stately market-place;
    From many a fruitful plain;
    From many a lonely hamlet,]
    Where[, hid by beech and pine,]
    Like 'an eagle’s nest, hangs on the crest
    Of purple Apennine;'
 
/* If this seems a bit .er.. florid, you can go minimalist approach to Evil Code */
--first clear up
DROP TABLE [many a stately market-place;
    From many a fruitful plain;
    From many a lonely hamlet,]
DROP FUNCTION [Candy
    Is dandy
    But liquor
    Is quicker.]
--now let's go neo-bauhaus
 
Create table [
] (# int)
 
insert   into [
]        select   1
 
Select   *
from     [
]
 
create table [ ] (# int)
insert   into " " select   1
select   *
     from     " "
 
drop table "
"," "
/*which twice creates a TABLE, inserts data INTO it, SELECTs the contents and then drops both tables.

So armed with this little lot I downloaded the new SQL Prompt 4  and tested it. Did it blink an eyelid? Did it lay it all out nicely? Darn it; I'd have loved to find code that SQL Prompt would trip over. Memories of their transparent joy when finding problems with the Prettifier still linger. */

Comments

Posted by R. C. van Dijk on 18 August 2009

brilliant peace.

I use the EvilCode for testing applicants here and it never fails!

Keep it up.

Posted by rja.carnegie on 18 August 2009

I wrote a program that had line breaks - not in an object name but in a character variable assignment - and the program failed in some deployments because one of our tools installed it with a line break other than hexadecimal 0xODOA.  SQL Compare, for instance, didn't detect this difference (in the version we were using).  Query Analyzer defaults to "our" line break, so scripting and re-creating in QA produced a working program.  But Management Studio respects the line break style found in the object, so didn't cure the virus.  I offer this as an Evil Whitespace example.

Posted by rkiser on 18 August 2009

Nice piece, except the poem you attributed to Ogden Nash was actually written by Dorothy Parker.

Posted by kramaswamy on 18 August 2009

oh this is absolutely brilliant. i love it!

Posted by CJensen on 18 August 2009

Exactly why documented object naming policies *are* a good thing. Imagine finding these object name on a database newly assigned to you. Or better yet, X and Y and Z as object names.

By the way, "candy is dandy" *is* Ogden Nash. Dorothy Parker gave us:

"Men seldom make passes at girls who wear glasses"

among many others.

Posted by jeff.kunkel on 18 August 2009

Great blog post Phil! This was fun.

Posted by Phil Factor on 18 August 2009

Yes. It was Odgen Nash. The great Dorothy Parker gave us a couple of possible table-names.

I like to have a martini,

Two at the very most.

After three I'm under the table,

after four I'm under my host.

I'd rather have a bottle in front of me,

than a frontal lobotomy.  

Posted by rob.lobbe on 18 August 2009

If you truly want to have fun, try it with the quote characters. [It's a table name] or 'Table [Boxed]'

In particular when you have embedded quotes and you are near the sysname limit - quoting quotes can easily explode the character length of a 'named' item, try it with some dynamic sql.

Posted by Paul Bowman on 19 August 2009

wow!

Posted by Kop_Eoin on 19 August 2009

You're a gas man.

Posted by Karl Heinz Brehme on 19 August 2009

With deep Unreal narrator voice: Holy Shi&&&&!

BTW, I´m started some time ago to use this kind of thing like:

Table Name: [Security.Users]

Procedure for list: [Security.Users.List]

Procedure for saving data (Insert or Update]: [Security.Users.Save]

Procedure for delete: [Security.Users.Delete]

Maybe boring sometimes, due to braclets, but can "transform" a relational data base in a Object Oriented point of view....

Ok, I´ll stop to code and continue to play Unreal 3... alone, LOL...

Posted by Jesse McLain on 12 October 2009

The "Getdate" code reminds me of the movie "Being John Malkovich", when Malkovich enters his own portal. ("Malkovich Malkovich Malkovich. Malkovich? Malkovich Malkovich.")

Posted by Slick on 16 December 2009

To the first poster.. you are so sick to be using this "Evil Code" to test applicants. I feel sorry for the poor fellows!

Posted by Saravanan on 5 February 2010

Evil Code is absolutely great.....

Leave a Comment

Please register or log in to leave a comment.