Is it possible to escape a ? in a queryExecute call?
Currently I build a SQL string and strSQL structure, then pass these
into a queryExecute call.
However I now have a SQL call that builds a link, including a ?. When
this runs I get a java.lang.IndexOutOfBoundsException error, as it’s
assuming the ? is coming from an array (which is not passed in as I’m
passing a structure in).
I’ve tried ?? and \? both have the same result.
I’ve briefly changed it so that the string built is no 'blah' + Char(63) 'rest' but I’m still wondering if a literal ? can be used.
<cfscript>
SQL = "
select
[cName],
'page.cfm?id=' + Cast([cId] as nvarchar(5)) + '&foo=3'
from
[dbo].[Campaigns]
where
[cId] = (:id)
";
strSQL = {
Id = {value=1, cfsqltype="cf_sql_integer"}
};
qryCampaigns = queryExecute(SQL, strSQL, {datasource="DSHere"});
writeDump(qryCampaigns);
</cfscript>
Then I get an "java.lang.IndexOutOfBoundsException" error. If I remove
the ? then the query runs fine.
I assume it’s because the parser is seeing the ? and expecting an array to
be passed in as the second parameter of queryExecute, but as I’m using
named parameters; I’m passing in a struct.
Is it possible to escape the ? so that the parser just sees it as a SQL
text?
As mentioned, I’ve tried \? and ?? to see if that would escape it, but it
doesn’t.
Agreed. But it does look like there’s an underlying issue in Lucee,
specifically this file:
I don’t have the environment set up to build and test, but I’m guessing
that line 158 should be changed to:
if(c=='?' && !inside) {
In the block above, it’s ignoring question marks within single or double
quotes, but if it then converts a named param to a ?, it will currently
then act on any question mark, whether of not it’s within quotes…
If I get a few minutes (!) later I might try to get Lucee building set up
on my laptop so I can play around with this sort of thing!
Sorry to open this old thread, but I think that this has never been resolved, although I really think that it needs to be addressed for certain use cases. In my case I’m trying to pass a regex containing a questionmark for MySQL’s REGEXP operator in a queryExecute.
This is causing an error:
qFoo=queryExecute(
sql="SELECT nodeid
FROM `text`
WHERE rawtext REGEXP '\\[img\\]https?://imgproxy\\.foo\\.com/' "
, options={ datasource : ds }
);
In the latter case the removal of the usual double escaping of backslashes in the regex is necessary, as the regex string is now passed as query parameter und thus no longer part of the actual query string. This makes writing regex somewhat easier than passing them inline with the double escaping.
Should I file a bug for this or is this working as intended?
In the SQL string just type #jsStringFormat(qryCampaigns.cId)# or even more if the incoming vars. I did not test is now, but the jsStringFormat should be able to escape all those chars with ease.
