http://coldfusion.com logo
Join Slack
Powered by
I'm running into a really weird cfquery error. It'...
# cfml-general
j
I'm running into a really weird cfquery error. It's like a cfquery block refuses variables to be referenced within, for some reason. If no variables are referenced, the cfquery block executes just fine. If a single variable is referenced, the error indicates the line with the problem is the line the variable is on. Add spacing, the line number changes to match. Add a second variable, the line number changes to reference the second variable. Add a third and it changes to reference the line with the third variable. The specific error is: java.util.NoSuchElementException. However, reference the "offending" variables immediately prior to the cfquery and no error is thrown for those lines (though it'll still throw them for the cfquery). Here's the offending code:
Copy code
<cfquery name="getlogins" datasource="#app.datasource#">
  SELECT user_login_log.user_id
       , user_email.email
       , user_login_log.employer_group_id
       , employer.uuid AS employer_uuid
       , employer.company AS employer_name
       , employer_group.uuid AS employer_group_uuid
       , employer_group.name AS employer_group_name
       , employer_group_role.uuid AS employer_group_role_uuid
       , employer_group_role.name AS employer_group_role_name
       , user_login_log.var_id
       , var.uuid AS var_uuid
       , var.name AS var_name
       , var_role.name AS var_role
       , '' AS context_icon
       , '' AS context_string
       , user_login_log.http_user_agent
       , user_login_log.remote_addr
       , user_login_log.http_referer
       , user_login_log.with_mfa
       , user_login_log.is_success
       , user_login_log.is_admin
       , user_login_log.date_created
    FROM user_login_log LEFT OUTER JOIN user_email
      ON user_login_log.user_email_id = user_email.id LEFT OUTER JOIN employer_group
      ON user_login_log.employer_group_id = employer_group.id LEFT OUTER JOIN employer_group_role
      ON user_login_log.employer_group_role_id = employer_group_role.id LEFT OUTER JOIN employer
      ON employer_group.employer_id = employer.id LEFT OUTER JOIN var
      ON user_login_log.var_id = var.id LEFT OUTER JOIN var_role
      ON user_login_log.var_role_id = var_role.id
   WHERE user_login_log.user_id = #Val(url.user_id)#<cfif getloginips.recordcount>
      OR user_login_log.remote_addr IN (#QuotedValueList(getloginips.remote_addr)#)</cfif>
   ORDER BY user_login_log.date_created DESC
</cfquery>
I should add that this doesn't throw errors for every value of url.user_id or quoted list of login remote_addr values. It only does it for some. I suspect it has to do it over a certain string length, but can't be absolutely certain.
m
Please indicate version and update for CF
a
And what the error message is.
j
Standard Edition, version 2018,0,03,314033 running on Windows Server 2016 against MSSQL
a
[insert usual dislaimer about not hard-coding values into your SQL string too]
m
Jeff if you dump user_login_log prior to this, do you get values?
s
Can you see the Variable before hitting the query?
m
Like dump and abort
j
error message, cryptically, is 
java.util.NoSuchElement
and references the last instance of a variable reference (in this case the quotedvaluelist())
yes, i can console.log the variables just prior to the cfquery call and they should up in console without issue
s
I would dump all the variables before you hit the query and confirm they are what you are expecting.
a
The actual full error. Not just parts of it.
j
Copy code
java.util.NoSuchElementException
	at java.base/java.util.LinkedList.removeFirst(LinkedList.java:274)
	at com.seefusion.SeeFusionHandler.publish(SeeFusionHandler.java:34)
	at java.logging/java.util.logging.Logger.log(Logger.java:979)
	at java.logging/java.util.logging.Logger.doLog(Logger.java:1006)
	at java.logging/java.util.logging.Logger.log(Logger.java:1029)
	at java.logging/java.util.logging.Logger.info(Logger.java:1802)
	at com.seefusion.ResultSetImpl.next(ResultSetImpl.java:123)
	at coldfusion.sql.QueryTable.populate(QueryTable.java:360)
	at coldfusion.sql.QueryTable.populate(QueryTable.java:287)
	at coldfusion.sql.Executive.getRowSet(Executive.java:712)
	at coldfusion.sql.Executive.executeQuery(Executive.java:1586)
	at coldfusion.sql.Executive.executeQuery(Executive.java:1317)
	at coldfusion.sql.Executive.executeQuery(Executive.java:1247)
	at coldfusion.sql.SqlImpl.execute(SqlImpl.java:427)
	at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1211)
	at coldfusion.tagext.sql.QueryTag.startQueryExecution(QueryTag.java:841)
	at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:794)...
m
console log? no, cfdump. or writeDump. In CF.
a
I'd chuck yer 
Val(url.user_id)
and 
getloginips.remote_addr
into params, just for good measure.
j
I have console capability in my cf instance
m
oh I see
s
And ditto using queryParam
m
Still, I'm not sure if the PAGE has scope.
or template I should say
a
OK. You've given us the stack trace. What about the actual CF part of the error?
s
Not sure what scope the variables are coming from 😉
a
Don't think one is gonna get a 
java.util.NoSuchElementException
from an undefined variable, is one?
s
I've never seen that for undefined variables but .... maybe some strange Java object issue?
a
And looking at the stacktrace, it looks to me like you have SeeFusion running, and it's that that is crapping out.
j
from try/catch, the error message is empty, no value whatsoever
type is java.util.NoSuchElementExeption
a
Which is not as helpful as it could be.
Yeah I think SeeFusion is crocked.
👍 1
j
i just noticed that in the stacktrace, too
i'll reach out to webapper and see if they have thoughts on this
it's odd that it's catching it so early in the request cycle that it won't even try to execute the query
a
There's probably a seefusion log somewhere on the server... have a look to see if it's bleating about something in general?
👍 1
j
fwiw, switching to using cfqueryparam offered no relief
and i appreciate the suggestions to use cfqueryparam, but these aren't vars that can be manipulated in any way by a user
a
doesn't matter
m
cfqueryparam will actually help your queries run faster in a lot of instances
a
It's bad form to hard-code values into SQL statements.
j
i've been doing this 25+ years and rarely found there to be a measurable difference
a
params are not for SQLi. That's is just a side effect of using them.
s
It's been a while with SeeFusion but you should be able to define a datasource without the seeFursion wrapper if I remember
a
All right mate. You do you.
Every frickin informed person in the world would say "don't hard-code values in an SQL statement, it's poor practice", but I guess you and your 25yrs know better. Somehow.
j
Adam, chill. That kind of response isn't helpful or professional. I'm not here being hostile. No need to treat me like I am.
s
Sometimes especially using IN statements queryParams are especially helpful when you need a list
j
I'm happy to adjust my coding style to incorporate them if I can see something that demonstrates why it's a good practice beyond vague-slacking 'it's good practice' or 'informed folks just know'
s
Google SQL injection
j
i know about sql injection. neither of the variables i'm using can be manipulated by a user, despite what they appear to be
s
Then binding is a good reason to
👍 1
j
@spills can you expand on why binding matters?
a
You see... you don't get to berate me about professionalism when this is your attitude. You've been given advice about better practice for your code. Better practice that the entire industry is behind. And your response is "nah, unless you prove it I'm gonna stick with writing shoddy code". That is not professional.
👎 1
The SQL statement gets compiled for reuse. If it's got values hard coded in it, the compilation step can't be reused on subsequent calls.
✅ 2
👍 1
j
ok, that makes sense. I wasn't aware that compiling could take advantage of splitting values out like that. it makes sense though, i guess
a
This may not have much perceivable difference, but yer just making yer DB do more work than it needs to. And it's just so easy to not do that.
Plus also... And I am kinda having a go at you,but the advice stands either way... the attitude of dismissing good practice advice because you don't understand it, is not great.
Saying "oh yeah, that's interesting but I don't get it... can you explain that to me cos I like to understand things rather than just blindly doing them" would be a better mindset to bring to this stuff.
j
that's fine, but loads of folks have loads of ideas of what's good practice. i've run into lots that's actually terrible advice, but couched under the banner of good advice. so, i've learned to ask for the 'why of it' before just accepting it.
a
yeah very true
j
but just coming out and implying i'm not an informed person because i personally haven't seen the point of using cfqueryparam isn't cool
a
This particular case is not helped by CFMLers being hung up on it being about SQLi.
So the advice as to why to use them is... as you say... less good than it could be.
j
fair
and for the record, my comment about 25 years and my experience with them was in reply to someone else saying they'd make queries run faster, not to you and your comments about using them.
so it kinda hit weird when you snapped back about me being, basically, an uninformed chud
a
In regards to the actually issue here... I'm pretty sure the issue is some glitch with SeeFusion and not actually your code.
Params or otherwise it seems fine to me, and you've checked the variables etc
m
We usually just think of Adam as a standard, run of the mill chud. Do with that information what you will 😉
😆 1
j
yes, i agree it's seefusion taking a dump for some reason and will dig into that matter.
a
I'm abrasive & cut to the chase
I'm not... USAn...
So culturally I'm less concerned about being crushingly polite all the time.
j
i'm abrasive and cut to the chase, too, so i guess i know how folks sometimes feel when i come at them in a code review 😉
a
But I am also a bad example of "antipodean flair" [cough]
m
Adam I will never refer to you as crushingly polite ❤️
a
A community colleague once (validly) pointed out to me "one catches more flies with honey than one does with vinegar", to which my reply was "yeah cool. Next time I'm catching fuckin flies I'll bear that in mind pal"
👍 1
j
ok, so cfqueryparam usage going up from here on out and a chat with the folks from seefusion
❤️ 1
a
Report back if you get to the bottom of it?
j
will do
a
Good man.
m
Also if you're on a host, move to CF2021 ASAP as 2018 is coming out of support
j
i know 😞 we just can't afford the cost of upgrading
a
Adam I will never refer to you as crushingly polite ❤️
You better not.
m
Hopefully we can help with that soon, trying to get some more affordable tiers in place. Especially in AWS/GCP/Azure
👍 3
s
Create a new datasource in CF Admin without SeeFusion connection properties and see if your query works
👍 1
a
oooh
Good suggestion.
j
thanks @spills. i knew i'd opened a new tab for a reason...
s
I did mention earlier 😉
j
yes and that's why i had a new tab but no address to cfadmin in it
s
Good Luck I gotta make it look like I am actually doing my day job now!
j
worked without issue so seefusion is definitely the culprit here
🎉 2
m
I'd suspect that it is trying to parse some parameters due to a pattern that included a : or ? in your string, and it can't find what it expected to reference back to. As others have mentioned, queryparam will improve the sql plan, but it also can prevent the other interpreters like fr from getting tripped up.
e
I would turn this into a stored procedure and then you can just refer to cfstoredproc.
3 Views
Open in Slack
PreviousNext
Powered by