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