[
https://issues.apache.org/jira/browse/CAMEL-25353?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
shashank reassigned CAMEL-25353:
--------------------------------
Assignee: shashank
> 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
> Assignee: shashank
> Priority: Minor
>
> {{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)
