Search code examples
javasqliteignitedatabase-backupschange-data-capture

Escaping Java variables to serialise SQL statement to string

We have an event system producing database events for change data capture.

The system sends an event which contains the INSERT or UPDATE statement with ? placeholders and an array of the ordered values matching each question mark.

I want to use this for per hour backup files so if I get a statement like:

insert into T0(a,b,c) VALUES(?,?,?)

with an array of values 1, 2 and it's his then I write the a line to the backup file for that hour as

insert into T0(a,b,c) VALUES(1,2,'it\'s his');

A few things:

  1. Is it only strings that need escaping? We don't have or allow binary columns
  2. Is there a Java library that can do this already (from the Spring eco-system, Apache or otherwise)?
  3. I've seen the Postgres JDBC code for escaping https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/core/Utils.java - is that sufficient?

I was also thinking of creating a SQLite database for each hour, writing to SQLite and then dumping it to the hr.sql text file. This has the advantage of capitalising on all the hardwork and thought already put into SQLite handling escaping but feels like overkill if there's a way to do the toString in Java then append a line to the file.

There's a performance consideration in using SQLite as well furthering my hesitation to that that route.

Solution

  • Found some options.

    Postgres JDBC driver is this https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/core/Utils.java and other impl. is even more simple https://github.com/p6spy/p6spy/blob/master/src/main/java/com/p6spy/engine/common/Value.java#L172 literally doing

    ESPECIAL_CHARACTER_PATTERN.matcher(stringValue).replaceAll("''")

    Where private static final Pattern ESPECIAL_CHARACTER_PATTERN = Pattern.compile("'");

    In both cases, only strings need this as I thought and binary is handled separately but we don't have/need binary.

    Digging further I rediscovered ESAPI https://github.com/ESAPI/esapi-java-legacy They have a lib for escaping SQL https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html#defense-option-4-escaping-all-user-supplied-input

    https://github.com/ESAPI/esapi-java-legacy/blob/develop/src/main/java/org/owasp/esapi/codecs/MySQLCodec.java