Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

is true really an integer in cfqueryparam???

Explorer ,
May 27, 2025 May 27, 2025

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?
557
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Expert , Jun 04, 2025 Jun 04, 2025

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 s

...
Translate
Community Expert ,
May 28, 2025 May 28, 2025

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
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 28, 2025 May 28, 2025

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
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 28, 2025 May 28, 2025

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
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 28, 2025 May 28, 2025

I am really at a loss here. You say, "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". But that is not so.

 

This is what your code says: the value going into cfqueryparam is #arguments.company_id# which, as you have confirmed, is an integer. It is not a boolean. Neither does ColdFusion accept it as or convert it into a boolean.

 

There is only one obvious boolean in your  code, namely, 

structKeyExists(arguments, "company_id")

But that is not the value going into cfqueryparam.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 28, 2025 May 28, 2025

let me try to clarify...

 

there is a link, xxx.com/company/?company_id=1, the user clicks it.

the page invokes the function, getCompany, and passes company_id=#url.company_id#

it's coded so that if the company_id argument exists, the function returns the specified company, otherwise it returns all companies, that's the boolean logic, but has nothing to do with the query param value.

 

but, if someone were to manually alter the link to xxx.com/company/?company_id=true

the page invokes the function, getCompany with company_id=#url.company_id#

the function returns a record where company_id=1

 

I haven't checked to see when true converts to 1, but it looks to be too soon, like when the function is called and url.company_id is the value of the argument. 

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 28, 2025 May 28, 2025
but, if someone were to manually alter the link to xxx.com/company/?company_id=true the page invokes the function, getCompany with company_id=#url.company_id# the function returns a record where company_id=1

 

Well, there's your problem right there! Look, it's nice you have CFQUERYPARAM to tell the database what types it should expect. But that's not intended to replace all validation in your code.

 

You should have something basically like this:

 

<cfif isValid("integer", url.company_id)>

... do all your other stuff, including your queries ...

<cfelse>

... report the problem ...

</cfif>

 

... or, better yet, use CFTRY/CFCATCH/CFTHROW: https://helpx.adobe.com/coldfusion/developing-applications/the-cfml-programming-language/extending-c...

 

Note: my CF skills are a bit rusty, there are a lot of CF versions out there, and I'm not sure whether isValid will work for you. You might want to look at this: https://stackoverflow.com/questions/46124664/best-way-to-check-if-value-is-integer-coldfusion-9

 

Anyway, wrap your entire page/function/whatever in some conditional or error handling logic before you get to your CFQUERY.

 

Dave Watts, Eidolon LLC
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 29, 2025 May 29, 2025

Thanks for your clarification, @dwaynea8754223 .

 

Now, it all makes sense to me. I can also see at once what the problem is. 

 

If you put the boolean value True or False in a cfqueryparam whose cfsqltype is Integer, ColdFusion will cast the value to 1 or 0, respectively. As you have discovered, cfargument of type Integer will also accept the value True or False. No need to look further into it. It's just one of those things.

 

This is not a peculiarity of ColdFusion alone. Many other programming languages, and not only weakly-typed ones, have the same behaviour. As Dave has said, correctly too, it is up to the programmer to validate any user-input to be used in an application.  

 

So the question is: how do you get ColdFusion to exclude booleans as integers?

 

You have provided an answer yourself: validate using isNumeric(arguments.company_id) ; which could possibly be expanded to isNumeric(arguments.company_id) and val(arguments.company_id) gt 0.


Dave provided isValid("integer", arguments.company_id); which could possibly be expanded to isValid("integer", arguments.company_id) and val(arguments.company_id) gt 0.

 

Dave's is more robust because it filters out decimals as well. Decimals such as 3.14 can be more of a nuisance than booleans. For a start, there are infinitely many more of them.

 

Here is yet a third possibility, using regular expression: REfind("^[1-9]\d*$", arguments.company_id)

 

 

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 28, 2025 May 28, 2025

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.

 

Dave Watts, Eidolon LLC
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 28, 2025 May 28, 2025

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
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
May 28, 2025 May 28, 2025

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.

 

Dave Watts, Eidolon LLC
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 04, 2025 Jun 04, 2025

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
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jun 04, 2025 Jun 04, 2025

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
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 09, 2025 Jun 09, 2025
LATEST

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
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources