Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Nesting Stored Procedures Expand / Collapse
Author
Message
Posted Thursday, May 22, 2003 8:58 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #62690
Posted Thursday, May 22, 2003 8:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 2,915, Visits: 1,848
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
Post #62691
Posted Thursday, May 22, 2003 9:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
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/
Post #62692
Posted Thursday, May 22, 2003 9:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 1:18 AM
Points: 5,956, Visits: 285
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/
Post #62693
Posted Thursday, May 22, 2003 9:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, October 23, 2010 4:51 AM
Points: 80, 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





Post #62694
Posted Thursday, May 22, 2003 9:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #62695
Posted Thursday, May 22, 2003 9:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 7, Visits: 87
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.




Post #62696
Posted Thursday, May 22, 2003 10:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #62697
Posted Thursday, May 22, 2003 10:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #62698
Posted Thursday, May 22, 2003 10:11 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
Post #62699
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse