0

There have been several times when I've created a cfquery and for no good reason, it doesn't work. For example, I recently had a query like this:

<cfquery name="get_projects" datasource="#application.dsn#">
        SELECT *
        FROM   projects
        WHERE  project_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#project_id#">

          <cfif start_date NEQ "">
               AND project_start_date = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#start_date#">
          </cfif>

        ORDER BY project_name
    </cfquery>

I confirmed the start_date variable was an empty string. However, I'll get an error that points to that line and says "Value can not be converted to requested type" even though it should never have gotten to that line.

But here's the really weird thing.... If I add "AND 1=1" to the WHERE clause, the query works perfectly.

I've had this happen sporadically on a number of queries, but I can't say I recognize a pattern to it. Always the query is written perfectly. In some cases, the query was working previously, and suddenly it stops, possibly when there is a change somewhere else in the file. All I have to do is add "AND 1=1" to the WHERE clause, and it works again. Anyone else run into this or have ideas how to fix it?

Nick Petrie
  • 5,364
  • 11
  • 41
  • 50
  • You should also specify which version of ColdFusion you are using. You'll often get helpful tips based on this info. – Evik James May 16 '12 at 21:11
  • 2
    Re: *even though it should never have gotten to that line.* Not necessarily true. Any non-blank value, including a single space, an alpha character, etcetera would make it past your CFIF. Since you need a value that can be converted into valid date, you should at least use an `IsDate()` check instead of `NEQ ""`. IsDate has a pretty lenient interpretation of dates... But it will ensure you are passing a date object to ``. – Leigh May 16 '12 at 21:13
  • In a of the arguments passed to this query, it shows [empty string] as the value of "start_date." But you're right, an IsDate() would be more appropriate for this data type. – Nick Petrie May 17 '12 at 13:12
  • IsDate() is not a bullet proof way of making sure a date is being entered. Remember that ACF will return true on a number of arbitrary values (see this post as an example http://www.richarddavies.us/archives/2008/02/isdate.php) – rip747 May 17 '12 at 13:59
  • Agreed. I was not suggesting it for user input validation either. Its rules are too lenient. (Edit) Just that at the query level (ie after input validation), `IsDate` is a more appropriate test than a simple string length check. If the string can be parsed by isDate, it is parse-able by cfqueryparam as well. – Leigh May 17 '12 at 17:14

5 Answers5

4

Its a shortcut a lot of developers use, here is an example

select * from table where 1 = 1
<cfif x EQ 1>and x = 1</cfif>
<cfif y EQ 1>and y = 1</cfif>
<cfif z EQ 1>and z = 1</cfif>

This always works. There is a conditional and in 3 ifs. But if all the ands are false you end up with the query:

select * from table where 1=1

ie Return all records

If you have one or multiple ands being true, and you don't have the 1=1, you end up with:

select * from table where and x = 1

.. which doesn't work. So its just a simple shortcut to ensure all the ands work regardless of whether you need 0 or all of them.

Leigh
  • 28,765
  • 10
  • 55
  • 103
Dale Fraser
  • 4,623
  • 7
  • 39
  • 76
  • 1
    There is no around the first where clause, so there would be no point in putting AND 1 = 1 in his query – Yisroel May 16 '12 at 22:55
  • Yes there is, read the example, without the 1=1 you end up with a query that is select * from table where and x=1. The 1=1 allows lots of and statements without having to worry about which matches as they are always and's to the 1=1 – Dale Fraser May 16 '12 at 23:25
  • In your example, yes. In Nicks query, it would add nothing – Yisroel May 16 '12 at 23:29
  • In his query, he hasn't shown a full example with the 1=1. So this is my guess as to why and how I've seen it used before. – Dale Fraser May 16 '12 at 23:32
  • Sorry for the confusion Dale. The line with "project_id = " is first part of the WHERE clause that always gets executed. 1=1 is not necessary, but I do sometimes use that when the WHERE clause is only made up of statements. – Nick Petrie May 17 '12 at 13:15
2

I don't think that query is correct anyway - there is no AND or OR combining the two parts in the WHERE clause.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265
  • Sorry you're right... I wrote it on the fly for this question and missed it. I fixed it in the example. It wasn't like that in the working code. The code was correct. – Nick Petrie May 17 '12 at 13:10
1

I don't see how your query above would run as is.

I added the AND in your CFIF statement. This should run (when you repair the cfqueryparams.

SELECT *
FROM   projects
WHERE  project_id = <cfqueryparam>
       <cfif start_date NEQ "">
          AND project_start_date = <cfqueryparam>
      </cfif>
ORDER BY project_name

I often use the "AND 1=1" statement like this, where I start the WHERE clause with the 1 = 1: 

SELECT *
FROM   projects
WHERE  1 = 1
       AND project_id = <cfqueryparam>
       <cfif start_date NEQ "">
          AND project_start_date = <cfqueryparam>
      </cfif>
ORDER BY project_name
Evik James
  • 10,335
  • 18
  • 71
  • 122
  • Sorry, as I mentioned above, that was a typo, not actually a mistake in the code. I wrote it on the fly for the question and forgot to add it. The problem still stands. – Nick Petrie May 17 '12 at 13:16
1

I've seen it happen where the query gets compiled incorrectly and changing any part of the text in it (such as adding 1=1) re-compiles it.

Sharondio
  • 2,605
  • 13
  • 16
  • The situation you describe is especially true if the query is within a function within a CFC. – Evik James May 16 '12 at 21:15
  • This is exactly the case. The query is in a function in a CFC. Is that a known CF issue? – Nick Petrie May 17 '12 at 13:17
  • Known issue...or expected behavior, depending on who you ask. CF, by default, tries to cache compiled queries and components for performance. If you go to the "Caching" page in the cfadmin, you'll see some settings related to this. Turn off all caching options on your dev box to avoid this. Although even then, sometimes I've found I've had to clear the caches from within cfadmin, or, as mentioned, update the query text with something innocuous to get it working again. – Sharondio May 17 '12 at 15:30
  • I really don't believe this is the answer and don't believe anything is being cached. – Dale Fraser May 18 '12 at 00:30
0

IIRC, a Timestamp is not the same as a Date. If you're using MSSQL Server, see http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx

<cfqueryparam cfsqltype="cf_sql_timestamp" value="#start_date#">

should be

<cfqueryparam cfsqltype="cf_sql_date" value="#start_date#">

Adding AND 1=1 probably causes the server's query optimizer to ignore the entire WHERE predicate.

akatakritos
  • 9,836
  • 1
  • 23
  • 29
  • You are right. The two types are a different. `date` truncates any time portion. `timestamp` does not. However, just using `cf_sql_timestamp` instead of `cf_sql_date` with the same value would not cause that kind of exception. Since cfqueryparam validates long before anything is sent to the db, I suspect the real problem is the `cfif` validation of the `#start_date#` value. Or as others suggested, a possible caching problem. – Leigh May 16 '12 at 21:28
  • OR 1 = 1 might make the query optimizer ignore the where, but not AND 1 = 1 – Yisroel May 16 '12 at 22:56
  • @Yisroel - Except the validation error message they mentioned occurs before any sql is ever sent to the database. – Leigh May 17 '12 at 15:46