Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Server Dynamic Procedure Design

Posted on 2010-01-04
10
Medium Priority
?
249 Views
Last Modified: 2012-05-08
I'm tasked with transforming inline dynamic SQL into a SQL stored procedure.  The object is to eliminate SQL injection potential from the application.  I am aware of the 'sp_executesql...' procedure but still need a good way to dymanically construct it's string.  The problem is I need to build the query using dynamic 'Field' + 'Comparison' (=,<,>) + 'FieldValue'.  'sp_executesql' only allows 'FieldValue' to be inserted.  How do I achieve this with 'Field' and 'Comparision'?
0
Comment
Question by:Phil5780
  • 5
  • 4
10 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 26176902
>>The problem is I need to build the query using dynamic 'Field' + 'Comparison' (=,<,>) + 'FieldValue'.  'sp_executesql' only allows 'FieldValue' to be inserted.

Huh?  Why don't you give a real description of what you want your dynamic SQL to do and I can help you.
0
 

Author Comment

by:Phil5780
ID: 26176917
The following code will not function dynamically when '@SearchField' and '@SearchCondition' are encountered.  How would a CASE statement best handle this?
ALTER PROCEDURE [dbo].[pDynamicSQLTest]
@SearchField VARCHAR(100) = '',
@SearchCondition VARCHAR(5) = '',
@SearchValue VARCHAR(100) = ''

AS
BEGIN

	SET NOCOUNT ON
	
	DECLARE @strSQL NVARCHAR(MAX)
	SET @strSQL = ''
	SET @strSQL = @strSQL + ' SELECT * FROM MyTable ' 
				    
	IF ((@SearchField <> '') AND (@SearchCondition <> ''))
	BEGIN
		SET @strSQL = @strSQL + ' WHERE @SearchField @SearchCondition @SearchValue '
	END
	
	PRINT @strSQL
	EXECUTE sp_executesql @strSQL, N'@SearchField VARCHAR(100), @SearchCondition VARCHAR(5), @SearchValue VARCHAR(100)', @SearchField, @SearchCondition, @SearchValue
	
END

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26176923
SET @strSQL = @strSQL + ' WHERE ' + @SearchField + ' ' + @SearchCondition + ' ' + @SearchValue
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 26176930
then you would just do this:

execute sp_executesql @strSQL

you don't have to pass in the params that way....you could have done it this way too:

SET @strSQL = @strSQL + ' WHERE ' + @SearchField + ' ' + @SearchCondition + ' @SearchValue '

execute sp_executesql
@strSQL,
'@searchvalue nvarchar(1000)',
@searchvalue
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26176943
>>The object is to eliminate SQL injection potential from the application. <<
Then do it right and do not use ANY dynamic SQL even in Stored Procedures.
0
 

Author Closing Comment

by:Phil5780
ID: 31672729
Ok, perfect.  That beats CASE statements any day of the week.
0
 

Author Comment

by:Phil5780
ID: 26176951
Without dynamic SQL how would a custom search be possible?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26176956
custom search is really a difficult beast to tackle without dynamic sql....but, however as acperkins as indicated...it is usually frowned upon.  i typically only use it in a situation where I have to do dynamic searches...and it can be made more secure by storing your joins/tables/fields in a table structure that you can pull when needed.  
0
 

Author Comment

by:Phil5780
ID: 26176969
High performance is also highly important.  Is 'sp_executesql' going to take longer that a 'joins/tables/fields' solution?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 26176981
no, sp_executesql would still need to be used even when you pull the majority of the dynamic sql from the table

also, sp_executesql caches execution plans the same way other procs do...if you want great performance then write it so that it is.  :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Considering today’s continual security threats, which affect Information technology networks and systems worldwide, it is very important to practice basic security awareness. A normal system user can secure himself or herself by following these simp…
With more and more companies allowing their employees to work remotely, it begs the question: What are some of the security risks involved with remote employees and what actions should we take to secure them?
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question