CODEWITHSUNDEEP
javapostgresql
Ziqi Liu
Ziqi Liu

Reputation: 3171

java sql prepared statement doesn't escape correctly for postgresql

I was using PreparedStatement in Java to create sql command for postgresql, however all placeholder are escape with single quote, which is different from postgresql requirement where double quote is used.

below this the log and error msg

2021-06-22 18:33:08,157 INFO  [ai.tus.map.fea.per.uti.MapperHelper] (executor-thread-199) Insert prepared statement: wrapped[ insert into "known_fruits" (?) VALUES (?) ]
2021-06-22 18:33:08,158 INFO  [ai.tus.map.fea.per.uti.MapperHelper] (executor-thread-199) Insert statement: wrapped[ insert into "known_fruits" ('name') VALUES ('ddf') ]
2021-06-22 18:33:08,159 ERROR [ai.tus.map.fea.FruitResource] (executor-thread-199) Failed to handle request: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 29
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
        at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
        at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:130)
        at io.agroal.pool.wrapper.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:88)

the corresponding code is:

        LOGGER.info("Insert prepared statement: " + stmt.toString());
        for (int i = 1; i <= fieldNum; ++i) {
            stmt.setString(i, existFields.get(i - 1).name);
            stmt.setObject(i + fieldNum, existFiledVals.get(i - 1));
        }
        LOGGER.info("Insert statement: " + stmt.toString());

edit:

the sql string I wanted to build:

insert into "known_fruits" ("name") VALUES ("ddf")

the sql string I put into PreparedStatement with placeholder is:

insert into "known_fruits" (?) VALUES (?)

the final sql string I got is:

insert into "known_fruits" ('name') VALUES ('ddf')

as @Andreas pointed out, I should not use ? for table name or column name, they're only for value injection. So my question would be is there any approach to safely make sql handle escaping regaridng table name or column name? Since these value are runtime-determined, I don't know whether there would be some table name same as sql keyword that need to be escaped.

Upvotes: 1

Views: 267

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246113

You cannot use a parameter for the column name, like in

insert into "known_fruits" (?) VALUES (?)

You will have to construct a query string that contains the column name as a literal.

Upvotes: 1

Related Questions