Using dynamic table names, correctly quoted, in JdbcTemplate

Question

I know about prepared statements and binding parameters and why you can't have the table name be a placeholder (needed for query planning), SQL query building by concatenating strings (don't do it), etc.

I still need my table name to be dynamic. I'm building a simple service: I have a bunch of table names (unquoted) in a list, I need to loop over them and do SELECT COUNT(*) FROM <database>.<schema>.<table>, and write the count somewhere.

Of course I can use the techniques in other Q&A (e.g. JdbcTemplate dynamic table name and SQL injection) to get the table name in there, but it may contain any character that is valid within an identifier. foo, foo"bar, foo; DROP TABLE Students, etc. They were originally fetched from INFORMATION_SCHEMA. The database and schema name are more predictable and probably don't even need quoting, but still.

The only method that correctly (*) quotes them is Statement's enquoteIdentifier, but I'm using JdbcTemplate which doesn't expose this easily. Best I could find is using one of the JdbcTemplate methods that uses a PreparedStatementCreator, so I can start from a Connection, get a Statement, using which I can quote the identifier; then throw it away, build a PreparedStatement from the Connection. I haven't tested this yet but it seems very awkward to use.

Is there a better, simpler way? I like jOOQ, but it seems overkill to add it as a dependency for something that is really just one SELECT COUNT(*).

(*) "Correctly" meaning I don't have to write a method that escapes quotes, escapes escape characters, and wraps the identifiers in quotes. Actually, does enquoteIdentifier even know about the session's escape characters? I'm going to assume that it's set to the default and the driver knows the default.

scrhartley · Accepted Answer · 2025-03-13 17:48:13Z

1

Statements are created per query from the Connection and although you can look at the source of JdbcTemplate and recreate this you probably don't want to go that low level.
I don't think that PgStatement overrides the default Statement implementation of enquoteIdentifier, so my advice is just to copy the source from enquoteIdentifier into your own project:

public static String enquoteIdentifier(String identifier, boolean alwaysQuote) throws SQLException {
    int len = identifier.length();
    if (len < 1 || len > 128) {
        throw new SQLException("Invalid name");
    }
    if (Pattern.compile("[\\p{Alpha}][\\p{Alnum}_]*").matcher(identifier).matches()) {
        return alwaysQuote ?  "\"" + identifier + "\"" : identifier;
    }
    if (identifier.matches("^\".+\"$")) {
        identifier = identifier.substring(1, len - 1);
    }
    if (Pattern.compile("[^\u0000\"]+").matcher(identifier).matches()) {
        return "\"" + identifier + "\"";
    } else {
        throw new SQLException("Invalid name");
    }
}

answered Mar 13 at 17:48

scrhartley

1,6421 gold badge4 silver badges10 bronze badges

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

3 Comments

Fabrice Gabolde Mar 14 at 14:26

I'm surprised this is that simple. I'd expect the implementation to vary per driver. After all, a correct enquoteIdentifier needs to know: the current session's escape character; the database engine's supported quote characters (backticks, double quotes, brackets?...); what constitutes an identifier that needs to be quoted (e.g. reserved words vary per database engine). Is this not a default implementation that drivers are supposed to provide a more specialized version of?

scrhartley Mar 14 at 16:54

As I said above, PgStatement which implements Statement in the postgres driver does not override the implementation of enquoteIdentifier and so you would end up with the default version anyway. This may be because certain aspects of the SQL standard are common. I can spot that the max name length may be too big, but this depends upon the postgres version. In the current version of postgres the max length is 63, but it doesn't care and will truncate longer identifiers.

Fabrice Gabolde Mar 20 at 9:42

I missed somehow in your answer that you were already talking about PgStatement and not plain Statement. You're right, there is no better way, using just the java.sql tools, to quote identifiers: either copy the method or make a throwaway Statement just to get access to it. I'm pretty disappointed in enquoteIdentifier after reading up. The javadoc even says it will throw an exception if it encounters a double quote in the identifier, despite this being escapable, at least in PG. As comparison, jOOQ knows about the dialect's reserved identifiers and correctly escapes double quotes.

Collectives™ on Stack Overflow

Using dynamic table names, correctly quoted, in JdbcTemplate

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related