|The jOOQ User Manual : SQL building : The DSLContext API : Custom Settings : Backslash Escaping||previous : next|
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Some databases (mainly MySQL and MariaDB) unfortunately chose to go an alternative, non-SQL-standard route when escaping string literals. Here's an example of how to escape a string containing apostrophes in different dialects:
SELECT 'I''m sure this is OK' AS val -- Standard SQL escaping of apostrophe by doubling it. SELECT 'I\'m certain this causes trouble' AS val -- Vendor-specific escaping of apostrophe by using a backslash.
As most databases don't support backslash escaping (and MySQL also allows for turning it off!), jOOQ by default also doesn't support it when inlining bind variables. However, this can lead to SQL injection vulnerabilities and syntax errors when not dealing with it carefully!
This feature is turned on by default and for historic reasons for MySQL and MariaDB.
DEFAULT(the - surprise! - default): Turns the feature
ONfor MySQL and MariaDB and
OFFfor all other dialects
ON: Turn the feature on.
OFF: Turn the feature off.
Settings settings = new Settings() .withBackslashEscaping(BackslashEscaping.OFF); // Default to DEFAULT
<settings xmlns="http://www.jooq.org/xsd/jooq-runtime-3.5.0.xsd"> <backslashEscaping>OFF</backslashEscaping> </settings>