`SimpleJdbcInsert` does not quote table and column names [SPR-9236]

[spring-projects-issues]

Michel Zanini opened SPR-9236 and commented

If you have table or column names that are reserved SQL words, like "varchar", "values", "integer", etc... the generated insert SQL must have escaping.

For example, now SimpleJdbcInsert is generating this SQL:

INSERT INTO crud (text, integer, char, datetime, decimal, varchar) VALUES(?, ?, ?, ?, ?, ?)

but it should be generating this:

INSERT INTO `crud` (`text`, `integer`, `char`, `datetime`, `decimal`, `varchar`) VALUES(?, ?, ?, ?, ?, ?)

As a workaround I am able to make it work adding the escape char "`" to the column names like this:

simpleJdbcInsert.withTableName("`" + tableName + "`");
simpleJdbcInsert.usingColumns("`" + column1 + "`", "`" + column2 + "`", "`" + column3 + "`");

but I believe SimpleJdbcInsert should support this out of the box. For example, it could have a boolean property like this:

simpleJdbcInsert.withTableName(tableName);
simpleJdbcInsert.usingColumns(column1, column2, column3);
simpleJdbcInsert.usingEscaping(true);

and then SimpleJdbcInsert would do the escaping for me.

---

Affects: 3.1.1

4 votes, 6 watchers

[spring-projects-issues]

Michel Zanini commented

Actually, escaping the columns like shown here does NOT work. It is inserting null values for all columns.

[spring-projects-issues]

Chris Beams commented

Added @thomas.risberg as watcher. Thomas, could you comment here?

[spring-projects-issues]

Thomas Risberg commented

That escaping would only work for MySQL. Would be better to add support for quoted identifiers and in that case we would have to make sure we match the case used for the column names.

We could add
usingQuotedIdentifiers(true)

and generate something like this:

INSERT INTO "CRUD" ("text", "integer", "char", "datetime", "decimal", "varchar") VALUES(?, ?, ?, ?, ?, ?)

We would need to get the quote character to use from the database metadata --http://docs.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getIdentifierQuoteString%28%29

Seems like a worthwhile improvement though.

-Thomas

[spring-projects-issues]

Michel Zanini commented

I agree. Looking forward to have this.

[spring-projects-issues]

Lephix Long commented

Why this issue last so long?

[spring-projects-issues]

reda abdi commented

any workaround?

[spring-projects-issues]

TotoTata commented

Really ? Since 2012 and no corrections ? There are 1000+ MySQL keywords so making column name without it is nearly impossible, so using SimpleJdbcInsert is also impossible.
It's not that hard to had ' in created SQL query.
I can't understand how a bug like this can still be here.

I'm using this for workaround:

String INSERT_SQL = "insert into CUSTOMER (NAME) values (?)";
KeyHolder key = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {

      @Override
      public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        final PreparedStatement ps = connection.prepareStatement(INSERT_SQL, 
            Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, name);
        return ps;
      }
    }, key);
    return key.getKey().longValue();

We need the same functionality for PostgreSQL because we use a mix of uppercase and lowercase letters for table names and we need to escape them with double quotes. When I set table name with double quotes like so

new SimpleJdbcInsert(dataSource)
                .withTableName("\"Table_Name\"");

I see a log message that metadata for table not found.