SELECT AAA, BBB, ... FROM Tables, etc.. WHERE 1=1 AND SQL_NameCol = AND SQL_AgeCol = ... etc., etc. ... ORDER BY ThisColumn, ThatColumn [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => MikerRoo [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 2 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> Building Query criteria dynamically based on Form inputs - Coldfusion - Advanced Techniques

Building Query criteria dynamically based on Form inputs - Coldfusion - Advanced Techniques

I need to dynamically build a db query based on form inputs; this such a typical thing I am hoping someone can shortcut me to a more elegant solution. The user does not have to enter any of the 5 criteria and the Query only pulls from one table, I just need to dynamically build the query criteria... Where I am now - Once the user has submitted their criteria I put the form variables into a list and use listlen(variable). to tell if there is 0, 1, gt 1 criteria <cfifListLen(q_crit) gt 0> SQL Statement <cfif ListLen(q_crit) eq 1> ...

  1. #1

    Default Building Query criteria dynamically based on Form inputs

    I need to dynamically build a db query based on form inputs; this such a
    typical thing I am hoping someone can shortcut me to a more elegant solution.
    The user does not have to enter any of the 5 criteria and the Query only pulls
    from one table, I just need to dynamically build the query criteria...

    Where I am now - Once the user has submitted their criteria I put the form
    variables into a list and use listlen(variable). to tell if there is 0, 1, gt
    1 criteria

    <cfifListLen(q_crit) gt 0>
    SQL Statement
    <cfif ListLen(q_crit) eq 1>
    WHERE (cfif's to figure out which variable was entered)
    <cfelseif ListLen(q_crit) gt 1>
    WHERE
    Dynamically format query criteria :confused;
    </cfif>
    </cfif>
    I have the first two options ready, but dynamically creating the where clause
    is giving me problems. There must be a better solution than writing out all the
    possible permutaions... how do I dynamically write the clause so "AND" is
    inserted after the first and subsequent variables but not after the last one?

    I tried continually adding to a variable, but so far I haven't figured it out.

    Any insights is greatly appreciated. If I managed to come up with a elegant or
    fugly solution, I'll post the results

    Beards247 Guest

  2. #2

    Default Re: Building Query criteria dynamically based on Forminputs

    There may be tutorials on [url]http://easycfm.com/[/url] and similar sites...

    Generally, here are some guidelines:
    1) Keep the Select clause the same -- not switched by If statements.
    2) Look up and study SQL Injection attacks -- Someone WILL attempt this on
    your site.
    3) Use <CFQUERYPARAM> and/or stored procedure(s) (If using SP's be aware that
    you may need the "with recompile" option).
    3) Use a universal stub where clause, like the attached code:

    Finally, don't put your form variables in a list and then try to p them
    back out -- this just needlessly complicates things.

    Regards,
    -- MikeR


    <!--- IMPORTANT: In this example we use the form variables directly (for
    simplicity).
    In actual practice, you would not do this but instead: (A) <cfparam> the
    vars,
    (B) guard against cross-site scripting, (C) guard against SQL Injection,
    (D) do any
    business-rule checking, and (E) check field length if applicable.
    --->

    <CFQUERY name="qSomeQry" datasource=...>
    SELECT
    AAA, BBB, ... <!--- Fixed list of columns --->
    FROM
    Tables, etc..
    WHERE
    1=1 <!--- Legal, harmless and very useful. --->

    <CFIF FORM.NameVar IS NOT "">
    AND SQL_NameCol = <CFQUERYPARAM value=#FORM.NameVar#
    CFSQLType="CF_SQL_VARCHAR">
    </CFIF>
    <CFIF FORM.AgeVar NEQ 0>
    AND SQL_AgeCol = <CFQUERYPARAM value=#FORM.AgeVar#
    CFSQLType="CF_SQL_INTEGER">
    </CFIF>

    ... etc., etc. ...

    ORDER BY
    ThisColumn, ThatColumn
    </CFQUERY>

    MikerRoo Guest

  3. #3

    Default Re: Building Query criteria dynamically based on Forminputs

    Mike -

    Thanks for the guidance. It's funny you mention not using the list b/c that is
    exactly what I kludged out over the weekend. Per your advice I've read up on
    SQL injection attacks and xss - we'll be implementing these code snippets ASAP!

    Curious though, what benefit does the 1=1 provide in the where statement aside
    from guaranteeing a where statement? Do you have URL's that reference this
    approach (or other handy favorites?)


    Beards247 Guest

  4. #4

    Default Re: Building Query criteria dynamically based on Forminputs

    I don't have any URLS on the 1=1 approach but there are several articles out
    there that use it.

    Anyway, the benifit is this:
    Without the 1=1: You must go through a complicated dance (Do I have any
    conditions? If so, add "Where" to the SQL; For each condition, Is this the
    first condition statement to be used? If not, omit the "and". etc. etc.)
    This gets messy quickly.

    With the 1=1: For each condition, if you have it, just add it with a leading
    "and". Done!

    MikerRoo Guest

  5. #5

    Default Re: Building Query criteria dynamically based on Form inputs

    > Curious though, what benefit does the 1=1 provide in the where statement
    aside 

    On a side note, I found that 1=1 is the thing to use when you want
    compatibility between MS SQL and MySQL (MS SQL doesn't like the short form:
    WHERE 1 and ...).

    --
    <mack />


    Neculai Guest

Similar Threads

  1. Variable inputs on a form
    By colesc8 in forum Coldfusion - Getting Started
    Replies: 4
    Last Post: March 6th, 05:05 AM
  2. User inputs token from web form
    By jon in forum FileMaker
    Replies: 1
    Last Post: July 30th, 08:59 AM
  3. Different Subforms based on Query Criteria
    By Deuce Sapp in forum Microsoft Access
    Replies: 1
    Last Post: July 15th, 03:03 AM
  4. Highlight Text based on certain criteria
    By Stephajn Craig in forum ASP.NET General
    Replies: 2
    Last Post: July 7th, 11:07 PM
  5. Update query based on a form
    By Brett in forum Microsoft Access
    Replies: 0
    Last Post: July 3rd, 02:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •