Nesting Stored Procedures
-
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
Billing and OSS Specialist - SQL Programmer
MCL Systems -
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.
-
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
Billing and OSS Specialist - SQL Programmer
MCL Systems -
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
Billing and OSS Specialist - SQL Programmer
MCL Systems -
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
Billing and OSS Specialist - SQL Programmer
MCL Systems -
rmarda
nest level exceeds 32 that only I wanted to point out ,is there an y explanation from ms,
I am having a script to convert figures to words using the same concept I will pulish it later ,thak ye rmarda
with
love john
-
I don't know that Microsoft has explained why you can only nest to nest level 32. I suspect they wanted to set some limit otherwise you could just have a stored procedure call itself and it would never stop. My guess is that an SP calling itself to infinity would cause other problems that would be worse than simply limiting the nest level to 32 and giving you an error if you exceed it.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
one good trick i know of is related to validation of arguments...
let's say you have some procs which are called by the GUI, so you need to do some validation of the rubbish the users type into the system... These procs call other procs (which are not used by the GUI)... in these internal procs you have a couple of lines like this
if @@nestlevel = 1 begin -- raise an error in your normal way... end
Which means that no-one can call your code from an ODBC connection through excel or access or whatever Strictly speaking you don't even need to grant exec permissions to these "internal" procs because all the tables are owned by dbo (just like the calling procs) and as an added benefit you can totally trust the arguments you receive because they come from other code in the database...
cheers,
ste
-
I used nested procedures a lot to reduce server round-trips. If I am loading a .NET DataSet with several tables to be loaded I will call a single procedure that, in turn, EXECUTEs all the other procedures I need to load those tables.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
Jeaux wrote: "My question is why couldn't you rewrite the example..." I have a different answer to this question. I once had a stored procedure with a similar structure to the one you gave as an example. The problem was that (if I understand correctly) SQL Server comes up with an optimized plan for the first query it runs the first time it compiles.
For example: Let's say SQL Server first compiles and optimizes based on the first query. Later a user runs the proc and the IF takes runs the second query. Now it is possible that the second query runs like a turtle-sooo sloowwwwww.
That's the exact problem I had once, and it almost brought my server down. I had a bunch of users running the same proc but hitting one of 5 queries that weren't optimized. I turned it into one master proc calling 6 different baby procs. Voila! Problem solved. It had nothing to do with writing clean code. It was a matter of optimization. Fair disclosure (but any bad explanations are mine): I got this trick off of Kimberly Tripp's site.
-
Just finished reading the article on nested SPs, and it raised a question about a practice I have been following for a few years now.
I write my SPs for one Web application in a common SP with the CommonSP;1, CommonSP;2 etc... and this helps me organize my work by application.
I am wondering if Robert Marda, or others have any thoughts about the wisdom, or not, of this practice.
David
-
I like the way you code, Robert, looking forward to seeing more articles.
Signature is NULL
-
Robert,
It might be interesting in a follow-up article to consider the implications and pitfalls of explicit transactions in nested stored procedures.
David Rueter
-
Can someone talk about transactions a little? If transaction control resides in the app tier, would this approach increase transactional locking that may be uneeded? Like the article, thanks.
-
I am glad you have liked my article. Please forgive me for being slow to reply to your comments. I have been busy with many personal matters but hope to be able to write more articles soon. Thank you for your ideas, I will keep them in mind.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply