search function does not pass URL.NAME

[pirula08]

I am trying to make the search function for my new site, but somehow I got stuck with something that should be easy to handle. Now, the search function works pretty well, considering it's for testing purposes only and need to be improved. On the other hand in some cases, when entering and passing keywords with quotes, only dots (...........), + (++++) or - , it is behaving strangely, like the name (keywords) can not actually be passed. I am preety much sure that the issue has something to do with sql injection, but can not figure out what exactly the problem is.

<cfparam name="URL.NAME" default="0" type="Any">

<cfquery name="test" datasource="database">
SELECT *
FROM table1
WHERE 0=0

    <cfif len (URL.NAME) gt 0>
       <cfloop index = "keyword"
        list = "#URL.NAME#"
        delimiters = " ">
        AND NAME LIKE <cfqueryparam value="%#keyword#%" cfsqltype="cf_sql_clob">
        </cfloop>
    </cfif>

</cfquery>



and code for handling messages


   <cfif isdefined ("URL.NAME")>
          <cfif URL.NAME GTE 1>
            <cfif test.recordcount is "0">
              No records match your search criteria. Please go back to the form and try again.
              </cfif>
              <cfelse>
              Please insert your search criteria.
            </cfif>
    </cfif>


everything works fine (as far as possible with this simple code) except in cases when for example I enter

"aaaaaa"
................
++++++

The problem is that the search returns a message "Please insert your search criteria." instead "No records match your search
criteria...."  It seems like for some reason URL.NAME is set to default value!?

What am I doing wrong?

[JMF3]

If this is on SQL Server, I don't think you can LIKE against a LOB

column type, but then again it seems like that's not the error you're

getting either.

[pirula08]

Nope, it's on MySQL server.

[-__cfSearching__-]

cfif URL.NAME GTE 1

If URL.NAME is a string of keywords, the CFIF does not make sense. It is testing if the value of the variable is greater than or equal to zero. What you probably meant to test is the variable's length.

Though if keywords are required, you should be checking this before the cfquery is executed, not after.

[pirula08]

"If URL.NAME is a string of keywords, the CFIF does not make sense." Yes it does. This will be an advanced search page so I intentionally set the default URL.NAME value to 0 and therefore when someone enters the page will automatically see a message stating "Please insert your search criteria." As for the other part of code, if someone enters anything in the search form URL.NAME should take value 1 and execute the rest of the code when needed!?

"What you probably meant to test is the variable's length." actually no, I did not mean that.

"Though if keywords are required, you should be checking this before the cfquery is executed, not after." of course, but as I said this is a simple code for testing purposes only and currently it lacks some functionality. I am just curious what is the problem with the current code.

[-__cfSearching__-]

"If URL.NAME is a string of keywords, the CFIF does not make sense."
>> Yes it does.

No, it really does not ...

<cfif URL.NAME GTE 1>

You are treating URL.NAME as both a numeric value

<cfloop index = "keyword"      

        list = "#URL.NAME#"
        delimiters = " ">
        AND NAME LIKE <cfqueryparam value="%#keyword#%" cfsqltype="cf_sql_clob">
        </cfloop>

... AND treating it as space separated list of values? There is something wrong with that logic.

As for the other part of code, if someone enters anything in the search form URL.NAME should

take value 1 and execute the rest of the code when needed!?

Why? You are not setting the value to "1" anywhere in the code posted. Second, if even you did that, the resulting query would end being:

         SELECT  *
         FROM     table1
         WHERE 0=0

         AND NAME LIKE '%1%'

.. which does not seem very useful.  So again, there seems to be a logic problem.  Now if you were talking about the length (ie number of list elements) ... that would make more sense.

Message was edited by: -==cfSearching==-

[pirula08]

Now if you were talking about the length (ie number of list elements) ... that would make more sense.

yep, I was talking about the number of list elements. 

[BKBK]

You could make it simpler. For example,

<cfparam name="url.name" default="some_default_string" type="Any">

<cfquery name="test" datasource="database">
SELECT *
FROM table1
WHERE name LIKE <cfqueryparam value="%#url.name#%" cfsqltype="cf_sql_varchar">
</cfquery>

and code for handling messages

<cfif url.name NEQ "some_default_string" and test.recordcount is 0>
    No records match your search criteria. Please go back to the form and try again.
<cfelseif url.name NEQ "some_default_string" and test.recordcount GT 0>
    <!--- The search returned a match! --->
<cfelseif url.name EQ "some_default_string">
    Please insert your search criteria.
</cfif>

[-__cfSearching__-]

You could make it simpler.

True. Though the code should probably skip the cfquery if there is nothing to search for yet.

That said, it is still not clear what they are even trying to compare here. Given the list loop and all ..

[pirula08]

I accepted some of your tips and here is what I came up with. The Code has been simplified, but now actually works.

<cfparam name="URL.NAME" default="abcd1234" type="Any">

<cfif len (URL.name) lt 1>
<cfset messageCH = "at least one character must be entered">
<cfelse>
   <cfquery name="testadvanced" datasource="datasource">
     SELECT *
     FROM table
     WHERE 0=0

     <cfloop index = "keyword"
        list = "#URL.NAME#"
        delimiters = " ">
        AND NAME LIKE <cfqueryparam value="%#trim(keyword)#%" cfsqltype="cf_sql_clob">
     </cfloop>
</cfif>

and for messages:

<cfif isdefined ("testadvanced") and url.name NEQ "abcd1234" and testadvanced.recordcount is 0>
     No records match your search criteria. Please go back to the form and try again.
     <cfelseif url.name EQ "abcd1234">
     Please insert your search criteria.
    <cfelseif isdefined ("messageCH")>
    <cfoutput>#messageCH#</cfoutput>
</cfif>

This is still a simple code for testing purposes only and has yet to be polished.