Power By 专否社区

Best way to store Sql Scripts in a database table

Go To StackoverFlow.com

-1

I need to store stored procedure execution scripts in a database table.

As an example:

exec proc_name 'somedata'

These are for execution at a later time after the data that will be changed has gone through a moderation process.

What is the best way to cleanse the script so that the statement cannot be used for sql injection.

Is there a specific type for encoding that I can use? Or is it as simple as doing a replacement on the '

2012-04-05 01:52
by shenku
Are you familiar with stored procedures - Colin 2012-04-05 01:54
What kind of "SQL scripts" do you need to save? You've given no information that can be used to answer your question. Please edit and provide details about what you're actually trying to do; as written, this isn't really a question and should be closed. Thanks. : - Ken White 2012-04-05 02:00
hi ken, thanks for your response, my question however is quite clear, "what is the best and safest way to store sql scripts for execution at a later time", sql script? well could be anything however in this case it is an execute for a stored procedure, as in "exec proc_name 'somedata'" - hope that clears up the confusio - shenku 2012-04-05 03:20
voting to close, as the author refuse to make it a real one - Your Common Sense 2012-04-05 04:35

1

Then it sounds like you would want to use a varchar(max) column and have a separate table for parameters.. If you use Parameters you should be safe from SQL injections. See quickie C# example below:

C# psuedo-code example

SQLCommand command = new SQLCommand("select * from myScripts where scriptid = @scriptid");
SQLParameter param = new SQLParameter("@scriptid", 12, int);

...new SQLCommand("select * from myParams where scriptid = @scriptid");
...new SQLParameter...

DataReader dr = new blah blah...

SQLCommand userCommand = new SQLCommand(dr['sql']);

foreach (parameter in params)
{
    userCommand.Parameter.Add(parameter['name'], value);
}

userCommand.Execute...
2012-04-05 02:00
by Michael Rice

0

You can either store your scripts for later execution in a Stored Procedure or a scheduled job. I don't see any reason for encoding a stored procedure, as you can put user privileges to prevent different users from reading or even seeing them.

2012-04-05 01:54
by t-clausen.dk
its quite a complex metadata structure that users will be changing data within, its not really a single SP that can be executed unfortunately - shenku 2012-04-05 03:26

0

There is no way to "cleanse" scripts.
The only way to secure your code is to separate the code from data. And "cleanse" the data only.

That's why we have our code separated from data.
The code is solid and secured, and data is variable and haver to be "cleansed".

As you are breaking this fundamental law, treating the code as data, there is no way to secure it.

Judging by the utter unusualness of the task, I'd say there is a proper solution for sure.
You just choose the wrong architecture.
So, you'd better ask another question, something like "I want to deal with quite complex metadata structure (with the structure and the purpose provided)" and you will get a proper solution that will require no storing SQL codes among the data.

2012-04-05 05:31
by Your Common Sense
Ads