SQLTeam.com | Weblogs | Forums

Dynamic SQL?

sql2008

#1

Haven't been on this site for a while - this looks good. Now on to my question:

I'm working on a project that allows end-users to create boolean searches. Ex:
Select * From table
where (position = Programmer or position = Customer Service) AND (Dept = IT or Dept = Finance) AND etc.
There are about 20 different choices (ex position, dept, hire date, etc), with multiple selections for each choice. I've been searching on the internet for a good way to do this - but haven't found anything that doesn't look really messy and complicated. I'm very tempted to just write this as an inline query, since it simplifies the whole thing. BTW - this is an internal app, using windows authentication/authorization and going through ssl, so I don't think security is as big of an issue if I do it inline.
Any feedback and/or suggestions would be greatly appreciated. I know ppl don't like using inline - but I don't see much difference between doing an inline query and dynamic sql, as far as security goes. The db will be a small db as far as amount of data (less than 1 million rows).
Thanks


#2

My suggestion would be to have the application level produce the necessary SQL statement. Security does become the issue. If the end-user's choices are limited by lists (e.g. Dept has a drop down list of values to pick from) then you should be okay. If the end-user can enter free form text for anything, you'll need to guard against SQL injection attacks. Using sp_executesql to actually run the generated SQL statement will help.


#3

Yes - the choices are lists that they can choose from, other than date values which I will validate against prior to executing the query. I just want to be sure I'm not missing something - or that there is a technique that would accomplish what I need securely in sql server. Other than dynamic sql, I don't know of any - which would have the same security risks anyway. Trying to write sp for all these possible queries would be ridiculous.. I think?


#4

At a time,how many choices does the user is able to choose.I mean to say that is there any restriction that end user has to select at least a selection for each and every choice or may or may not select a choice at all?


#5

We do this using sp_ExecuteSQL

DECLARE @strWhere nvarchar(MAX), @strSelect nvarchar(MAX), @strSQL nvarchar(MAX)
--
SELECT @strWhere = 'WHERE 1=1' 
       + CASE WHEN @MyPositionParam IS NULL THEN '' ELSE ' AND [Position] = @MyPositionParam' END
       + CASE WHEN @MyDeptParam IS NULL THEN '' ELSE ' AND [Dept] = @MyDeptParam' END
       ...
SELECT @strSelect = 'SELECT ' + @UserColumnListParameter -- Presuming columns provided by end user from a picklist
--
SELECT @strSQL = @strSelect + ' FROM dbo.MyTable1 AS T1 JOIN dbo.MyTable2 AS T2 ON T2.SomeID = T1.SomeID
--
EXEC sp_ExecuteSQL @strSQL,
                   N'@MyPositionParam varchar(999), @MyDeptParam varchar(999)',
                   @MyPositionParam = @MyPositionParam,
                   @MyDeptParam = @MyDeptParam

The benefit of this is:
@MyPositionParam is past to sp_ExecuteSQL (and to the SProc that calls it, if that is relevant) as a parameter using a native SQL datatype. I don't have to worry about SQL Injection, or embedded Single Quotes, or illegal string-dates etc. mucking up the syntax
My WHERE clause only contains phrases that are relevant to the query. I don't have any:

WHERE     (@MyPositionParam IS NULL OR [Position] = @MyPositionParam)
      AND (@MyDeptParam IS NULL OR [Dept] = @MyDeptParam)

which will almost certainly result in a Table Scan. My relevant-column-specific WHERE clause will be optimal for SQL to choose appropriate indexes etc.

Using sp_ExecuteSQL like this will cause the Query Optimisers to cache the query plan for the exact contents of @strSQL. Because I have used

AND [Position] = @MyPositionParam

and in particular I have not used actual values like:

AND [Position] = 'FooBar'

then there is a good chance that some/many of the users' actual queries get cached - for sure the popular ones will.

For security I can wrap this is SProcs which the user has EXECUTE permission on. Still need permission on the underlying table for the dynamic SQL, but if that that is an issue you can have the SProc EXECUTE AS to impersonate a specific permission user/role.

(My only experience with sp_ExectueSQL is via SProcs, but I expect you can do some sort of EXECUTE AS direct from an APP if you want to execute the SQL direct without an SProc wrapper. The key is to use sp_ExecuteSQL to get reuse of cached query plans)


#6

They can choose just 1 option or they can have up to 25 more. In each of the 25 categories, there are anywhere from 2 to 30 choices


#7

You mean to say that for each category definitely there will be a default choice selected.?


#8

No - there will not be a default choice. For example, in the Position category, end-user can elect to not use Position in their query. If they do decide to use it, the Position category list contains something like 50 different positions


#9

This is a good starting point. I will have to play around with it to figure out when to use the AND, since they may or may not, as in my example, use position at all. If sp_ExecuteSQL actually caches the sp is good, since end user also has option to "Save" their queries to use again, albeit with different date ranges.

Thanks


#10

Also make sure to read this to understand more about Dynamic SQL www.sommarskog.se/dynamic_sql.html


#11

Hope this work for you since it worked out for me

Declare @aSelectClause nvarchar(max),
Declare @CONDITION nvarchar(max)

SET @aSelectClause =' Select * From table '
SET @CONDITION=' '

-- case for position category and here @position contains choices for position category separated by comma
IF(@position IS NOT NULL AND LEN(@position ) > 0 )
SET @CONDITION='(position IN ( ' + CAST(@position AS NVARCHAR(MAX)) + ') )'

IF(@Dept IS NOT NULL AND LEN(@Dept ) > 0)
IF(LEN(@CONDITION) > 0)
SET @CONDITION = @CONDITION + ' AND ( Dept IN ( ' + CAST(@Dept AS NVARCHAR(MAX)) + ') ) '
ELSE
SET @CONDITION = @CONDITION + ' ( Dept IN (' + CAST(@Dept AS NVARCHAR(MAX)) + ')) '

Similarly add cases for each category as shown above

IF(LEN(@CONDITION) > 0)
SET @aSelectClause =@aSelectClause + ' WHERE '+ CAST(@CONDITION AS NVARCHAR(MAX))

EXEC sp_executesql @aSelectClause


#12

Thanks - I will play around with this today too.


#13

One more thing forgot to mention declare optional parameters i.e., parameter for each category


#14

This will suffer from any embedded single-quotes (i.e. there is a risk of both Syntax Errors and SQL Injection). For example if a user put this into the @Position parameter:

'x')); DROP DATABASE xxx; --

then the actual SQL that would be executed would be:

Select * From table  WHERE (position IN ( 'x')); DROP DATABASE xxx; --) )

The first part might, or might not, select some rows ... but the DROP DATABASE command is then capable of executing :frowning:

To get re-use of the cached query plan the call to sp_ExecuteSQL needs to both a) contain paramaters and b) have a list of those parameters


#15

Thanks Kristen - I really appreciate your help


#16

Yes, sql injection security is very important. But the login running the application should never have the authority to issue a DROP DATABASE or DROP TABLE or DROP anything for that matter.


#17

Kristen:
I have a question?
ann1 mentioned that choices are lists which means selecting a value(s) from list so there is no scope for end user to type any text in selections.
So how could be any sql injection if there are no text fields.?


#18

Sure, I did note that the intention was to constrain the parameters to picklists in the APP, but I personally wouldn't regard that as safe. Many APPs these days are web pages and such parameters are transmitted using HTTP so its easy enough just to hand-edit the GET parameter list.

Either way, my example was dramatised. DELETE or UPDATE will be just as damaging.
Much more likely issues are Single Quotes and data type parsing issues and they are solved by using parametrised queries, which also has the benefit that sp_ExecuteSQL can usefully cache the query plan.


#19

Kristen:
Thank you kristen you have opened my eyes i thought there is only one possible case for sql injection
never thought of tampering HTTP request object.
Thanks a milion. :grinning:


#20

Given that this is an internal app, using windows authentication/authorization and this actual "search" page is also limited to a subset of the app users, permission levels of the db AND the company uses IDS/IPS to inspect packets prior to any DB calls - I would rate this as low as a security risk. In this scenario, I think the solution is appropriate. This is certainly not the first, nor will it be the last app that has to weigh in on the business needs vs security.