T-SQL: EXECUTE SP with Parameters Q?

jpb1974

Hi,

I was wondering does anyone know if it is possible to run a stored procedure using the EXECUTE command with all parameters tucked into a single variable? Example -

Replace this code (where each parameter has to be indivually declared and passed into the EXEC statement):

declare @retval integer
declare @param1 varchar(50)
declare @param2 varchar(50)
select @param1 = 10
SELECT @param2 = 20
EXEC @retval = TOMS_JOB_STEP_1 @param1, @param2

With something along the lines of the following, where the parameters are packaged into a single variable:

declare @retval integer
declare @params varchar(50)
select @params = '(@var1=10,@var2=test)'
EXEC @retval = TOMS_JOB_STEP_1 @params

I'm trying to implement a stored procedure which will read SP names and parameters from a database table and execute them. Problem is that SQL Server seems to insist that each and every parameter is individually declared in the EXECUTE statement. I can think of longer ways of doing this but was wondering if anyone had any thoughts as to a simple solution?

It is important that the return value is handled similar to the above.

Many Thanks!

stevenmu

I think this could work only if you specifically write the target SPs to accept one single parameter, and then to break up the parameter itself.

Do you really need to do this in a SP, it would be much easier to write some client code to read all the SPs from the DB and then call each of them.

If you really need it to be done on the DB end you could write a .Net/CLR function to do it on the DB end. You could then call that from your 'master' SP if needed.

jpb1974

Hi Steve,

Thanks for the reply.

I understand what you're saying but unfortunately changing all the target SPs is not a runner.

We currently run all these SPs using SQL Servers in-built Job Manager. Our company was recently sold and our new parent company is forcing many changes on us. The SQL Server Job Scheduler is no longer a runner so we're looking at a method of replicating it using a single master SP that reads schedule info from a database table and executes each step generically.

Sounds daft.. but that's how it is.

stevenmu

Parent companies seem to love imposing new standards

I think your best bet so will be the user defined function called from your master SP.

jpb1974

Well.. after at least 50 different permutations and combinations I finally managed to get this to work using the following recommendations as an example -

http://stackoverflow.com/questions/462312/executing-stored-procedure-via-sp-executesql

Basically I can now run a stored procedure with any amount of variables held within a string variable and receive the return value of within that SP.

Happy days... I think... I hope

Procasinator

jpb1974 wrote: »

Well.. after at least 50 different permutations and combinations I finally managed to get this to work using the following recommendations as an example -

http://stackoverflow.com/questions/462312/executing-stored-procedure-via-sp-executesql

Basically I can now run a stored procedure with any amount of variables held within a string variable and receive the return value of within that SP.

Happy days... I think... I hope

Usually referred to as dynamic SQL. You should make sure these types of queries don't introduce any security and/or performance issues.

This is particularly true if you are using EXEC and building the query with string concatenation instead of prepared statements.

jpb1974

I always thought of dynamic SQL as SQL statements generated within your code on the fly... the database doesn't store them so they aren't optimized.

In this case we are calling pre-compiled stored procedures that already exist in the database. We're just not hard-coding the code that calls them.

I don't think it should be an issue... but given the way this parent company operate you never know.

Procasinator

jpb1974 wrote: »

I always thought of dynamic SQL as SQL statements generated within your code on the fly... the database doesn't store them so they aren't optimized.

In this case we are calling pre-compiled stored procedures that already exist in the database. We're just not hard-coding the code that calls them.

I don't think it should be an issue... but given the way this parent company operate you never know.

Well, the dynamic SQL would, in this case, be the call to the stored procedure. If I understand your solution correctly.

Whether or not it has security implications is variable and mostly dependant on the source of the parameters for the query. Can they be manipulated by a end user at some point?

For instance, in this code:

DECLARE @params nvarchar(255)
DECLARE @query nvarchar(500)

-- Is params controllable by the user? Could they inject sql?
set @params  = '@table_name = ''Users''; SELECT 1'

set @query = 'sp_tables '+ @params
EXEC sp_executesql @query

If @params was built off user input, a user might be able to inject additional queries or parameters. In this case I added SELECT 1 to show that more than one query can be executed.

You are probably fine. It's just dynamic sql, much like eval() or similar constructs, tend to inadvertently open security issues; so it's worth reviewing.

Galtee

Procasinator wrote: »

Well, the dynamic SQL would, in this case, be the call to the stored procedure. If I understand your solution correctly.

Whether or not it has security implications is variable and mostly dependant on the source of the parameters for the query. Can they be manipulated by a end user at some point?

For instance, in this code:

DECLARE @params nvarchar(255)
DECLARE @query nvarchar(500)
 
-- Is params controllable by the user? Could they inject sql?
set @params  = '@table_name = ''Users''; SELECT 1'
 
set @query = 'sp_tables '+ @params
EXEC sp_executesql @query

If @params was built off user input, a user might be able to inject additional queries or parameters. In this case I added SELECT 1 to show that more than one query can be executed.

You are probably fine. It's just dynamic sql, much like eval() or similar constructs, tend to inadvertently open security issues; so it's worth reviewing.

How is it dynamic SQL? The SQL isn't changing is it?

Procasinator

Galtee wrote: »

How is it dynamic SQL? The SQL isn't changing is it?

It's not "dynamic", because I hardcoded the params. But as you would see from the comment above the set @params:

-- Is params controllable by the user? Could they inject sql?

My point is, if say 'Users' there was set by the user (i.e. they supplied the table name), they could conceivably add to the query.

I maybe should have used this as a better example:

CREATE PROCEDURE ExecuteQuery
	@proc nvarchar(50),
	@params nvarchar(255)
AS

DECLARE @query nvarchar(500)

-- Is params controllable by the user? Could they inject sql?

set @query = @proc + ' ' + @params
EXEC sp_executesql @query
GO

The point is, if the user can control @proc and/or @params in some way, there could be a vulnerability.

jpb1974

Whether or not it has security implications is variable and mostly dependant on the source of the parameters for the query. Can they be manipulated by a end user at some point?

Fortunatley the parameters are being held in a database table that is not accessible by standard users, will rarely change and if they had to be changed then it would go through change management.

We're just replacing a DBA managed job schedule with a facility of our own that will run in conjunction with AutoSys for job scheduling. The entire process will be Developer/Administrator managed.

So, in this regard, it should be low risk.

Cheers

Giblet

Galtee wrote: »

How is it dynamic SQL? The SQL isn't changing is it?

No, but it's a dynamically created query as opposed to a static query or compiled one. It's execution and scope is unknown until it is run.

jpb1974

No, but it's a dynamically created query as opposed to a static query or compiled one. It's execution and scope is unknown until it is run.

I wouldn't entirely agree with that statement.

In this instance the stored procedures are already compiled and stored in the database.

All we are doing is creating code to call them dynamically.

So the database knows that the SPs are there and how they are structured. In this instance it just doesn't know how they are going to be called which is fair enough... given you could write an external app that will talk to the database and call SPs with parameters... and the Db would be none the wiser.

Galtee

Giblet wrote: »

No, but it's a dynamically created query as opposed to a static query or compiled one. It's execution and scope is unknown until it is run.

What? Dynamic SQL is SQL that's built on the fly.

Giblet

Galtee wrote: »

What? Dynamic SQL is SQL that's built on the fly.

I was explaining that.. not explaining any examples in this thread.

Procasinator

You could argue whether it's dynamic or not to you are blue in the face, but for the most part it doesn't matter (I've had enough semantic disputes for this week).

The original point was using EXEC (and sp_executesql) can be potentially dangerous as generally they are used to execute user input of some description which often bypasses many of the typical safeguards built-in.

My take is dynamic SQL is an SQL that is built and executed in SQL at run-time. The original example given was hardcoded @params to represent how a parameter variables value *may* have been tampered with.

Obviously, the @params nvarchar value was not really dynamic in the example, as it could not have changed any place in the small snippet.

It'd be highly unlikely to see variables with constant values (note constant variables generally they don't exist in SQL implementations) used as an actual query , as it would be pointless: you would just execute the SQL directly. Much like you wouldn't expect to see int two = 1 + 1; in code. :P

Galtee

Unless I'm missing something obvious which could well be the case and it wouldn't be the first time I can't see what the issue is with security based on the original question.

ChRoMe

Galtee wrote: »

Unless I'm missing something obvious which could well be the case and it wouldn't be the first time I can't see what the issue is with security based on the original question.

Variables not being escaped could lead to sql injection attacks, is how it appears to me.

Procasinator

Generally speaking stored procedures offer protection from a lot of attacks due to the parameterised call and the inability to do SQL injection from it (depending, of course).

By building dynamic SQL to execute the stored procedure, the risk is increased, as you are now responsible for this sanitisation.

This explains some of the concerns (and the article in general is a good resource on dynamic sql in MS SQL Server):
http://www.sommarskog.se/dynamic_sql.html#SQL_injection

I'm not suggesting the user would bring in security issues, but these kind of messy tasks (much uses of eval), that his new management are bringing in are typical changes that introduce security issues, as they are often poorly researched quick fixes.

Galtee

ChRoMe wrote: »

Variables not being escaped could lead to sql injection attacks, is how it appears to me.

Oh I see. and how would you inject the SQL?

ChRoMe

Galtee wrote: »

Oh I see. and how would you inject the SQL?

Dirty variables being inserted into the query dynamically.

Galtee

ChRoMe wrote: »

Dirty variables being inserted into the query dynamically.

Oh, OK, I was under the impression that parameters were not evaluated as part of the SQL ie they were treated as data.

** My whole world is crumbling**

Procasinator

Galtee wrote: »

Oh, OK, I was under the impression that parameters were not evaluated as part of the SQL ie they were treated as data.

** My whole world is crumbling**

If they were stored procedures, they would be treated as data generally (depending on how they are called).

Dynamic SQL pretty much means executing the result string concatenation. If the values originate from users, security vulnerabilities could arise. It's usually how SQL injection appears in the first place.

The point I was trying to get at is that often people think about sanitising/stopping SQL injection in code, which is usually where it's handled. But when you introduce things like dynamic SQL, you might accidentally introduce a new attack surface.

Galtee

Procasinator wrote: »

If they were stored procedures, they would be treated as data generally (depending on how they are called).

Dynamic SQL pretty much means executing the result string concatenation. If the values originate from users, security vulnerabilities could arise. It's usually how SQL injection appears in the first place.

The point I was trying to get at is that often people think about sanitising/stopping SQL injection in code, which is usually where it's handled. But when you introduce things like dynamic SQL, you might accidentally introduce a new attack surface.

Now I see. excellent point.