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


Nesting Stored Procedures


Nesting Stored Procedures

Author
Message
Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 82
Frank (A.K.A. a5xo3z1):

The major danger I am aware of from sql injection attacks via data entered by a user and used in a stored procedure is when you dynamically build your queries and then execute them. The best way I know to completely eliminate this type of injection is to use the following code on your character datatypes: SET @LastName = REPLACE (@LastName, '''', '''''')

That code ensures that all characters they enter will remain in the query to be compared against a column and can not execute as its own query.

Other than that I agree that all other forms of validation will be best handled before the input reaches the stored procedures.

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.



Robert W. Marda
SQL Programmer
Ipreo

David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3704 Visits: 3121
Robert,

I don't have the specific example to hand as its in an archived project library.

From what I remember I wrote code to produce a paginated list of records from a database

Basically I had a stored procedure called usp_Paginator which accepted a page number, page length and recordset identifier.

This stored procedure called another stored procedure depending on the recordset identifier which returned a recordset which I used to populate a temporary table.

I came across and instance where I wanted to say

INSERT MyTable
exec usp_Proc @Arg

The problem was that usp_Proc contained code of this type itself.

If you try and do this then SQL returns an error message warning you that you cannot nest these sorts of statements.

As with all things SQL my INSERT/Exec method was a case of AN answer rather than THE answer.

LinkedIn Profile

Newbie on www.simple-talk.com
Frank Kalis
Frank Kalis
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5971 Visits: 289
Hi Robert,

quote:

The major danger I am aware of from sql injection attacks via data entered by a user and used in a stored procedure is when you dynamically build your queries and then execute them. The best way I know to completely eliminate this type of injection is to use the following code on your character datatypes: SET @LastName = REPLACE (@LastName, '''', '''''')

That code ensures that all characters they enter will remain in the query to be compared against a column and can not execute as its own query.

Other than that I agree that all other forms of validation will be best handled before the input reaches the stored procedures.



Agreed, I'm trying to avoid dynamic SQL for other than administrative tasks and to validate as much as possible at app level. But there is still a small percentage of uncertainty to deal with.

You have to do more than just REPLACE (@LastName, '''', '''''')

I'm sure you know this articles from http://www.appsecinc.com named Manipulating Microsoft SQL Server Using SQL Injection. Or Advanced SQL Injections in SQL Server Applications by http://www.nssoftware.com.

But we're moving off-topic...

One thing that can be stated is that you can use nested procs for this reason, or? If you do so, that's another question

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
Frank Kalis
Frank Kalis
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5971 Visits: 289
Correction,

the second link should be called http://www.ngssoftware.com.

Sorry!

Cheers,

Frank

Edited by - a5xo3z1 on 05/22/2003 09:26:40 AM

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/
johncyriac
johncyriac
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 58
innovative thinking is not at all there
just try this real nesting

create proc nest_sp_dec
@kount int
as
begin
select @kount
select @kount=@kount-1

if @kount >0
exec nest_sp_dec @kount

end


and execute it with

nest_sp_dec 10

How is it dude



Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 82
I knew about the second link, however it seems to be only discussing ways to get a password for a user account. Once you have a password for an account with the permissions you need then you can simply by pass the stored procedure and do what you want.

My answer was focused at the stored procedure level (I don't always look at the big picture). Once inside a stored procedure I know of no other way to do an injection attack except the one I mentioned. You will forgive my lack of knowledge in this area, if there is another way I would love to know about it so that I can protect against it.

With that in mind I don't think that nesting stored procedures would help protect against inection attacks unless you have something other than what I thought of in mind.

Can you tell me specifically where to go at the first site to find something about injection attacks?

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.



Robert W. Marda
SQL Programmer
Ipreo

jeaux
jeaux
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 120
My question is why couldn't you rewrite the example in the article as the following:


IF @Last <> '' AND @First = ''
BEGIN
SELECT * FROM authors WHERE au_lname LIKE @Last + '%'

END
ELSE IF @First <> '' AND @Last = ''
BEGIN
SELECT * FROM authors WHERE au_fname LIKE @First + '%'
END
ELSE
BEGIN
SELECT * FROM authors WHERE au_lname LIKE @Last + '%' AND au_fname LIKE @First + '%'
END


What is the advantage here of having 3 separate stored procedures? Forgive my ignorance.



Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 82
jeaux:

With so small of queries in each SP there is no advantage I know of to use what I showed in my article over what you just posted.

The advantage comes when you have 2,000 or more lines of code for each query using complex SELECT, FROM, WHERE, and ORDER BY clauses and possibly using GROUP BY and HAVING as well. Then it can be easier to maintain to have seperate stored procedures instead of using the technique you used.

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.



Robert W. Marda
SQL Programmer
Ipreo

Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 82
Frank:

I found an article at the first link you mentioned called Manipulating Microsoft SQL Server Using SQL Injection. It focuses on using OPENROWSET and OPENDATASOURCE to execute unauthorized code on a SQL Server.

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.



Robert W. Marda
SQL Programmer
Ipreo

Robert W Marda
Robert W Marda
SSC Eights!
SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)SSC Eights! (986 reputation)

Group: General Forum Members
Points: 986 Visits: 82
johncyriac:

Nice. I added a line to see to what level nesting it did:

create proc nest_sp_dec
@kount int
as
begin
select @kount
select @kount=@kount-1

SELECT @@NESTLEVEL AS NESTLEVEL

if @kount >0
exec nest_sp_dec @kount

end

and executed it with:

nest_sp_dec 10

It showed a nest level of 10

and then with

nest_sp_dec 33

which exceeded the max nest level.


Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.



Robert W. Marda
SQL Programmer
Ipreo

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