shashank created CAMEL-25353:
--------------------------------
Summary: camel-sql - a :#in: parameter whose name is the start of
a later :#in: name breaks the later one ("in (?,?License)"), and :#in:${...}
with [ or ( is not expanded
Key: CAMEL-25353
URL: https://issues.apache.org/jira/browse/CAMEL-25353
Project: Camel
Issue Type: Bug
Components: camel-sql
Reporter: shashank
{{DefaultSqlPrepareStatementStrategy.prepareQuery}} finds each {{:?in:<name>}}
with {{REPLACE_IN_PATTERN}} and then replaces every match of the regular
expression {{":?in:" + name}} in the whole query:
{code:java}
Matcher paramMatcher = Pattern.compile("\\:\\?in\\:" + foundEscaped,
Pattern.MULTILINE).matcher(query);
query = paramMatcher.replaceAll(replace);
{code}
The regular expression has no word boundary, so when the name of an IN
parameter is the start of the name of a later one, the first replacement also
replaces the start of the later parameter:
{code:sql}
select * from projects where project in (:#in:project) and license in
(:#in:projectLicense)
-- becomes
select * from projects where project in (?,?) and license in (?,?License)
{code}
and the statement fails with a syntax error (H2: {{Syntax error in SQL
statement ... in (?,?[*]License)}}). Pairs such as {{id}} / {{ids}}, {{status}}
/ {{statusList}}, {{type}} / {{types}} are common; the order matters (the
shorter name first). Only {{$}}, {{\{}} and {{\}}} are escaped in the name, so
an expression with other metacharacters, such as {{:#in:$\{body[names]\}}} (a
Map body), does not match itself and is left in the SQL ({{in
(?:$\{body[names]\})}}).
h3. Reproduction
New {{SqlProducerInParameterNamesTest}} (H2, the {{projects}} table of the
module): {{:#in:project}} with {{:#in:projectLicense}}, and
{{:#in:$\{body[names]\}}}, fail with a syntax error; the same query with
{{:#in:projects}} and {{:#in:licenses}} is the control. Two runs on main.
h3. Proposed fix
Replace each match where it is, in one pass ({{Matcher.appendReplacement}}),
with as many placeholders as its own parameter has values; a parameter without
a value is left as it was. The prepared SQL is unchanged for every query that
worked (a name repeated in the query is looked up per occurrence, as the old
loop also did). No upgrade note (only queries that failed change). camel-sql:
302 tests, all pass except {{SqlFunctionDataSourceTest}} (2), whose embedded
MariaDB cannot start on this machine ({{Library not loaded:
/opt/homebrew/opt/pcre2/lib/libpcre2-8.0.dylib}}, independent of the change:
the same two tests fail with main's code; stored functions do not use
{{prepareQuery}}).
Found with a Lean 4 model of the loop of {{replaceAll}} calls and of the
one-pass replacement: "each IN list has the number of values of its own
parameter" fails on main for every number of values when the first name is a
prefix of the second (the rest of the longer name stays in the SQL), and the
fix gives the same SQL as main exactly when the first name is not a proper
prefix of the second (checked exhaustively over five names, both orders and 1
to 3 values).
Affected: 4.14.x, 4.18.x and main (checked); CAMEL-10499 fixed a different
problem with two IN parameters.
Duplicate check (2026-10-05): JIRA camel-sql with prefix / IN clause / in
parameter (CAMEL-10499, CAMEL-10151, CAMEL-10154: other causes); GitHub pull
requests "DefaultSqlPrepareStatementStrategy" (#19610 CAMEL-22565 parameter
conversion, #26916 CAMEL-25039); open PRs on camel-sql: #26845 (SqlComponent,
other files).
_Filed with Claude Code on behalf of allthingssecurity._
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
