0

I have question regarding adding/removing filters in SQL 2008 query. Here is example of one scenario where I have to use server side language (ColdFusion in my case) to add filter. 

<cfif userdata.appaccess lt 8>
    AND (building_list IN (<cfqueryparam value="#userdata.appBldgList#" cfsqltype="cf_sql_char" maxlength="4" list="yes">))
</cfif>

As you can see I have cfif statement that checks if user access is less than 8 then include filter in the query. I'm wondering if there is a way to do this with SQL language? If it is, would that be better or more efficient option/solution? Coding standard in my project are not consistent and code is written by many different programmers that left and every new programmer started something new. I'm trying to find the best and the most efficient solution for these kind of situations. Any suggestions/example would be helpful. Thank you.

espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • 1
    SQL Server supports `if ... else` logic - https://learn.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql Or use `case` statements - https://stackoverflow.com/q/63447/1636917 – Miguel-F Jun 11 '18 at 20:28
  • 1
    What's wrong with the current code? Though possible in SQL, it doesn't easily support the degree of dynamic-ness that CF does. *Some* operations can only be done with dynamic SQL (which if done wrong, opens a big sql injection hole). Plus dynamic SQL is harder to debug IMO. Personally I don't recommend it unless you really know what you're doing. – SOS Jun 11 '18 at 22:10

1 Answers1

0

Yes, just use a SQL "OR" condition together with the AND like this

   select...
    from...
    where 
    ...some conditions...
    and (#userdata.appaccess# >= 8 OR
    (building_list IN (<cfqueryparam value="#userdata.appBldgList#"    cfsqltype="cf_sql_char" maxlength="4" list="yes">))
    )

If userdata.appaccess is greater than or equal to 8, then SQL will ignore the list value condition. You must validate that userdata.appaccess does not contain SQL code and it only contains a numeric value before you do this because if the user can replace the number with SQL code, then you have a SQL Injection problem.

If you find this answer acceptable, please indicate.

Eccountable
  • 622
  • 3
  • 13
  • Using raw variables in SQL is dangerous unless you're 100% certain the values are clean. – SOS Jun 13 '18 at 15:41
  • Thanks :) I think they're asking how to replace the whole thing with vanilla sql (ie no cfqueryparam at all). Personally I don't see the benefit in this one case. While it's possible to handle some things with plain sql, handling multiple values (like in the example) aren't - least not without more complexity. IMO, sticking with cfml and cfqueryparam is simpler. It avoids the added complexity and some of the extra risks that come with dynamic sql. – SOS Jun 14 '18 at 15:00
  • Agreed. I thought he was mainly interested in removing the CFIF in the SQL. – Eccountable Jun 15 '18 at 00:33