The all-purpose stored procedure



  • I recently found this useful sproc in our database:

    
    CREATE Procedure [dbo].[asp_GetSomeDomainObjectForASpecificOperation]
    	@Query as nvarchar(4000)
    As
    Begin
    	exec sp_executesql @Query 
    end
    
    

    I don't know why we bother with sprocs that contain, you know, actual SQL queries--this is so flexible!



  • There's more than one way to Rome, but all roads lead to this stored procedure.



  • That "asp_" prefix in this thing makes me think about web applications. The fact that it simply executes whatever comes it way makes me wonder whether the code above it runs any validation at all on the query.

    SQL injection, anyone?



  • [quote user="Renan "C#" Sousa"]

    That "asp_" prefix in this thing makes me think about web applications. The fact that it simply executes whatever comes it way makes me wonder whether the code above it runs any validation at all on the query.

    SQL injection, anyone?

    [/quote]I'm guessing there is a requirement that all data querying be done via stored procedures.  I'm also guessing this is someones way of getting around that requirement...

    @toth said:

    I recently found this useful sproc in our database:

    You wouldn't happen to work at a school, with a student nicknamed "little bobby tables", would you?


  • [quote user="Renan "C#" Sousa"]

    That "asp_" prefix in this thing makes me think about web applications. The fact that it simply executes whatever comes it way makes me wonder whether the code above it runs any validation at all on the query.

    SQL injection, anyone?

    [/quote]

    Actually, in our database, almost every sproc begins with "asp_" and it's not a web application--it's mostly a Winforms app. If it's not a reporting sproc ("rpt_") or a utility sproc ("usp_"), it begins with "asp_". I seem to remember someone explaining that it stood for "Application Stored Procedure" or something. It bugs the hell out of me.



  • @galgorah said:

    You wouldn't happen to work at a school, with a student nicknamed "little bobby tables", would you?

    No, but I work on an application developed by an Indian company. It's being "re-onshored" to internal development. To be fair, I don't thing this is ever called except with a SQL query constructed in the code of a Winforms app used exclusively internally.


  • Trolleybus Mechanic

    @toth said:

    CREATE Procedure [dbo].[asp_GetSomeDomainObjectForASpecificOperation] @Query as nvarchar(4000) As Begin exec sp_executesql @Query end
     

    EXEC asp_GetSomeDomainObjectForASpecificOperation 'asp_GetSomeDomainObjectForASpecificOperation'

    Hmmm, now how do we get that to call itself in an infininte regression?



  • @toth said:

    @galgorah said:
    You wouldn't happen to work at a school, with a student nicknamed "little bobby tables", would you?
    No, but I work on an application developed by an Indian company. It's being "re-onshored" to internal development. To be fair, I don't thing this is ever called except with a SQL query constructed in the code of a Winforms app used exclusively internally.

    If it's any consolation,  I came across a stored procedure last month that needlessly involved cursors nested 4 levels deep, Lots of dynamic sql, temp tables with naughty names, and extremely liberal use of the NOLOCK query hint everywhere..  Also I started here 3 months ago and found that they had no useful indexes on any of the tables in the database.  the strategy was define a single column index for every single column.  The good precentage of the tables are GBs in size. I was brought on to clean up the mess so to speak. 


  • @toth said:

    I recently found this useful sproc in our database:

    
    CREATE Procedure [dbo].[asp_GetSomeDomainObjectForASpecificOperation]
    	@Query as nvarchar(4000)
    As
    Begin
    	exec sp_executesql @Query 
    end
    
    

    I don't know why we bother with sprocs that contain, you know, actual SQL queries--this is so flexible!

    This is what you get when you tell people that stored procedures protect against SQL injection.  A junior dev simply took this bit of information and tried to build an injection-proof data access layer out of it.


  • Well? How did that fare?

    Anybody mad enough to try that on his own database?

    Cause frankly, I'm dying to know what will that abomination of code do to a poor server...
    @Lorne Kates said:

    @toth said:

    CREATE Procedure [dbo].[asp_GetSomeDomainObjectForASpecificOperation]
    @Query as nvarchar(4000)
    As
    Begin
    exec sp_executesql @Query
    end
     

    EXEC asp_GetSomeDomainObjectForASpecificOperation 'asp_GetSomeDomainObjectForASpecificOperation'

    Hmmm, now how do we get that to call itself in an infininte regression?



  • @Izhido said:

    Well? How did that fare? Anybody mad enough to try that on his own database? Cause frankly, I'm dying to know what will that abomination of code do to a poor server... @Lorne Kates said:

    @toth said:

    CREATE Procedure [dbo].[asp_GetSomeDomainObjectForASpecificOperation] @Query as nvarchar(4000) As Begin exec sp_executesql @Query end
     

    EXEC asp_GetSomeDomainObjectForASpecificOperation 'asp_GetSomeDomainObjectForASpecificOperation'

    Hmmm, now how do we get that to call itself in an infininte regression?

    Common Table Expression based queries in SQL Server will easily cause an infinite loop due to their recursive nature. 


  • @toth said:

    I recently found this useful sproc in our database:

    
    CREATE Procedure [dbo].[asp_GetSomeDomainObjectForASpecificOperation]
    	@Query as nvarchar(4000)
    As
    Begin
    	-- maybe I needing later
    exec sp_executesql @Query
    end

    FTFY!




  • @DaveK said:

    @toth said:


    I recently found this useful sproc in our database:

    
    CREATE Procedure [dbo].[asp_GetSomeDomainObjectForASpecificOperation]
    	@Query as nvarchar(4000)
    As
    Begin
    	-- maybe I needing later
    exec sp_executesql @Query
    end

    FTFY!


    win


  •  This is obviously the case of the DBAs mandating that all calls to the database be done through stored procs, but for some reason not knowing they can disable execsql.  Unfortunately, our DBAs know how to disable it.



  • @ShatteredArm said:

     This is obviously the case of the DBAs mandating that all calls to the database be done through stored procs, but for some reason not knowing they can disable execsql.  Unfortunately, our DBAs know how to disable it.

    FTFY.

    If someone in my company ever wrote code like the one in the OP, we would fire the bugger quicker than you can say WTF.



  • @ShatteredArm said:

     This is obviously the case of the DBAs mandating that all calls to the database be done through stored procs, but for some reason not knowing they can disable execsql.  Unfortunately, our DBAs know how to disable it.

    But if sp_execsql were disabled, how could we run all our search procs that take @WhereClause as a parameter?!?!



  • @Izhido said:

    Well? How did that fare?

    Anybody mad enough to try that on his own database?

    Cause frankly, I'm dying to know what will that abomination of code do to a poor server...
    @Lorne Kates said:

    @toth said:

    CREATE Procedure [dbo].[asp_GetSomeDomainObjectForASpecificOperation]
    @Query as nvarchar(4000)
    As
    Begin
    exec sp_executesql @Query
    end
     

    EXEC asp_GetSomeDomainObjectForASpecificOperation 'asp_GetSomeDomainObjectForASpecificOperation'

    Hmmm, now how do we get that to call itself in an infininte regression?

    Why would this create an infinite loop? By the second iteration, the asp_GetSomeDomainObjectForASpecificOperation parameter would be gone, wouldn't it? Maybe I'm misunderstanding @Query, this is how I'm translating it in my head:


    function callFunction(fn) {
    fn();
    }
    callFunction(callFunction);

    The second time through, callFunction is called without parameters, so fn would be null.



  • @Xyro said:

    @Izhido said:
    Well? How did that fare?

    Anybody mad enough to try that on his own database?

    Cause frankly, I'm dying to know what will that abomination of code do to a poor server...
    @Lorne Kates said:

    @toth said:

    CREATE Procedure [dbo].[asp_GetSomeDomainObjectForASpecificOperation]
    @Query as nvarchar(4000)
    As
    Begin
    exec sp_executesql @Query
    end
     

    EXEC asp_GetSomeDomainObjectForASpecificOperation 'asp_GetSomeDomainObjectForASpecificOperation'

    Hmmm, now how do we get that to call itself in an infininte regression?

    Why would this create an infinite loop? By the second iteration, the asp_GetSomeDomainObjectForASpecificOperation parameter would be gone, wouldn't it? Maybe I'm misunderstanding @Query, this is how I'm translating it in my head:

    function callFunction(fn) {
    fn();
    }
    callFunction(callFunction);

    The second time through, callFunction is called without parameters, so fn would be null.

    Indeed. And if you just called the function again passing the same parameters, then you'd only get a stack overflow at some point. In any case you'd be lacking on destructive power.

    What you really need for a powerful code bomb is a sproc with a query that will get the last identity inserted into the database, and if it perfectly divisible by (average insertions in your database per semester), cascade-delete three random items from three different important tables. Or better yet, truncate something.

    Bonus points if you do this in a trigger instead of a stored procedure.



  • @Xyro said:

    @Izhido said:
    Well? How did that fare?

    Anybody mad enough to try that on his own database?

    Cause frankly, I'm dying to know what will that abomination of code do to a poor server...
    @Lorne Kates said:

    @toth said:

    CREATE Procedure [dbo].[asp_GetSomeDomainObjectForASpecificOperation]
    @Query as nvarchar(4000)
    As
    Begin
    exec sp_executesql @Query
    end
     

    EXEC asp_GetSomeDomainObjectForASpecificOperation 'asp_GetSomeDomainObjectForASpecificOperation'

    Hmmm, now how do we get that to call itself in an infininte regression?

    Why would this create an infinite loop? By the second iteration, the asp_GetSomeDomainObjectForASpecificOperation parameter would be gone, wouldn't it? Maybe I'm misunderstanding @Query, this is how I'm translating it in my head:

    function callFunction(fn) {
    fn();
    }
    callFunction(callFunction);

    The second time through, callFunction is called without parameters, so fn would be null.

    Indeed. And if you just called the function again passing the same parameters, then you'd only get a stack overflow at some point. In any case you'd be lacking on destructive power.

    What you really need for a powerful code bomb is a sproc with a query that will get the last identity inserted into the database, and if it perfectly divisible by (average insertions in your database per semester), cascade-delete three random items from three different important tables. Or better yet, truncate something.

    Bonus points if you do this in a trigger instead of a stored procedure.



  • [quote user="Renan "C#" Sousa"]@ShatteredArm said:

     This is obviously the case of the DBAs mandating that all calls to the database be done through stored procs, but for some reason not knowing they can disable execsql.  Unfortunately, our DBAs know how to disable it.

    FTFY.

    If someone in my company ever wrote code like the one in the OP, we would fire the bugger quicker than you can say WTF.

    [/quote]

    My company also, she will get same tretment


Log in to reply