Include Checkbox in SQL Server Where Statement only When Checked

I have a form used for searching with multiple checkboxes.  If the check box is checked I need to include it in the where clause for the search results.  If it's not checked I want to ignore it in the where clause.    
I've included the segment of the code.  Essentially I want to include the checkbox form.religiontheology variable in the where clause if the user checked it.  I'm trying to set it to null in the cfif statement if the user didn't check it and essentially ignore it in the where clause.


<cfif not isdefined("form.religiontheology")>
<cfparam name="form.religiontheology" default="">
</cfif>
<cfif not isdefined("form.math")>
<cfparam name="form.math" default="0">
</cfif>
<cfif not isdefined("form.cert1")>
<cfparam name="form.cert1" default="No">
</cfif>
<cfif not isdefined("form.catholic")>
<cfparam name="form.catholic" default="0">
</cfif>


<cfquery datasource="teacher-recruitment" name="teacher1">
Select PositionFullTime, FirstName, LastName, PDFFile1Name, PracticingCAT, GradDegree, CollegeDegree, CertTitle1
from tblTeacherMaster
where #SESSION.ThisApp.CN#='-1' and #Session.ThisApp.JT#='-1' and #SESSION.ThisApp.GD#='-1' and Status='#Session.ThisApp.Sta#' and Math='#form.math#' and CatCert1YN='#form.cert1#' and PracticingCAT='#form.catholic#' and Highestdegree='#form.dg#' and ReligionTheology=isNull('#form.religiontheology#',ReligionTheology) 
</cfquery>

Open in new window

ajd3rdAsked:
Who is Participating?
 
_agx_Commented:
The simplest way is to check the information_schema tables.  If a record is found, that column exists. Otherwise, it doesn't.  

<!--- cfqueryparam omitted for clarity--->
<cfquery ...>
SELECT       column_name
FROM       information_schema.columns
WHERE   table_name = 'YourTableNameHere'
AND     column_name = '#session.yourVariableHere#'
</cfquery>
0
 
lofCommented:
Hi ajd3rd,

I don't know much about cold fusion itself but that's database problem to me.

I assume that cold fusion set's value to 0 when the checkbox is not checked  and to 1 when it's checked.
Now for each optional query I'd do something like that

and (0='#form.catholic#' or PracticingCAT='#form.catholic#')

instead of

and PracticingCAT='#form.catholic#'

If the checkbox is not checked and it's value = 0 then regardless of the second part of the condition (your original one) will be ignored. However if it is one, the result of the condition will be depending solely on the original condition.
0
 
ajd3rdAuthor Commented:
I had tried that orignally but then it will included only people who do not have that checkbox checked in the database.  Actually I think I just figured it out.  I used a cfif statement withing the where clause and it seems to be working now.

THanks for your response.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
lofCommented:
It would work have a look at the query again (and compare it with what you've tried)

and (0='#form.catholic#' or PracticingCAT='#form.catholic#')

first you compare 0 'zero' value, to value of a checkbox. It is not compared to database field at this time. If it is true - the checkbox is not set - the second part after OR will not be even evaluated as it will not change enything.

however if the checkbox is checked and it's value is 1 the first condition will be false (0=1) and the result will depend only on the part after OR which is comparing value of checkbox to value in database. Actually because the value in the database may be only 0 or 1 you could simplify the query to

and (0='#form.catholic#' or PracticingCAT=1)

It will work in ColdFusion and very similar version will work in plan SQL

declare @option bit
set @option = 1

and (0=@option or PracticingCAT=1)
0
 
_agx_Commented:
@lof  - In ColdFusion this something better done with CF code, rather than that type of SQL.

@ajd3rd -  Yes, a CFIF is definitely the way to go here.

WHERE  Math='#form.math#'
<cfif form.catholic neq 0>
        ... add your sql here
</cfif>

BUT, a few notes:

><cfif not isdefined("form.catholic")>
> <cfparam name="form.catholic" default="0">
> </cfif>

The IsDefined() is redundant.  CFPARAM only sets the value if the field is not defined. So you don't need the IsDefined() check. Instead, just write:

      <cfparam name="form.catholic" default="0">

> where #SESSION.ThisApp.CN#='-1'

Does #SESSION.ThisApp.CN# contain a column name? If so, be careful with that kind of dynamic sql. If you're setting the session variable from user supplied input, that could easily become a sql injection risk.



0
 
ajd3rdAuthor Commented:
Agx,

A question.  I am using the #session.thisapp.cn# as a column name.  Since the site requires a sign on and it only allows for searching of the database should I still be concerned about sql injection?  If so can you recommend a way around the problem.  I am using the session variables to pass info the user selected on a preliminary search page via combo boxes.  I then use the session data (original combo boxes selections) along with additional input from the user to filter the results from the first search.

Thanks,

Andy
0
 
_agx_Commented:
> should I still be concerned about sql injection?

Yes - if the session values are based on user supplied input. Say you present users with a form that let's them select column names for filtering. When the form is submitted, you take those FORM values and store in session variables. Technically, that presents a sql injection risk because a user could submit a fake form and replace the FORM values with anything they want. Example #session.thisapp.cn# = "1 = 1; DROP TABLE xyz;". At the very least, the code should verify the submitted values are valid column names, and ignore/reject them if they're not.

Generally, I prefer not to expose the db schema in forms and instead I set up a VIEW for filterable reports.  Then use the system views (information_schema) to validate the selected information. But that may be overkill  here.
0
 
_agx_Commented:
> the site requires a sign on

BTW: Never trust users or rely on a login as the sole line of defense ;-)  
0
 
ajd3rdAuthor Commented:
agx,

Sorry it took so long for me to get back to you.  Can you tell me a simple way to check that the session variable value is actually a column name.  Essentially I'm looking for a way to say:

If session variable is one of the following allow it.

Thanks for you're help I'll award the points to you but I would appreciate it if you have any idea on how to check the above.

Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.