Skip to main content
dwaynea8754223
dwaynea8754223
Known Participant
May 28, 2025 2:11 AM UTC
Answered

is true really an integer in cfqueryparam???

if you use the literal word "true" as the value for  integer type cfqueryparam, this executes the query with an integer value of "1".

 

<cfif StructKeyExists(arguments, "company_id")>
      AND tblCompany.company_id = <cfqueryparam value = "#arguments.company_id#" cfsqltype = "cf_sql_integer">
      </cfif> 
 
This returns a record when the column value equals one "1" ???
 
Is this the intended behavior? Is there a way around this. I'm guessing IsValid "Integer" might help. I realize CF equates true to 1, but when the type is specified, it should not.
 
Anyone been through this?
    BKBK
    BKBKCorrect answer
    Community Expert
    Jun 4, 2025 9:27 PM UTC

    I am sorry to repeat this, but feel I have to for the sake of rigour. In your code example, ColdFusion is not converting type. You have specified the type, namely, integer.

     

    If you tell ColdFusion that the datatype of a variable is "integer", and you then pass it the value True/False, then ColdFusion will interpret the value to be 1/0 implicitly. In other words, in ColdFusion,

    integer value of True is 1

    and

    integer value of False is 0

     

    ColdFusion is not the only language that has this. The same thing happens in Lucee, PHP, C / C++, JavaScript and Python.

     

    That said, the test

    isNumeric(url.company_id) ? url.company_id : 0

    is not an accurate validation test for integers,. This test would give 3.14 a thumbs-up, which is incorrect.

     

    Dave's suggestion, 

    isValid("integer",url.company_id) ? url.company_id : 0

    is an accurate validation test for integers.

    View reply

    13 replies

    dwaynea8754223
    dwaynea8754223Author
    Known Participant
    Jun 4, 2025 4:23 PM UTC

    I used this to validate the url variable. Don't think CF should convert type in this case, but okay.

     

    <cfinvoke component="#companyservice#" method="getcompanies" company_id="#( IsNumeric(url.company_id) ? url.company_id : 0)#" returnVariable="company">

    Translate
    BKBK
    Community Expert
    BKBKCommunity ExpertCorrect answer
    Community Expert
    Jun 4, 2025 9:27 PM UTC

    I am sorry to repeat this, but feel I have to for the sake of rigour. In your code example, ColdFusion is not converting type. You have specified the type, namely, integer.

     

    If you tell ColdFusion that the datatype of a variable is "integer", and you then pass it the value True/False, then ColdFusion will interpret the value to be 1/0 implicitly. In other words, in ColdFusion,

    integer value of True is 1

    and

    integer value of False is 0

     

    ColdFusion is not the only language that has this. The same thing happens in Lucee, PHP, C / C++, JavaScript and Python.

     

    That said, the test

    isNumeric(url.company_id) ? url.company_id : 0

    is not an accurate validation test for integers,. This test would give 3.14 a thumbs-up, which is incorrect.

     

    Dave's suggestion, 

    isValid("integer",url.company_id) ? url.company_id : 0

    is an accurate validation test for integers.

    Translate
    dwaynea8754223
    dwaynea8754223Author
    Known Participant
    Jun 9, 2025 2:02 PM UTC

    yeah, I agree. I was rushing and really didn't consider the implications of numeric. it worked for my initial test since it wouldn't satisfy the integer caqueryparam type.

     

    Translate
    Community Expert
    Dave WattsCommunity Expert
    Community Expert
    May 28, 2025 8:27 PM UTC

    I wouldn't go so far as to say this is the intended behavior, but it's unsurprising. While CFQUERYPARAM requires a strict type for DB parameter validation, that's really only used by the DB. CFML itself is very loosely typed. So, if you happen to have a Boolean value where you think you should have an integer value, that Boolean value will automatically be converted to integer if it fits. And that's what's happening here! CFML is like a cat: "if it fits, I sits". In CFML, the fact that Boolean values are often expressed as if they were strings makes all this a bit more complicated, too.

     

    The way around this is to not use Boolean values when you want integer values. The programmer should know which is which.

     

    Translate
    Dave Watts, Eidolon LLC
      dwaynea8754223
      dwaynea8754223Author
      Known Participant
      May 28, 2025 8:29 PM UTC

      I'm not doing anything with the bool value.... the user could...

       

      plain and simple, if the user sets the url param to true, the function returns record 1.

      Translate
      Community Expert
      Dave WattsCommunity Expert
      Community Expert
      May 28, 2025 8:48 PM UTC

      If you don't know what's in there, you should be able to check yourself and decide what to do. My other answer, posted a few seconds ago, might be more helpful.

       

      Translate
      Dave Watts, Eidolon LLC
      BKBK
      Community Expert
      BKBKCommunity Expert
      Community Expert
      May 28, 2025 1:31 PM UTC

      Hi, the scenario is unclear to me. In fact, it seems to me to be highly unlikely that the value of  tblCompany.company_id in the database is TRUE.

       

      From what I can see, ColdFusion is behaving as expected. The reason why a record is returned is because the surrounding function has an argument called "company_id". So, structKeyExists(arguments, "company_id") is true, and your code is equivalent to:

      <cfif true>
	AND tblCompany.company_id = <cfqueryparam value = "#arguments.company_id#" cfsqltype = "cf_sql_integer">
</cfif>

      Apparently, that argument has value 1. Furthermore, you have specified the cfsqltype as cf_sql_integer, hence the integer value 1:

      <cfif true>
	AND tblCompany.company_id = 1>
</cfif>

       

      Suggestions:

      1.  If you want to exclude one or more company_id values, then apply a further condition on the arguments.company_id value. For example, to exclude the value 1 :

      <cfif StructKeyExists(arguments, "company_id") and arguments.company_id neq 1>
	AND tblCompany.company_id = <cfqueryparam value = "#arguments.company_id#" cfsqltype = "cf_sql_integer">
</cfif>

       

      2.  If you want a boolean value in cfqueryparam, then the cfsqltype to use is cf_sql_bit. Something like this:

      <cfif structKeyExists(arguments, "company_id") and arguments.company_id neq 1 and arguments.isActive>
	AND tblCompany.isActive = <cfqueryparam value = "#structKeyExists(arguments, 'company_id') and arguments.company_id neq 1 and arguments.isActive#" cfsqltype = "cf_sql_bit">
</cfif>

       

       

       

      Translate
      dwaynea8754223
      dwaynea8754223Author
      Known Participant
      May 28, 2025 6:13 PM UTC

      the value of tblCompany.company_id is integer 1. the cfquery param accepts the string value "true" as a qualified integer. and since it's integer 1, pulls up the company who's company_id is 1. there is a company with a company_id of 1- that's how I found the problem. if "true" is passed to the function, which is not the case under normal circumstances, it's supposed to be an int, the function returns the first company, which is company_id =1

       

      I quess you can specify the type of argument? "true" should not satisfy the argument type of numeric, but I've never had much luck getting form collections to successfully pass to functions with specific argument types.

       

       

      Translate
      dwaynea8754223
      dwaynea8754223Author
      Known Participant
      May 28, 2025 6:41 PM UTC

      even <cfargument type="numeric"> still accepts "true" as an int.

      i ended up adding logic when invoking the function that checks the value with isNumeric.

      Translate
      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices