0

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.

zcourts
  • 4,863
  • 6
  • 49
  • 74
  • 3
    JDBC already handles the escaping. Check out [PreparedStatement](https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html). – Marc Baumbach Jun 18 '21 at 16:33
  • I don't want to execute the prepared statement though, I want to serialise it to string. Did I miss an API on the prepared statement class to do this? As far as I know you'd normally call `executeUpdate` or something similar...that's not what I want. The query is already being sent to the database which will handle it. This is a backup solution outside the DB...since it doesn't have one that supports what we want to do with backups. – zcourts Jun 18 '21 at 16:49

1 Answers1

0

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

zcourts
  • 4,863
  • 6
  • 49
  • 74