java sql prepared statement doesn't escape correctly for postgresql

Question

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.

You can't use ? parameter markers to inject identifiers such as table and column names. You can only inject values. You have to use string concatenation to insert the column names, then ? to handle the values. — Andreas
– Andreas, Commented Jun 23, 2021 at 2:07
Please edit your question and show us the complete SQL String you are building. — user330315
– user330315, Commented Jun 23, 2021 at 5:11
@Andreas is there anyway to let sql safely inject table name or column name? since these are still runtime value I don't know whether I need to escape them — Ziqi Liu
– Ziqi Liu, Commented Jun 23, 2021 at 5:28
values ("ddf") would be wrong to begin with as "ddf" refers to a column name. It's not a SQL string constant — user330315
– user330315, Commented Jun 23, 2021 at 5:36
Is the table and column names coming from a trusted source? If yes, e.g. a hard-coded list somewhere else in the code, then you don't need escaping. If no, e.g. they are header names from a CSV file, then you absolutely need to validate or escape the names. --- If you intend the names to be unquoted (recommended), then you should simply validate that the names only consist of valid characters, which basically means matching regex \w+. — Andreas
– Andreas, Commented Jun 23, 2021 at 13:23

Laurenz Albe · Accepted Answer · 2021-06-23 13:25:00Z

1

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.

edited Jun 23, 2021 at 13:25

answered Jun 23, 2021 at 4:02

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andreas Over a year ago

OP never wrote $1, the JDBC driver replaced ? with $1.

Laurenz Albe Over a year ago

@Andreas Ah, I see, yes. I have fixed the answer.

Collectives™ on Stack Overflow

java sql prepared statement doesn't escape correctly for postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related