?
Solved

Include Checkbox in SQL Server  Where Statement only When Checked

Posted on 2010-01-03
9
Medium Priority
?
288 Views
Last Modified: 2013-12-24
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

0
Comment
Question by:ajd3rd
  • 4
  • 3
  • 2
9 Comments
 
LVL 10

Expert Comment

by:lof
ID: 26166926
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
 

Author Comment

by:ajd3rd
ID: 26166949
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
 
LVL 10

Expert Comment

by:lof
ID: 26167096
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 52

Expert Comment

by:_agx_
ID: 26168889
@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
 

Author Comment

by:ajd3rd
ID: 26170560
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
 
LVL 52

Expert Comment

by:_agx_
ID: 26172748
> 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
 
LVL 52

Expert Comment

by:_agx_
ID: 26172796
> the site requires a sign on

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

Author Comment

by:ajd3rd
ID: 26206546
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 26207341
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question