Copy link to clipboard
Copied
if you use the literal word "true" as the value for integer type cfqueryparam, this executes the query with an integer value of "1".
1 Correct answer
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
...Copy link to clipboard
Copied
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>
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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)
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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">
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.

