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 12»»

Is sp_executesql reliable? Expand / Collapse
Author
Message
Posted Monday, December 16, 2013 5:16 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, October 12, 2014 6:53 AM
Points: 64, Visits: 289
Hello.
I have a bad feeling about sp_executesql, I think in some particular situations it modify itself.
I mean if we have a select stored procedure for dynamic table name like this:

    CREATE PROCEDURE SP @TableName AS SYSNAME AS
BEGIN
DECLARE @sql AS NVARCHAR(MAX)
SET @sql = N'Select something from ' + QUOTENAME(@TableName) + ' where 1=1'
EXEC sp_executesql @sql
END

I think after we pass something to @TableName parameter, in a particular situation its possible our stored procedure modify itself to something like this:

Select something from tableName where 1=1

And next time we want to pass something to @TableName, it will throw an exception that @TableName parameter not found.

I found something like this in my program and im not sure if my theory about sp_executesql modify itself randomly is correct or not.
Thank you for help.


___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1523167
Posted Monday, December 16, 2013 5:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 2,397, Visits: 2,936
A stored procedure cannot be changed without the ALTER PROCEDURE statement. Your statement the stored procedure "has changed itself" indicates someone executed code to drop/recreate or alter the existing stored procedure.You can look in SSMS at the properties of a stored procedure to see the date the procedure was created. This could help you when the modification has been done with a DROP/CREATE statement.
If this happens without your knowledge and you want logging when it happens consider creating a DDL trigger on the database.


** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1523171
Posted Monday, December 16, 2013 5:39 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, October 12, 2014 6:53 AM
Points: 64, Visits: 289
Much appreciated, I think you've gave me enough clue.

___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1523174
Posted Monday, December 16, 2013 6:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 13,890, Visits: 28,285
As you have it currently configured, you're right not to trust it. You're completely open to SQL Injection. For a good laugh, and a good education, see to Bobby Tables. As you're using it, you're begging for trouble. sp_executesql does support parameters, but, you won't be able to just pass it table names like that. If you really want to do that (and I'm convinced it's a poor choice), you need to first do some type of check against the parameter values to ensure that what is passed is actually a real table name. Check in sys.objects or something. Just don't leave it raw like that.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1523189
Posted Monday, December 16, 2013 7:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:54 PM
Points: 1,430, Visits: 3,229
Grant is right on. Your use of sp_executeSQL is superfluous in terms of preventing injection the way you have implemented it. Read BOL on sp_executeSQL and learn how it works. It is always important to do as much parameter validation at the application level as possible even when using sp_executesql. Parameter validation inside stored procedures is also valid.






The probability of survival is inversely proportional to the angle of arrival.
Post #1523216
Posted Tuesday, December 24, 2013 3:10 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, October 12, 2014 6:53 AM
Points: 64, Visits: 289
Much appreciated, Ive read from somewhere if I use QUOTENAME(@FieldName,'''') with 4 single quotations, it makes stored procedure bullet proof from sql injection, but Im still in doubt if its adequate.

According to documentations QUOTENAME('Syntax-Example','''') produce 'Syntax-Example' out put.
But documentation didnt described how QUOTENAME('Syntax-Example','''') produce 'Syntax-Example' out put.
Is there any logic behind this? Or its just a rule?


___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1525717
Posted Tuesday, December 24, 2013 3:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:35 AM
Points: 6,733, Visits: 8,485
masoudk1990 (12/24/2013)
Much appreciated, Ive read from somewhere if I use QUOTENAME(@FieldName,'''') with 4 single quotations, it makes stored procedure bullet proof from sql injection, but Im still in doubt if its adequate.

According to documentations QUOTENAME('Syntax-Example','''') produce 'Syntax-Example' out put.
But documentation didnt described how QUOTENAME('Syntax-Example','''') produce 'Syntax-Example' out put.
Is there any logic behind this? Or its just a rule?


with regards to sql injection there is no such thing a bullet proof dynamic sql, unless you really build testing logic to intercept potential abuse !

Anyone anticipation your quotename can just anticipate your quote-char "* ; <put malicious code here> --" and you're *** [clipped on purpose]

http://technet.microsoft.com/en-us/library/ms176114.aspx


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1525720
Posted Tuesday, December 24, 2013 3:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 13,890, Visits: 28,285
masoudk1990 (12/24/2013)
Much appreciated, Ive read from somewhere if I use QUOTENAME(@FieldName,'''') with 4 single quotations, it makes stored procedure bullet proof from sql injection, but Im still in doubt if its adequate.

According to documentations QUOTENAME('Syntax-Example','''') produce 'Syntax-Example' out put.
But documentation didnt described how QUOTENAME('Syntax-Example','''') produce 'Syntax-Example' out put.
Is there any logic behind this? Or its just a rule?


There are several things you can do to prevent SQL Injection. First, don't use dynamic T-SQL at all. Only use straight T-SQL and parameters. That code can't be modified by values within the parameters. Second, if you must use dynamic T-SQL, only use parameters as parameters in the same way you would with standard T-SQL. There are examples here in the Books Online. Finally, as has been said, put in logic checks to completely sanitize your inputs if you have to dynamically build T-SQL, especially if you're going to go down the dangerous route of passing in table names as values.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1525724
Posted Tuesday, December 24, 2013 6:08 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, October 12, 2014 6:53 AM
Points: 64, Visits: 289
Thanks Grant Fritchey, Ill take a look at that.

According to this:
http://technet.microsoft.com/en-us/library/ms176114.aspx

Now to add to my troubles:
how QUOTENAME('abc[]def') produce [abc[]]def] ?

Well, Its a new question. Ill ask it in a new thread, thank you every one.


___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1525738
Posted Tuesday, December 24, 2013 11:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:38 PM
Points: 35,371, Visits: 31,912
Ah... while I agree with being paranoid about the use of dynamic SQL, it should not be something that keeps you from using it when necessary. It's an incredibly powerful tool that can do things not otherwise possible but, as the others have stated, must be made to completely and absolutely prevent SQL Injection attempts.

For the code given in the original post, the content of @TableName should be verified by checking sys.objects to see if the table name actually exists and THAT can certainly be done without dynamic SQL. If the table name doesn't exist, the exit the stored procedure without giving any clue as to what the problem is so that a potential attacker isn't given any clues.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1525796
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse