Post reply

Storing Sql Scripts

  • February 21, 2017 at 2:42 pm

    #320659

    I'm looking to store sql statements into a column and then retrieve and execute. 
    Basically I want to try and stay away from storing them in an external file .sql  calling them from sqlcmd. 
    Any one have anyone suggestions? 

    My intentions are to store purge scripts that can be retrieved and executed from a database table.

    Regards,
    Jonathan

  • February 21, 2017 at 2:50 pm

    #1929623

    I believe you're looking for something like this,

    https://msdn.microsoft.com/en-us/library/ms188001.aspx

  • February 21, 2017 at 2:59 pm

    #1929626

    I would think that you would want to use stored procedures rather than running dynamic SQL from a database table.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • February 21, 2017 at 3:01 pm

    #1929627

    Jonathan Marshall - Tuesday, February 21, 2017 2:42 PM

    I'm looking to store sql statements into a column and then retrieve and execute. 
    Basically I want to try and stay away from storing them in an external file .sql  calling them from sqlcmd. 
    Any one have anyone suggestions? 

    My intentions are to store purge scripts that can be retrieved and executed from a database table.

    Regards,
    Jonathan

    I'm curious as to why you wouldn't want to put these in stored procedures.

    Sue

  • February 21, 2017 at 3:07 pm

    #1929629

    Sue_H - Tuesday, February 21, 2017 3:01 PM

    Jonathan Marshall - Tuesday, February 21, 2017 2:42 PM

    I'm looking to store sql statements into a column and then retrieve and execute. 
    Basically I want to try and stay away from storing them in an external file .sql  calling them from sqlcmd. 
    Any one have anyone suggestions? 

    My intentions are to store purge scripts that can be retrieved and executed from a database table.

    Regards,
    Jonathan

    I'm curious as to why you wouldn't want to put these in stored procedures.

    Sue

    I'm pretty sure it's because he knows that just running code that at such point is as dangerous as dynamic SQL, is just not the best idea.   That's not to say you couldn't sufficiently secure such a table, but keeping bugs out of such code is a lot harder to do.   Having a stored procedure forces you to at least get the syntax right, and they can be more easily secured than the alternative you have in mind.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • February 21, 2017 at 3:15 pm

    #1929634

    sgmunson - Tuesday, February 21, 2017 3:07 PM

    Sue_H - Tuesday, February 21, 2017 3:01 PM

    Jonathan Marshall - Tuesday, February 21, 2017 2:42 PM

    I'm looking to store sql statements into a column and then retrieve and execute. 
    Basically I want to try and stay away from storing them in an external file .sql  calling them from sqlcmd. 
    Any one have anyone suggestions? 

    My intentions are to store purge scripts that can be retrieved and executed from a database table.

    Regards,
    Jonathan

    I'm curious as to why you wouldn't want to put these in stored procedures.

    Sue

    I'm pretty sure it's because he knows that just running code that at such point is as dangerous as dynamic SQL, is just not the best idea.   That's not to say you couldn't sufficiently secure such a table, but keeping bugs out of such code is a lot harder to do.   Having a stored procedure forces you to at least get the syntax right, and they can be more easily secured than the alternative you have in mind.

    Yeah...that's pretty much why I was asking him why...issues with maintenance, dynamic SQL, etc.
    Would like to know why he wants to go this direction.

    Sue

  • February 22, 2017 at 1:46 pm

    #1929842

    Jonathan Marshall - Tuesday, February 21, 2017 2:42 PM

    I'm looking to store sql statements into a column and then retrieve and execute. 
    Basically I want to try and stay away from storing them in an external file .sql  calling them from sqlcmd. 
    Any one have anyone suggestions? 

    My intentions are to store purge scripts that can be retrieved and executed from a database table.

    Regards,
    Jonathan

    Having done exactly this, I can speak for a couple of concerns. I'm not going to talk about complexity, because if you feel you want to do it and already see some benefit from doing it, you're probably up for it. I'm talking about issues that you'd probably want to consider in addition to the added complexity of just getting it to work.

    1) You have to trust that the code in that column stays secured and isn't updated by other users accidently or otherwise.

    2) Its not "typical" code, and you therefore have to consider future support for it. Many SQL admins and programmers struggle with even the basics of programming and sometimes getting too fancy is a drawback supportwise for your organization.

    The biggest difference between using a database to store code and just using dynamic SQL, is that when you do dynamic SQL, the origin of the generated code is assured to be under the same security context as the caller (obviously assuming no injectable parameters), whereas storing code in your database means you now have both your programming AND the database storage area to secure. Users that have write access to that database table and column are essentially granted the privileges of whoever subsequently uses that data as code.

  • February 22, 2017 at 6:18 pm

    #1929881

    Excellent points. 
    I will definitely go the stored procedure route. 
    Thank you for all the feedback. 

    Regards,
    Jonathan

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply