I have to escape a parameters to avoid SQL injection problems. I have a big CriteriaBuilder SQL where I find the next:
Expression<Integer> containsFunction = cb.function("CONTAINS", Integer.class,
joinParty.get(MyEntity_.name), cb.literal(sb.toString())
);
This "sb" is a StringBuilder where the SQLi are. Anyway, is a uncommon (unknowed to me) sentence in this operación "DEFINEMERGE" that have concated arguments:
StringBuilder sb = new StringBuilder("DEFINEMERGE(((NEAR((");
for (int i = 0; i < nameValues.length; i++) {
sb.append("{").append(nameValues[i]).append("}");
if(i + 1 < nameValues.length) {
sb.append(",");
}
}
sb.append("),0)),(").append(nameValues[0]).append(" AND {").append(nameValues[1]).append("})");
if(nameValues.length > 3) {
sb.append(",(").append(nameValues[1]).append(" AND {").append(nameValues[2]).append("})");
if(nameValues.length == 4) {
sb.append(",(").append(nameValues[2]).append(" AND {").append(nameValues[3]).append("})");
}
}
sb.append("), AND, MIN)");
Problem is that some fo the nameValues have a "(" inside, breaking the SQL. I am not sure what is the best way of securize this values in this case because this CONTAINS works with a SQL String literal, not an criteria object.
This is the generated SQL expected:
CONTAINS(
table.name,
'DEFINEMERGE (
(
(NEAR( (?, {?},{?}), 0, FALSE)),
(? AND {?} and {?})
),
AND,
min
)'
,
1
)
This is an example of the generated query (hidden real names of tables and fields for company privacy and security) This have the parameter bind array too:
SELECT
COUNT(t0.<VALUE>)
FROM
SCHEME.<TABLE_NAME> t0
LEFT OUTER JOIN SCHEME.<TABLE_NAME> t2 ON (t2.<VALUE> = t0.<VALUE>)
LEFT OUTER JOIN SCHEME.<TABLE_NAME> t6 ON (t6.<VALUE> = t2.<VALUE>)
LEFT OUTER JOIN SCHEME.<TABLE_NAME> t7 ON (t7.<VALUE> = t6.<VALUE>)
LEFT OUTER JOIN SCHEME.<TABLE_NAME> t8 ON (t8.<VALUE> = t7.<VALUE>)
LEFT OUTER JOIN SCHEME.<TABLE_NAME> t3 ON (t3.<VALUE> = t2.<VALUE>)
LEFT OUTER JOIN SCHEME.<TABLE_NAME> t4 ON (t4.<VALUE> = t3.<VALUE>)
LEFT OUTER JOIN SCHEME.<TABLE_NAME> t5 ON (t5.<VALUE> = t4.<VALUE>),
SCHEME.<TABLE_NAME> t11,
SCHEME.<TABLE_NAME> t10,
SCHEME.<TABLE_NAME> t9,
SCHEME.<TABLE_NAME> t1
WHERE
((((((((((((((((t0.<VALUE> IN (?)) AND (t0.<VALUE> IN (?))) AND (t6.<VALUE> = ?)) AND (t3.<VALUE> IN (?))) AND ((t2.<VALUE> IS NULL) OR (t2.<VALUE> = t9.<VALUE>))) AND (t0.<VALUE> = ?)) AND (CONTAINS(t1.<VALUE>, ?) > ?)) AND (t0.<VALUE> IN (?))) AND (t1.<VALUE> = ?)) AND t0.<VALUE> IN (SELECT t12.<VALUE> FROM SCHEME.<TABLE> t14 LEFT OUTER JOIN SCHEME.<TABLE> t16 ON (t16.<VALUE> = t14.<VALUE>), SCHEME.<TABLE> t13, SCHEME.<TABLE> t12, SCHEME.<TABLE> t15 WHERE ((((((t13.<VALUE> IN (?)) AND (t14.<VALUE> IN (?))) AND (t14.<VALUE> <= ?)) AND ((t14.<VALUE> IS NULL) OR (t14.<VALUE> >= ?))) AND (t16.<VALUE> = ?)) AND (((t14.<VALUE> = t13.<VALUE>) AND (t12.<VALUE> = t13.<VALUE>)) AND (t15.<VALUE> = t14.<VALUE>))))) AND (t9.<VALUE> IN (?))) AND (t11.<VALUE> IN (?))) AND (t0.<VALUE> = t11.<VALUE>)) AND (t9.<VALUE> <= ?)) AND ((t9.<VALUE> IS NULL) OR (t9.PARO_DA_END_VALIDITY >= ?))) AND (((t9.<VALUE> = t0.<VALUE>) AND (t1.<VALUE> = t0.<VALUE>)) AND (t10.<VALUE> = t9.ROTY_ID_ENGAGED_ROLE_SPEC)))
bind => [1, INDI, 1, 1, 1, DEFINEMERGE(((NEAR(({(name},{name)}),0)),((name AND {name)})), AND, MIN), 0, 1, 1, 1, 1, 2020-09-07 00:00:00.0, 2020-09-07 00:00:00.0, 1, 1, 1, 2020-09-07 00:00:00.0, 2020-09-07 00:00:00.0]