SQL Escape without PreparedStatements Criteria-API

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]

Unfortunately, there is no guaranteed way to escape SQL, because there really is no such thing as 'SQL'. There's the dialect, and it is a required bit of info to do the job of escaping properly. So, what do you want to escape? 'SQL' is not a workable answer. Workable answers are 'postgres's SQL', 'MySQL's SQL', 'Oracle's SQL', etc. SQL is more like a notion than a direct spec (there IS an actual spec but it contains a lot less than you think it does, and every dialect of SQL out there breaks that spec and adds a ton of things to it).

That's why the normal advice is: really, no, you can't do what you want, you must go via .setX methods on PreparedStatement if you want to escape this stuff.

From your question it looks like you have actual SQL statements as string literals inside your database which is itself a bizarre scenario, and something that will rather easily lead to severe security issues, so whilst you probably don't want to hear it, this design is in need of a complete rework, and it sounds like other code will grab this SQL and then run it verbatim.

Javascript had something like this for a while (eval) and the sheer number of security leaks that occurred because eval is a thing is staggering. There are also now ways to use headers to ban eval in the javascript on your site, and browsers working around bits in the URL appearing inside these, it's that bad - modern security guidelines tell you to entirely disable this feature, and you can't really rely on it working properly if you want to.

Given that it's such a bad idea to handroll SQL escaping, I doubt there are any libraries out there you can use. There is a small chance your JDBC driver (dig into those classes and check the website!) has a utility method that can do it; given that every dialect has different rules, this makes sense: Every SQL engine has a different JDBC driver. Clearly if your JDBC driver ships with an SQL escaping tool, it is the right one for that specific engine.

If you can't find that, the simplest way to escape things in most SQL dialects is to make a whitelist of allowed characters, and to escape every character that isn't on the whitelist. The whitelist should only contain definitely, absolutely safe things (a-z, 0-9, maybe _, -. Nothing that looks like a quote or a backslash should be on the list, and I'd avoid $ just because it's commonly used for variable substitution, which is not a thing in SQL but better safe than your production servers p0wned).

The rest can then be escaped. For example, in postgres, you'd turn the string:

Joe's bar & Grill

into

E'Joe\u0027s bar \u0026 Grill'

The E means: String with escapes. The algorithm checks every character and copied over everything on the whitelist. the quote and ampersand weren't on it, so those were replaced by \u0000 where the zeroes are the hexadecimal encoding of the character (the value of s.charAt(i), cast to an int, printed as hex number)>

That should cover all your bases, but note that (sigh) escape strings are entirely outside of the SQL spec, it's a postgresism.