Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Polymorphed Proc


Polymorphed Proc

Author
Message
DannyY
DannyY
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 165
rudy komacsar (3/16/2009)
and yet again to prove why it does work:

CREATE PROC [;] AS BEGIN
EXEC ('ALTER PROC [;] AS SELECT NULL')
EXEC [;]
DROP PROC [;]
END
GO
EXEC [;]


Yes, but you had to use square brackets - making it obvious-, whereas GO worked like a "charm" (please read "later nightmare")
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1474 Visits: 3059
Using the name "GO" is a red herring here and is not so strange as the fact that this is both recursive and self-modifying code. Generally, self-modifying code is not good to use because of the added complexity in maintenance.

Sure it works:
The script creates the proc with three steps inside.
The script runs the proc.
While running,
The proc alters itself to select NULL
The proc runs itself, returning the NULL
The proc drops itself.
Peter Schott
Peter Schott
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 1906
Strangely enough, my first choice for an answer - "The DBA who wrote this is locked out of all accounts and escorted out of the department" - was not an option. I agree that it's an interesting exercise, but in general someone who writes something like this for production use should not be allowed to code SQL for a long time. Smile

Of course, that being said, I think that Red Gate had a blog posting recently that read very much like an obfuscated SQL contest entry. The article tried to use just about every reserved word possible to build tables, name columns, populate the tables, create the procs, etc. It was amusing, but I'd sure hate to have to maintain anything really written like that.



StarNamer
StarNamer
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1294 Visits: 1992
If you look in SSMS Options under Query Execution, you'll find that the GO batch separator can be changed.

I wouldn't recommend it though.BigGrin

Derek
Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 18651
Derek Dongray (3/16/2009)
If you look in SSMS Options under Query Execution, you'll find that the GO batch separator can be changed.

I wouldn't recommend it though.BigGrin


Yeah - I can see someone changing it to SELECT... Sad
Amit Lohia
Amit Lohia
Say Hey Kid
Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)

Group: General Forum Members
Points: 706 Visits: 174
Those who answer the question correctly without running the query and he is not a newbie in SQL Server then you really did good. All those who cheated by running the query, you just raised your score but failed to test your real knowledge.

I was wrong in my answer Sad.


Kindest Regards,

Amit Lohia
Amit Lohia
Amit Lohia
Say Hey Kid
Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)

Group: General Forum Members
Points: 706 Visits: 174
Though I was wrong in my answer but now I can understand or I think I understand why it will return NULL.

You can alter the procedure and drop the procedure within the same procedure so this will eliminate two options from the answer

It is not recrusive eliminating the third option also

Now between error message of GO and NULL.
GO will consider as the above T-SQL ready to go as a batch only if it the first word in the line.

For example this will fail,

CREATE PROC
GO AS -- As go it the first word in the line
BEGIN
EXEC ('ALTER PROC GO AS SELECT NULL')
EXEC GO
DROP PROC GO
END
GO

EXEC GO


Kindest Regards,

Amit Lohia
Sanjay-300840
Sanjay-300840
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 124
I was wrong in answering the question. But to me it seems that Procedure name 'GO' is not the cause of concern. If I put something like this

CREATE PROC GOT AS BEGIN
EXEC ('ALTER PROC GOT AS SELECT NULL')
--EXEC GOT
DROP PROC GOT
END
GO
EXEC GOT

As you see, if the second Exec statement is commented out then it doesn't throw the 'NULL', which means that the second execution result is what it shows up when the whole script is run. Also If just the last statment 'EXEC GO' is run twice for the script provided in the question then it throws the right error.
Another point is that if you don't execute the last statement 'EXEC GO' , then you can see that the procedure still exists in the DB. So that exec statement is causing the procedure to live even after the drop statement.
Aaron N. Cutshall
Aaron N. Cutshall
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1599 Visits: 964
I agree with the earlier statement that the choice of a procedure named "GO" was a red herring. That has NOTHING to do with the GO separator. The procedure could have just as easily been named "XYZ" or anything else.

The reason it works is that the procedure is first created then executed. While that procedure is in memory, the stored version is then altered to simply return NULL, then executed, then dropped. All while this is happening, the version in memory is unaffected and when it is completed it of course no longer exists. The version stored in the database was already dropped so there is no trace of it whatsoever.

I agree that it was an interesting bit of a brain teaser made more difficult with using a name of "GO" so as to confuse us with the separator. Good job! Hehe


"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
brewmanz
brewmanz
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 406
I got it wrong, but nothing to do with the use of the word 'GO'.

As I calculated in my head what would happen, I had to make a guess as to whether the procedure 'GO' would be cached and so escape being changed by the 'ALTER' when invoked by next line. I opted for the 'nesting level exceeded'. Oh well, never mind; I learnt more by trying to work it out and getting it wrong than blindly running it and getting points.

I too use these QOTDs as a learning exercise (IANAL^HDBA), but simple male (or should that be 'human' in these PC days?) pride means that, ummm, sometimes I *do* run the code to make sure I get the points. Then again, I can just post some comment in the forum!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search