java - Using dynamic table names, correctly quoted, in JdbcTemplate - Stack Overflow

admin管理员组

文章数量:1390784

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.

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.

• java
• sql
• spring-jdbc

Share Improve this question Follow asked Mar 12 at 17:16 Fabrice GaboldeFabrice Gabolde 33522 silver badges1111 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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 (Patternpile("[\\p{Alpha}][\\p{Alnum}_]*").matcher(identifier).matches()) {
        return alwaysQuote ?  "\"" + identifier + "\"" : identifier;
    }
    if (identifier.matches("^\".+\"$")) {
        identifier = identifier.substring(1, len - 1);
    }
    if (Patternpile("[^\u0000\"]+").matcher(identifier).matches()) {
        return "\"" + identifier + "\"";
    } else {
        throw new SQLException("Invalid name");
    }
}

本文标签： javaUsing dynamic table namescorrectly quotedin JdbcTemplateStack Overflow