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


Polymorphed Proc


Polymorphed Proc

Author
Message
Saurabh Dwivedy
Saurabh Dwivedy
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 340
DPhillips (3/17/2009)
Chris Harshman (3/17/2009)
I'm just suprised that SQL Server would let a stored procedure modify its own code. That's the real head scratcher here. Ermm


Many code environs can allow this, especially in DBs where the SPs are actually stored within the system, not individual physical disk files. This is not unique only to SQL Server.

The trick is, it isn't modifying its' own code... the in memory version is modifying the saved version... it doesn't need to lock the file for writes, as it has read the whole thing in. Any internal sub-executions are also read into memory and the saved object is still free for ALTER/DROP.

Having a valid reason to do so is another issue entirely, but functionally there is no reason to dis-allow it.


When you say the "in-memory" version is modifying the "saved version" - I seek a bit more clarity here. The difference between "in memory" and "saved version" will only be the difference between an uncompiled and compiled code -

I write a code, compile it and execute it. When I execute the code, it gets loaded in memory.

Now I make changes to it. I Save it but don't execute it yet. This, according to me, is the "saved version' and is - as it stands at the moment - different from the "in-memory" version because I still haven't executed the "saved code".

So, in the present example: I create a proc and then within the body of the proc I issue an Alter Proc command and then Execute the proc itself. Which PROC is it going to ALTER and EXECUTE? Which are the saved and in-memory versions here?

Regards

Please correct my understanding because I am totally lost here.

Regards

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!
DPhillips-731960
DPhillips-731960
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1158 Visits: 801
"
CREATE PROC GO AS BEGIN
EXEC ('ALTER PROC GO AS SELECT NULL')
EXEC GO
DROP PROC GO
END
GO
EXEC GO
"

So to start we have actually TWO primary initiated commands.
First:
"
CREATE PROC GO AS BEGIN
EXEC ('ALTER PROC GO AS SELECT NULL')
EXEC GO
DROP PROC GO
END
GO
"

And Second:
"
EXEC GO
"

The first one executes and completes whether or not it remains leftover in memory has no further effect on any subsequent processing of it.

The second one is the one that loads the saved version for execution.

So, at this point, what is loaded and has begun to execute is:
"
PROC GO AS BEGIN
EXEC ('ALTER PROC GO AS SELECT NULL')
EXEC GO
DROP PROC GO
END
"

Now, execution line-by-line is:
"EXEC ('ALTER PROC GO AS SELECT NULL')" Note that this is modifying the SAVED version, not the in-memory current run version.

The next line executes the newly modified saved version of:
"PROC GO AS SELECT NULL"

Note that at this moment, *TWO* different instances and versions of proc "GO" are running.

This SECOND "inner" instance in memory completes the Select, and returns a NULL. It reaches completion and ends.

The FIRST "outer" instance in memory now continues the next command:
"DROP PROC GO",
which serves to drop the saved version, and has no bearing on the currently running memory version.

Now the last command in the FIRST "outer" version is, "END", which terminates the only remain ("first") memory version.

Let's recap the order of events again, in other more human language:
1. Version 1 of PROC GO is saved.
2. Version 1 of that PROC is EXECuted (into memory) and becomes memory version 1.
3. Memory version 1 changes saved version 1 to saved version 2, keeping the same name, "GO".
4. Memory version 1 EXECutes new saved "GO" (version 2) into memory and completes having executed the SELECT, and goes into the ether, but version 2 still exists as saved PROC "GO".
5. Memory Version 1 continues by dropping whatever version is stored as "GO" (happens to be version 2 as the only version 1 that still exists is this running thread).
6. Memory version 1 ENDs and evaporates into the ether.

Hope this helps clear it all up. The in memory version is not the saved version, whether either is compiled or not.
DPhillips-731960
DPhillips-731960
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1158 Visits: 801
I wanted to add that it is definitely by design that multiple instances of the same named Procedure can be executed at the same time. If not, then recursive trees or structures can be very painful to write.

The fact that the procedure can be modified while other active instances of that procedure are still running is also by design, especially for adaptive code sets.

I still have never run into an instance where it was necessary for a piece of code to modify itself, but this borders on wild frontiers where code can be made to be self healing and/or self mutating. That is both scary and wonderfully thought-provoking.

I think we are yet only dabbling in the shore-line of an ocean of future possibilities.
Rudyx - the Doctor
Rudyx - the Doctor
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3290 Visits: 2476
self healing or mutation ??? hmmm ... sounds like malware or a virus to me ...

Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
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: 1596 Visits: 964
OMG!! It's the beginnings of Skynet! Hehe


"...when ye are in the service of your fellow beings ye are only in the service of your God." -- Mosiah 2:17
Rudyx - the Doctor
Rudyx - the Doctor
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3290 Visits: 2476
Skynet begins to learn at a geometric rate. It becomes self-aware at 2:14 a.m. Eastern time, August 29th. In a panic, they try to pull the plug. ...

Glad it was in 1997 ... or is it yet to come ...

Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Saurabh Dwivedy
Saurabh Dwivedy
SSC-Addicted
SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)SSC-Addicted (488 reputation)

Group: General Forum Members
Points: 488 Visits: 340
Dear DPhillips,

Thank you for the painstakingly elaborate explanation. Took me a while to figure out - not because the explanation was lacking in anyway - but due to my own capacity to assimilate the information.

Thanks once again.

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!
DPhillips-731960
DPhillips-731960
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1158 Visits: 801
saurabh.dwivedy (5/12/2009)
Dear DPhillips,

Thank you for the painstakingly elaborate explanation.


Glad I could help!
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10704 Visits: 12002
DPhillips (5/11/2009)
I still have never run into an instance where it was necessary for a piece of code to modify itself, but this borders on wild frontiers where code can be made to be self healing and/or self mutating. That is both scary and wonderfully thought-provoking..


One use for this sort of thing (but with conditional self-modification, raher than unconditional as in the question) is code that changes its behavious the first time it is run after certain kinds of intrusion are detected. I'm not sure if this is useful in the T-SQL case, but it has been used in other languages (where it tends to be much more diffiult than it is in T-SQL).
And as for code that drops itself - I used to use that sometimes when I want to be sure something only happens once, as part of a belt and braces approach to some kinds of data upgrade: the proc that does the upgrade firsts drops itself so that no-one can try calling it again, then checks whether the upgrade has already been done by seeing if a "this is already done" flag exists, then does the upgrade (altering much data) and creates the flag. Now I don't do it that way any more - not sure why.

Tom

khullargirish02
khullargirish02
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 126
apart from al syntax discussions, I enjoyed this question. Thanks QoD
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