Dynamic query based of business rules

  • Lidou123

    Hall of Fame

    Points: 3052

    I hope you are well. I need your help and advice on a repetitive task that I would like to automate. Every month my manager asks me to run a series of SQL queries based on the same table and columns. I thought I could create a rules table that would allow him to create his own queries So I created this rules table like this:RuleTable

    Based on this table I would like to successively execute 3 queries.

    The first query is for the rule '+NET 3 or More' and the query is like that:

     SELECT * FROM Table Where    
    ([QuestionCode] = 'HSZ' AND ResponseCode = 3)
    OR ([QuestionCode] = 'HSZ' AND ResponseCode = 4)

    The Second Query is for the Rule 'Age Between 25-35' and the query is like that:

    SELECT * FROM Table Where    
    ([QuestionCode] = 'RS2' AND ResponseCode >= 25)
    AND ([QuestionCode] = 'RS2' AND ResponseCode < 35)

    The 3rd query is for the rule 'CHN' and 'HSZ' and the query is like that:

    SELECT * FROM Table Where    
    ([QuestionCode] = 'CHN' AND ResponseCode = 5)
    AND ([QuestionCode] = 'HSZ' AND ResponseCode = 1)

    I would like your opinion on this solution and especially have your help to create this dynamic query Here is the script to create the rule table:

        WITH CTE AS 

    (
    SELECT RuleId = 1
    , NetQuestionCode = '+NET 3 or More'
    , QuestionCategory = 'HSZ'
    , QuestionCode = 'HSZ'
    , ResponseOperator = '='
    , ResponseCode = '3'
    , RuleOrder = '1'
    , CategoryRule = 'OR'

    UNION ALL
    SELECT RuleId = 1
    , NetQuestionCode = '+NET 3 or More'
    , QuestionCategory = 'HSZ'
    , QuestionCode = 'HSZ'
    ,ResponseOperator = '='
    , ResponseCode = '4'
    , RuleOrder = '2'
    , CategoryRule = 'OR'

    UNION ALL
    SELECT RuleId = 2
    , NetQuestionCode = 'Age Between 25-35'
    , QuestionCategory = 'RS2'
    , QuestionCode = 'RS2'
    ,ResponseOperator = '>='
    , ResponseCode = '25'
    , RuleOrder = '1'
    , CategoryRule = 'and'

    UNION ALL
    SELECT RuleId = 2
    , NetQuestionCode = 'Age Between 25-35'
    , QuestionCategory = 'RS2'
    , QuestionCode = 'RS2'
    ,ResponseOperator = '<'
    , ResponseCode = '35'
    , RuleOrder = '2'
    , CategoryRule = 'and'

    UNION ALL
    SELECT RuleId = 3
    , NetQuestionCode = 'CHN AND HSZ'
    , QuestionCategory = 'CHN'
    , QuestionCode = 'CHN'
    ,ResponseOperator = '='
    , ResponseCode = '5'
    , RuleOrder = '1'
    , CategoryRule = 'and'

    UNION ALL
    SELECT RuleId = 3
    , NetQuestionCode = 'CHN AND HSZ'
    , QuestionCategory = 'CHN'
    , QuestionCode = 'HSZ'
    ,ResponseOperator = '='
    , ResponseCode = '1'
    , RuleOrder = '2'
    , CategoryRule = 'and'
    )

    SELECT *
    Into [dbo].[Rules_Parameters]
    FROM CTE

    Thank you for your help.

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 22914

    This may not be the most elegant solution, but I believe it should work for you.  Just need to un-comment the EXEC(@SQLCMD) line:

    DECLARE @SQLCMD VARCHAR(MAX) = '';
    DECLARE @ruleID INT
    DECLARE @ruleOrder INT
    DECLARE @MaxRule INT
    DECLARE @trimmedQuery VARCHAR(MAX)

    DECLARE curseWords CURSOR LOCAL FAST_FORWARD FOR
    WITH CTE AS (
    SELECT RuleID, RuleOrder, CategoryRule, MAX(RuleOrder) OVER (PARTITION BY RuleID) AS MaxRule, 'SELECT * FROM Table Where ([QuestionCode] = ''' + [QuestionCode] + ''' AND ResponseCode ' + [CTE].[ResponseOperator] + ' ' + [CTE].[ResponseCode] + ') ' + [CTE].[CategoryRule] AS Query
    FROM [dbo].[RulesParameters]
    ),
    CTE2 AS (
    SELECT RuleOrder,MaxRule,RuleID, CASE WHEN MaxRule = 1 THEN LEFT(Query,LEN(Query)-LEN(CategoryRule)) WHEN RuleOrder = MaxRule THEN RIGHT(LEFT(Query,LEN(Query)-LEN(CategoryRule)-1),LEN(Query)-LEN(CategoryRule)-26) ELSE Query END AS TrimmedQuery
    FROM CTE
    )
    SELECT RuleOrder, MaxRule, TrimmedQuery FROM CTE2
    ORDER BY ruleid, [CTE2].[RuleOrder]
    OPEN [curseWords]
    FETCH NEXT FROM [curseWords] INTO @ruleOrder, @MaxRule, @trimmedQuery
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    IF (@RuleOrder = @MaxRule)
    BEGIN
    SELECT @SQLCMD = @sqlCMD + @trimmedQuery
    PRINT @SQLCMD
    --EXEC(@SQLCMD)
    SELECT @SQLCMD = ''
    END
    ELSE
    BEGIN
    SELECT @SQLCMD = @SQLCMD + @trimmedQuery
    END
    FETCH NEXT FROM [curseWords] INTO @ruleOrder, @MaxRule, @trimmedQuery
    END

    CLOSE [curseWords]
    DEALLOCATE [curseWords]

    Could probably do this with fewer CTE's, but I like to validate things as I go.  I see little things that could be optimized (such as building the query in CTE2 instead of CTE and just using CTE to get the MaxRule OR doing everything inside a single CTE), but I also like readable code and I feel that this is fairly readable.  What I would recommend with this for testing it is to run it as is but with your data set and with a few little tweaks.  Tweaks that will be required:

    1 - replace "Table" with the name of your table

    2 - Adjust CTE2 to subtract the length of your table name instead of the hard-coded 26 (21 + length of table name).

    3 - After validating that the PRINTed statements are what you expect, comment out the "PRINT @SQLCMD" and uncomment out "--EXEC(@SQLCMD)

    There may be a more elegant way to do this, but I was trying to make it work with the data set provided as well as allowing for more than 2 rule orders.  The above should allow for any max rule order including 1, as long as @SQLCMD doesn't exceed 8000 characters.

     

  • Lidou123

    Hall of Fame

    Points: 3052

    Hello

    Thank u for your help.

    I'll test it tomorrow.

     

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 22914

    I should add that there is a little bit of risk with SQL injection depending on what is in the code.

    For example, the Query that is being executed is:

    SELECT * FROM Table Where ([QuestionCode] = ' + [QuestionCode] + ' AND ResponseCode ' + [CTE].[ResponseOperator] + ' ' + [CTE].[ResponseCode] + ') ' + [CTE].[CategoryRule]

    So, lets say your QuestionCode column contains the text:

    [QuestionCode]); DELETE FROM Table; --

    That will delete all of the data from Table (presuming they have permissions) and will ignore the rest of the code.  You do need to be careful with dynamic SQL...

  • Lidou123

    Hall of Fame

    Points: 3052

    Hello

    Thanks a lot for your help

    I am in the process of testing the query.

    I've made some changes, including the addition of "and" for the various clauses of where.

    Concerning the risk of injunction in the SQL code, I'm thinking of putting a right which prevents deleting the data from the database.

    What do you think?

  • Mr. Brian Gale

    SSC-Insane

    Points: 22914

    Putting in the restriction on delete isn't a bad idea, but depending on the permissions the user is granted, it may not be enough.  what if they drop that trigger?  Or alter permissions? or drop data?

    Or assuming they lack those permissions, they could select data from other tables, they could potentially insert or delete data... SQL injection can be quite dangerous.  But if you can restrict who can run that query or who can update the tables that handle that query, you should be pretty safe.  Or if you reduce the size of the data that can go into a column, you should be safe too.  It is hard (but not impossible) to do any dangerous SQL with a CHAR(3) column.  I picked CHAR(3) as that was the largest size needed in your sample data to build the dynamic SQL.

  • Jason A. Long

    SSC-Insane

    Points: 23650

    Please don't take this the wrong way, but looks like the SQL equivalent of a Rube Goldberg devise.

    The easiest solution would be to simply install SSMS on the managers laptop and all you need to do is create the basic queries in one or more .sql files that he can execute when he needs new data.

    If your manager has any kind of brain at all, it shouldn't take more than 5 minutes to explain how the WHERE clause works and how to modify it to based on their specific needs.

    If you're doing this simply to see if you can pull it off, go for it... but... I would suggest that you do not subject your manager to it.

    Just my two cents...

  • Jeff Moden

    SSC Guru

    Points: 996645

    Lidou123 wrote:

    Concerning the risk of injunction in the SQL code, I'm thinking of putting a right which prevents deleting the data from the database. What do you think?

     

    Totally inadequate.   Either you prevent it all or you don't.  It's best to avoid any kind of concatenation of user input.  It that cannot be avoided, then the inputs must be thoroughly deloused by doing checks against tables and several checks for symptomatic code.

    And remember that the goal of someone using dynamic SQL usually isn't to delete some data... it's normally to take some data as a part of a payload or to inject something allowed by lower privs and then have someone with higher privs unknowingly run the injected code.

    You just can't afford to screw around with supposed "good enough" code here.  It has to be 100% air tight.

    --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.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply