How to create dynamic parameters in SQL Server stored procedure



  • A helpful example of dynamic search conditions.

    Especially if you have Irish clients, where you have lots of names like Molly O'Boyle, Patrick O'Connor and Sean O'; drop table customers--.

    [code]
    -- execution script: Execute dbo.pr_customers 'te','tx',1
    CREATE PROCEDURE dbo.Pr_customers(@firstname VARCHAR(90)=NULL,
                                      @lastname  VARCHAR(90)=NULL,
                                      @age       INT=NULL,
                                      @phone     INT=NULL)
    AS
      BEGIN
          -- declare the variables to build the string dynamically
          DECLARE @Where VARCHAR(MAX),
                  @SQL   VARCHAR(MAX)

    SET @Where = ' WHERE 1=1 '

    IF ( @firstname IS NOT NULL )
            SET @Where = @Where + ' AND firstname = ''' + @firstname + ''''

    -- make sure that you append four quotes(') for charecter parameters
          IF ( @lastname IS NOT NULL )
            SET @Where = @Where + ' AND lastname = ''' + @lastname + ''''

    IF ( @age IS NOT NULL )
            SET @Where = @Where + ' AND city = ' + CONVERT(VARCHAR, @age)

    -- for integer parameters you no need to append quotes(')
          IF ( @phone IS NOT NULL )
            SET @Where = @Where + ' AND country = ' + CONVERT(VARCHAR, @phone)

    SET @SQL = 'select * from customers ' + @Where

    EXEC (@SQL)
      END 
    [/code]



  • @Groaner said:

    -- for integer parameters you no need to append quotes(')

    Consuela quote FTW



  • @Groaner said:

    [code]
            SET @Where = @Where + ' AND city = ' + CONVERT(VARCHAR, @age)
    ...
            SET @Where = @Where + ' AND country = ' + CONVERT(VARCHAR, @phone)
    [/code]

    Huh? Of course the biggest WTF is the injection vulnerability, but city = age? country = phone?

    WTF did it do to the formatting?



  • the three backticks are code

    But you appear to have changed my reply window text size

    I think we may have stumbled across leaky formatting that we can abuse for spin to wins.

    The quote breakage is from the @Where


  • :belt_onion:

    i'm confuse.

    not sure if joking....


    Filed Under: Nevermind, I figured out if joking. Many laughs are had now.


  • @Matches said:

    the three backticks are code

    The three "backticks" are ... that I typed to indicate I snipped something. It's code because I kept the [code] markup when I quoted it.

    @Matches said:

    I think we may have stumbled across leaky formatting that we can abuse for spin to wins.

    Go for it! :)


  • BINNED

    @HardwareGeek said:

    Huh? Of course the biggest WTF is the injection vulnerability, but city = age? country = phone?

    I suspect copy pasta. Unless there is some mysterious meaning to those words not known to us mere mortals.


    Filed under: Chtulhu ftagn



  • From that page:

    "But probably not every one knows that it might leads to SQL Injection attacks.But still, one good thing about using .NET is that if you execute any sql statement from the front end then it will automatically calls the API function (sp_executesql)that rewrites the SQL statements to a parameterized query which is safe. so make sure the front-end technology which you are using has this capability to call the APIS while passing sql statements to backend."

    Can someone explain to me what this is all about?



  • I have no idea why anyone would do dynamic SQL, it is pretty much impossible to debug.



  • @lucas said:

    I have no idea why anyone would do dynamic SQL, it is pretty much impossible to debug.

    I know one guy that does many many abuses of dynamic SQL. Sadly I am not allowed to share.

    This is because he refuses to learn a programming language and therefore does everything in SQL. Even if SQL is completely the wrong tool for the job.


  • BINNED

    @lucas said:

    I have no idea why anyone would do dynamic SQL, it is pretty much impossible to debug.

    Not apologizing, asking for genuine solutions here. I'm not a DBA and have to, at least for now, deal with this myself. Which I don't like but I have no other choice.

    I have an application that allows querying data using an arbitrary number of filters. For this example, let's say there are five possible ones. All combinations of these filters are valid and have to return results.

    Additionally, some filters check for equality, some are less than, some are less than or equal, and the other way around. What is the most sane way to deal with this if not by using dynamic SQL?



  • I haven't done a lot of SQL of late but basically you can do boolean logic in the WHERE clause.

    http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

    So I think it should be written in the following way

    SELECT * FROM customers
    WHERE firstname = @firstname OR @firstname IS NULL
    AND lastname = @lastname OR @lastname IS NULL
    AND city = @age OR @age IS NULL
    AND country = @phone or @phone IS NULL
    

    Though without trying it out, I am not really sure anymore as I tend to avoid writing any SQL these days.


  • BINNED

    @lucas said:

    I haven't done a lot of SQL of late but basically you can do boolean logic in the WHERE clause.

    Which the dynamic SQL part generates, yes.

    But the problem is that it's not a constant query. Using your example, if the user selects filters for firstname and lastname the resulting query has to be something like:

    SELECT * FROM customers
    WHERE firstname = @firstname
    AND lastname = @lastname
    

    However, he can select, for example, firstname and registeredAfter, which results in:

    SELECT * FROM customers
    WHERE firstname = @firstname OR @firstname IS NULL
    AND registeredDate >= @registeredAfter
    

    Again, there are mutiple filters on each form. I think one has something like 10. If I were to create a stored procedure for every possible combination, I'd end up in the hundreds of stored procs just for one form, with more added if we add a new filter to it.



  • Well that is the point of putting the OR @myParam ISNULL into the WHERE clause.


  • BINNED

    Oh, I missed the @ while reading, whoops!

    Hmmm... will still require some black magic when it comes to combinations of extra modules that are optionally plugged in into the form, but I guess I can have the queries overridden in some way in my models.

    Will take a closer look once I'm done with current mindfuckery I'm dealing with. Thanks for the suggestion in any case, it would appear my google-foo was lacking when researching it originally.



  • I am surprised I remembered how to do it after not really doing anything too complicated with SQL in years.



  • This way of writing a multi-filter SQL Query is very nice and Works well for tables with a few hundred, or maybe a few thousand, records. Where it fails is when you have millions of records. Each "or @filterField is null" will match very record and Thus will result in a table scan, which you don't want.
    Thus comes the dynamic SQL into living. Either you do i tthis way or you do it using temporary tables, which have their own set of problems.
    But the notes about SQL injection is valid, and you should look into doing parametrised dynamic SQL.
    Somebody mentions that sp_executeSQL parametrised vierything, but it wonøt Work when the parameters is inserted into dynamic SQL.
    So: Beware: Here be dragons.



  • @lucas said:

    I have no idea why anyone would do dynamic SQL, it is pretty much impossible to debug.

    Where I work we occassionally use dynamic SQL in some our stored procs. The main scenario comes up when accessing client databases.

    Our in-house software allows us to track shipments for our various customers. Due to contract constraints, we are required to either keep information for certain clients in separate databases or encrypt the databases at rest. When the program was first being developed, DB encryption was slow, so the decision was made to keep the client information in separate DBs. This means that when we want to retrieve data for a given shipment, we have to dynamically determine which database to pull the information from.

    This is just one example in our system. We have a few other scenarios within our environment where dynamic SQL is the best solution. As long as you properly use the dynamic SQL - and remember, dynamic SQL still allows parameterization - it can greatly improve the flexibility of some of your procs.


  • BINNED

    @abarker said:

    As long as you properly use the dynamic SQL - for example, dynamic SQL still allows parameterization - it can greatly improve the flexibility of some of your procs.

    It is a bitch to debug though.

    As an aside, I actually generate mine in PHP (I know, I know...) because MySQL's (yes, I know!) routines are pretty damn shitty. All parameters are passed through PDO's prepared statements, with preparation emulation turned off.

    I guess I should page @Arantor at this point so he can tell me if I left a gaping security hole by doing this somewhere...



  • @Onyx said:

    It is a bitch to debug though.

    It sure can be. Which is why I usually output the SQL that gets run when I use dynamic SQL.



  • If you're using PDO prepared statements you should generally be OK because that's kind of the point of using prepared statements ;)


  • BINNED

    @Arantor said:

    If you're using PDO prepared statements you should generally be OK because that's kind of the point of using prepared statements

    It's PHP and it has some kind of emulation turned on by default (unless that changed since I last read about it). You can understand that I'm still paranoid.



  • Magic quotes is only a thing in 5.3 (deprecated) and it isn't a thing any more in 5.4+. But provided you're using prepared statements properly (i.e. actually never letting any content in to the query directly) you're golden from that perspective.


  • BINNED

    I'll recheck tomorrow for my own peace of mind, but yes, that's what I'm doing.

    I also discovered the joys of using bindParam vs. bindValue while working on that specific bit of code. Why the fuck is there no difference in notation of references vs. values (except in function definition)? Gaaah!



  • welcome to PHP. Entry price: your sanity.



  • There are a significant number of valid, and invalid uses for dynamic sql, the primary use for my work is typically as configuration items for reporting.



  • This won't work, you'd need something more along the lines of

    where 1=1
    and isnull(@firstname, firstname) = firstname -- and this is assuming firstname can't be null. If firstname can be null, your results will disappear.



  • Personally, the way I've handled it is passing all values to the query like this:

    stored_proc(@value1, @value2, @value3) -- using real names
    declare @value1raw varchar(15)
    ...
    , @value3raw varchar(15)
    set @value1raw =@value1
    ..
    set @value3raw = @valule3

    select * from sometable where <built up dynamic sql where clause, using the predefined sql declared variables, NOT the raw variables> [IE: where firstname=@value1]

    This will more or less control the flow of the query possibilities by enforcing parameters that are generated by your app, and still parameterize unsafe user input.

    My two cents since I see this crap a lot.



  • Depending on your language, you can also use something more sane, like in C# there's a sql command builder where you can dynamically build the sql and append your parameters.

    YMMV depending on language. (The above was designed for something more like VBA)



  • @Matches said:

    This won't work, you'd need something more along the lines of

    where 1=1
    and isnull(@firstname, firstname) = firstname -- and this is assuming firstname can't be null. If firstname can be null, your results will disappear.

    And that also doesn't account for situations where your parameter defaults to some non-null value. Some new developer on your team could just blindly pass in '' instead of null when the parameter is left blank, and then your proc won't return the right values.


  • :belt_onion:

    We deal with a different type of dynamic SQL. It's not about the filters, it's about aggregations at different levels with the same output template. The fields in these queries are not user inputs. warning, php ahead
    [code]
    select $location_level, $period_type, sum(sales)
    from sales s, location_info l, period_info p
    where s.l_key = l.l_key and s.p_key = p.p_key
    group by $location_level, $period_type
    [/code]
    Note: Data Warehouse environment. Above Fields/names were highly simplified and anonymized. Extra filters like location/period predicate and/or batteries not included.


  • BINNED

    I'd still pass it through PDO and be on the safe side. Unless that was an oversimplification.


  • ♿ (Parody)

    @abarker said:

    Some new developer on your team could just blindly pass in '' instead of null when the parameter is left blank, and then your proc won't return the right values.

    Solution: Oracle.


  • BINNED

    I clicked on the heart, but that's only because it's both the only option, and also because I don't think there's a name for what I'm feeling right now so I can't blame Discourse for not including it.


  • ♿ (Parody)

    I've been working with Oracle for so long, '' not being null would no doubt cause me a world of hurt.



  • @lucas said:

    I have no idea why anyone would do dynamic SQL, it is pretty much impossible to debug.

    So is Ruby, and people write forums in it.


  • :belt_onion:

    Oversimplified, there are filters with parameters as well, and usually more data columns being aggregated. But the filters are not dynamically generated if at all possible.



  • I feel better about my SQL skills after reading this thread.



  • Yeah, the basics really aren't that hard.

    It took me about an hour of mind bending to figure out how to structure queries, after years of not writing any. (Oh, that's right, select is a monad, and join joins the layers...)

    I still need a manual/syntax reference, though.



  • @Keith said:

    Can someone explain to me what this is all about?

    Such a delicious twist, that is. The author has fallen into the trap of believing that because .NET uses sp_executesql for ad-hoc queries, the parameters are sanitized.

    All sp_executesql does is allow you to define parameters and pass values (and potentially reuse the resulting query plan) to a block of dynamic SQL. Even if the arguments are in parameters, it does absolutely nothing to protect against SQL injection if you then use those raw parameters to build dynamic sql!

    @lucas said:

    I have no idea why anyone would do dynamic SQL, it is pretty much impossible to debug.

    I have a process that constructs insert/update/delete statements to move data across databases, based on customized rules. If the queries weren't constructed dynamically, the procs that move the data would have to change every time someone added a new column to any of the tables involved. The recompilation hit is minimal in comparison to the length of the overall job, and not having the maintenance burden of all the underlying procs is quite lovely.

    Also, unless you want to do aggregation in the application itself instead of the database, it's very hard to make a dynamic crosstab report without using dynamic SQL.

    @abarker said:

    Onyx said:
    It is a bitch to debug though.

    It sure can be. Which is why I usually output the SQL that gets run when I use dynamic SQL.

    It's pretty easy to debug if you use debug prints. Typically, the hardest part is developing the code that builds the query(ies) and making sure its output is free of syntax errors and that all tokens used to build the query have been run through QUOTENAME() or similar.

    I end up writing a lot of dynamic SQL mainly because I get handed many of the DBA-level projects, but I try to avoid using it until I've exhausted Erland's numerous alternatives.


  • BINNED

    @Arantor said:

    This is because he refuses to learn a programming language and therefore does everything in SQL. Even if SQL is completely the wrong tool for the job.

    Sometimes that's because management wants it done using stored procedures and SSIS packages because the initial development is faster. Don't ask me how I know this. FML



  • That's one step above my management, usually its Microsoft Access impersonating ssis.


  • BINNED

    @Groaner said:

    It's pretty easy to debug if you use debug prints.

    I turn on query logging in MySQL and do tail -f /var/mysql/query.log because PDO doesn't let you to view the prepared query easily. Sure, you can see the SQL without values, but sometimes you could be hunting for a logical error in your SQL only to realize you tried to push a NULL into a NOT NULL column.

    Works nicely if you have a separate instance of MySQL running for testing only. If you have other stuff connecting to the instance as well.. yeah, back to var_dump it is.



  • @Groaner said:

    All sp_executesql does is allow you to define parameters and pass values (and potentially reuse the resulting query plan) to a block of dynamic SQL. Even if the arguments are in parameters, it does absolutely nothing to protect against SQL injection if you then use those raw parameters to build dynamic sql!

    Thanks for the explanation. It seemed like what they were suggesting would be pretty much impossible unless the compiler was doing something special when building the query.



  • @Groaner said:

    Such a delicious twist, that is. The author has fallen into the trap of believing that because .NET uses sp_executesql for ad-hoc queries, the parameters are sanitized.

    All sp_executesql does is allow you to define parameters and pass values (and potentially reuse the resulting query plan) to a block of dynamic SQL. Even if the arguments are in parameters, it does absolutely nothing to protect against SQL injection if you then use those raw parameters to build dynamic sql!

    Are you sure about that? The online docs on this are a mess, but the few articles I found seem to suggest that passing parameters into a dynamic SQL using sp_executesql should be safe.

    But then, you have stuff like this:

    So confused...



  • I've only ever seen it misused. Maybe I shouldn't have made such a blasé statements, but I am certainly of the opinion that it should be avoided. I don't tend to do a lot SQL these days and usually just choose some decentish ORM library to do the hardwork for me.


  • ♿ (Parody)

    @Onyx said:

    I turn on query logging in MySQL

    Hibernate has an option to log every query it runs. This is also handy in figuring out when you need to beat it into submission with different annotations in your data model.


  • ♿ (Parody)

    @lucas said:

    some decentish ORM library to do the hardwork for me.

    What do you choose? I like to pass my work off too, but scaling and complexity often push me back into the realm of native SQL.



  • @Groaner said:

    .NET uses sp_executesql for ad-hoc queries

    Bzzzt! .NET does no such thing. Go on, google it. I'll wait.

    Now, as google has told you, sp_executesql is a database engine stored procedure in SQL Server. Still a Microsoft product, but not .NET.

    @Groaner said:

    All sp_executesql does is allow you to define parameters and pass values (and potentially reuse the resulting query plan) to a block of dynamic SQL. Even if the arguments are in parameters, it does absolutely nothing to protect against SQL injection if you then use those raw parameters to build dynamic sql!

    Still not quite right. If you build dynamic, parameterized SQL and pass it to sp_executesql, you get the same benefits as running a parameterized SQL query anywhere else, namely: prevention of SQL injection. Of course, this assumes that you are properly parameterizing your dynamic SQL instead of just dropping the parameter values into your SQL string.

    @Groaner said:

    Typically, the hardest part is developing the code that builds the query(ies) and making sure its output is free of syntax errors and that all tokens used to build the query have been run through QUOTENAME() or similar.

    You don't need QUOTENAME(). Just parameterize the SQL:

    CREATE PROCEDURE usp_Sample_Proc (@procInput  varchar(25))
    
    AS
    
    DEFINE @SQL     nvarchar(150)
    DEFINE @Params  nvarchar(50)
    
    SET @SQL = 'SELECT ID, Col1 FROM Table WHERE Col2 = @filter'
    
    SET @Params = '@filter varchar(25)'
    
    EXEC sp_executesql @SQL, @Params, @filter = @procInput
    
    GO
    

    Now, obviously, this is a very simplistic scenario that doesn't really need dynamic SQL, but it demonstrates my points. You can even pass 'DROP DATABASE; to this proc, and it will just return an empty table, unless you actually have a matching record.



  • @cartman82 said:

    Are you sure about that? The online docs on this are a mess, but the few articles I found seem to suggest that passing parameters into a dynamic SQL using sp_executesql should be safe.

    But then, you have stuff like this:

    So confused...

    Yes, you can use sp_executesql to help prevent injection attacks. However, if you aren't careful building your dynamic SQL, you can still leave yourself vulnerable. The example in the OP would still be vulnerable to SQL injection. However, the example in my previous post is not (I know from experience).


Log in to reply