Evil Code

Phil Factor, 2009-08-14

/*

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. */

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads