CODEWITHSUNDEEP
mysqlsql-servercoldfusion
sudhanshu balaiya
sudhanshu balaiya

Reputation: 43

Sql search query for my search form in coldfusion

I have created a search form for my application. I now want to search in the database by taking inputs from either 1 or multiple inputs at a time from productID, ProductName, ProductCostFrom , ProductCostTO. I am using following query to search. It is working quiet well if I give a cost range in the inputs, but gives syntax error if I search only for "ProID" or "ProName" or both.

<cfquery name="myquery" datasource="cfdb2">
    Select *  
    from   ProEntry 
    where  (
              Procost BETWEEN #Form.Procost# AND #Form.ProCostTo#
           ) 
    OR     (
            Proid='#form.Proid#' OR 
            Proname='#form.Proname#'
           ) 
</cfquery>

Upvotes: 0

Views: 129

Answers (1)

Leigh
Leigh

Reputation: 28873

Remember, whatever query you send to the database must be valid SQL. When the cost values are missing (or contain non-numeric values) the generated SQL will be malformed. If both fields are empty the where clause will look like this (wrong):

where  ( Procost BETWEEN AND )

.. instead of this:

where  ( Procost BETWEEN 50 AND 100 )

If the search values are optional, you need to build the SQL statement conditionally. A common approach is to start with a static condition that is never true, so the query only returns records if the other filters are matched: 

WHERE 1 = 0

Then append the filters only if the necessary form fields are populated. Though always use cfqueryparam to protect against sql injection. 

<cfif isNumeric(form.Procost) AND isNumeric(form.ProCostTo)>
  OR (
       Procost BETWEEN 
           <cfqueryparam value="#form.Procost#" cfsqltype="{replace_with_your_type}"> 
       AND
           <cfqueryparam value="#form.ProCostTo#" cfsqltype="{replace_with_your_type}">
      )
</cfif>

<cfif len(trim(form.proID))>
   OR Proid= <cfqueryparam value="#form.proID#" cfsqltype="{replace_with_your_type}">
</cfif>

... etcetera ...

Upvotes: 5

Related Questions