http://coldfusion.com logo
Join Slack
Powered by
For a `<cfquery>` call that causes a DB erro...
# lucee
a
For a 
<cfquery>
call that causes a DB error, is there any way of getting back the exact SQL string that was sent to the DB? In the returned exception all the param values have been inlined into the SQL (eg: I see 
SELECT * from tbl WHERE col = 'param value'
not 
SELECT * from tbl WHERE col = ?
(and then a separate struct of the param values that were passed). Reason being... the error I am getting is with how Lucee is passing the param values, and I can't troubleshoot it if I can't see what it's passing. I could likely get it back from the underlying JDBC exception, but Lucee swallows that (see https://luceeserver.atlassian.net/browse/LDEV-3738). Am just wondering if there's any other way. The issue is that somehow an SQL string / 
<cfqueryparam>
combo I am getting is somehow passing more params than it is param markers (ie, the 
?
in the SQL string), and I'm trying to work out why. Error is 
Parameter index out of range (3 > number of parameters, which is 2)
The SQL string is getting munged by some CFWheels stupidity, but I am trying to work out how I'm not just getting a SQL syntax error (which I should), rather than this param count mismatch. To recap, the question is just this: "is there any way of getting back the exact SQL string that was sent to the DB?" The rest is context.
z
yeah this is something i wanna improve
i think this is what you're after? https://luceeserver.atlassian.net/browse/LDEV-3381
a
Ah yeah exactly. so currently: not possible then, yeah?
z
unless you use a query listener....
public function after(args,caller,meta) {}
a
uuh?
(off to a meeting... back in an hour or so...)
z
in your application.cfc 
this.query.listener=component {
public function after(args,caller,meta) {}
}
all the fancy new ones are just a cfml wrapper around the cfquery tag, got an example, obv we'd like to smash any bugs
a
@zackster thanks for that tip re the query event handlers... def gave me the info I need, and I can now repro the situation.
So... this is slightly convoluted. A user has entered a value on a form that is 
Olivia ('Ollie')
(it's their cat's name). CFWheels's "ORM" then cocks that up, and the ensuing SQL fragment ends up being:
Copy code
AND col2 = <cfqueryparam value="Olivia ('Ollie" cfsqltype="cf_sql_varchar">)'
(Note how it's mis-parsed the value due to an inability to deal with single quotes properly, and half the value is in the param, and half is after it). This results in the entire SQL statement being:
Copy code
SELECT *
FROM test
WHERE
col1 = <cfqueryparam value="val1" cfsqltype="cf_sql_varchar">

AND col2 = <cfqueryparam value="Olivia ('Ollie" cfsqltype="cf_sql_varchar">)'

AND col3 = <cfqueryparam value="val3" cfsqltype="cf_sql_varchar">
AND col4 = <cfqueryparam value="val4" cfsqltype="cf_sql_varchar">
When this is passed to the DB driver, it's like this:
Copy code
SELECT *
FROM test
WHERE
col1 = ?

AND col2 = ?)'

AND col3 = ?
AND col4 = ?
It seems the driver doesn't care that that string starting with the last 
'
isn't terminated, and is seeing this as a string value:
Copy code
AND col3 = ?
AND col4 = ?
So those 
?
are not being seen as param markers. However Lucee is dutifully passing all four 
<cfqueryparam>
values through, as it should. The DB driver sees two param markers, but has four param values. Error. So Lucee's doing nowt wrong, and nor is the DB driver... it's reporting things exactly as they are. It's just a quirky result of CFWheels messing up. --- Now if only CFWheels... oh... [sigh]... never mind. I don't have any expectations of CFWheels doing things right once we stray away from completely obvious stuff.
r
@Adam Cameron Are you submitting this into the database or doing a search for that value in the column? Is this mysql8? I assume since you'r eshowing select you are querying it based on an exact match to that value.
a
8.0.22-13
Don't quite understand what specifically you are asking about re "Are you submitting this into the database..." I mean... I posted the repro query. It's a 
SELECT
as you say, so... I cannot decipher exactly what yer asking as to me it seems like you answered your own question. What am I missing?
r
I just asked because if it was an insert I wanted to see what my value looked like in the database in case the insert modified the final value in the db. I certainly have seen values get truncated on single ticks in sql insert
I just tried it and the insert looks good so know I want to see if I can select it and duplicate your issue.
@Adam Cameron Well, It inserted just fine and did break on select. Can't believe I haven't seen this before because I have apps where people submit all kinds of craziness.
I do see the sql on dump though:
Copy code
WHERE
(
core_trees.treename =
'Olivia (''Ollie'
)
) AND (
core_trees.deletedAt IS NULL
)
a
Oh sorry are you like trying to assess the CFWheels side of things or something? It's from a call to 
exists
, where we're passing in some SQL in the 
where
parameter value. And Wheels - aaah bless - thinks it knows how to parse SQL like it's seen the grown-ups do. It happens in the shitfuckery that is this mess: https://github.com/cfwheels/cfwheels/blob/v1.4.5/wheels/model/adapters/Base.cfc#L302
r
Yes, I was trying it using wheels orm.
a
And did you replicate it? Are you using 2.x or 1.x? We're on 1.x and at some point I got the impression this had been fixed in 2.x..?
r
It broke for me on latest. I'm interested in how it can be fixed. I see in your version it looks like they are cfqueryparam all the values
Copy code
<cfqueryparam attributeCollection="#loc.queryParamAttributes#">
Your example search string is great. I always strip that stuff when submitted but I agree it should work. If the pets name is olivia or ollie choose one or the other when you search:)
a
Nono, this is from a user adding a new pet; we've got some code that subsequently loops back and does some check on it again afterwards (TBH I'm not familiar with the code thee, so dunno what it's doing or why; it's probably legit).The user has entered their pet as Olivia / Ollie, and that's fine. I guess it would remove ambiguity when they're talking to the person @ the vet practice and potentially forget what name they registered. It's a legit value, and I'm not gonna ask the bods who handle the front end "oh please don't put stuff that'll be tricky for CFWheels to piss about with because ugh"
And.. damn... I was hoping to lift the "fixed" code from 2.x and backport to our version. Ah well. Cheers for confirming though.
r
Understood. And on cfwheels latest when you add the value Olivia ('Ollie') in a form field to add a new record it worked perfectly without error. It broke for me when doing a find with that value submitted.
a
This is coming in from a POST req, but not via a form.
(but can't see how that matters to the code... just that the original form that gathers the info before being sent via a separate POST req is outwith my control)
This is a server-to-server thingey
r
Sucks you'll have to skip the orm on this query and write your own. I'll let you know if I ever get a fix not that it will help you now.
a
@risto I could not find an issue for this in GitHub y/day, and you don't seem to have raised one (I dunno if you are on the CFWheels team or just a helpful community member?). Is there an issue? If not, want me to raise one? I am not at all bothered about having to use a situation-specific 
<cfquery>
for things that aren't 100% straight-forward. For anything other than obvious stuff, I find Wheels' "ORM" to be less effective/clear than just an SQL statement, if I am to be frank.
Open in Slack
PreviousNext
Powered by