How to correctly insert a jsonb into postgresql using a Java PreparedStatement

16 Views Asked by At

I have a postgresql database table mytable with a jsonb column mycolumn. Now, I would like to insert a new row using Java and a PreparedStatement

public void insertRow(JSONObject json, Connection connection) {
   PreparedStatement ps = connection.prepareStatement("INSERT INTO mytable (mycolumn) VALUES (?));
   ps.setObject(position++, json, Types.OTHER);
   ps.executeUpdate();
}

However, if the json contains a single quote ' somewhere, like this example:

{
    "foo": "'bar (notice the single quote in front)"
}

this results in an org.postgresql.util.PSQLException: Unterminated string literal started at position...

Thus, this could be exploited for SQL injection despite having used a PreparedStatement. How to correctly escape the JSON data?

Postgres-Driver: 42.7.1 Open-JDK: 17

0

There are 0 best solutions below