How to quote/escape identifiers such as column names with JDBC?

Question

Different database servers use different ways to quote and escape identifiers.

E.g. "foo bar" vs `foo bar` vs [foo bar], or "10""" vs "10\"", or identifiers such as FooBar or array need to be quoted for some databases but not for others.

Is there any API method that performs the quoting/escaping correctly for a given database connection? Or any alternative solution?

score 14 · Answer 1 · answered Jan 10 '10 at 21:24

14

Have a look at

DatabaseMetaData.getIdentifierQuoteString()

I never used it but it sounds good :-)

getExtraNameCharacters() could also be of some help

answered Jan 10 '10 at 21:24

user85421

28,957
10
64
87

1

Not a complete solution, but definitely useful! – aditsu quit because SE is EVIL Jan 11 '10 at 06:07
1

@aditsu I don't understand how is this not a complete solution. – raven Sep 01 '19 at 12:42
1

@raven well, for example it doesn't help at all with escaping – aditsu quit because SE is EVIL Sep 01 '19 at 12:44
@raven, with these methods, a programmer still needs to do the job manually. – Sasha Jan 02 '21 at 09:42
[DatabaseMetaData#getSQLKeywords()](https://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getSQLKeywords()) can also be useful (e.g. to know which strings are unnecessary to quote). – Sasha Jan 02 '21 at 10:15

OrangeDog · Answer 2 · 2022-03-18T10:24:33.970

Since Java 9, the Statement interface provides various methods for engine-specific quoting:

enquoteIdentifier for SQL identifiers (e.g. schema, table, column names)
enquoteLiteral for string literals (e.g. char, varchar, text literals)
enquoteNCharLiteral for National Character Set literals

Statement stmt = connection.createStatement();
String query = String.format(
        "SELECT id FROM %s WHERE name = %s",
        stmt.enquoteIdentifier("table", false),
        stmt.enquoteLiteral("it's"));
ResultSet resultSet = stmt.executeQuery(query);

However, whenever possible (i.e. for values in data queries), use prepared statements instead.

Statement stmtFormat = connection.createStatement();
String query = String.format(
        "SELECT id FROM %s WHERE name = ?", 
        stmtFormat.enquoteIdentifier("table", false);
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, "it's");
ResultSet resultSet = stmt.executeQuery();

Yishai · Accepted Answer · 2010-01-11T02:48:25.280

1

I think the answer to your question is that if you are writing a database neutral application using JDBC, then you need to use database neutral names, and not things that require special escaping per database vendor.

There is nothing I know of in the JDBC which supports that. A ORM product will deal with such things.

Edit: If you are writing an ORM, then I would think need a seperate SQL generation class for each supported database, just to handle the various syntax involved, so you would have to write that. You can certainly look at the source code of the various open source ORM's out there and see how they handle it.

edited Jan 11 '10 at 02:48

answered Jan 10 '10 at 19:14

Yishai

90,445
31
189
263

2

Good point about database-neutral names, but surprises can still appear (e.g. a certain identifier may not be acceptable in another database). Also, what if I'm.. actually writing an ORM? Would it require a separate hand-coded quote/escape implementation for each supported database? – aditsu quit because SE is EVIL Jan 10 '10 at 19:36
1

Stick to the rules for identifier naming in the SQL standard. Those will work anywhere. – Paul Tomblin Jan 10 '10 at 19:44
3

@PaulTomblin Heh, yeah, the "standard". There's a whole lot of flexibility in the spec - and vendors add their own keywords and reserved words, which must be quoted if the application is to use them. I agree that trying to stick to the spec is the least painful option, but it *sucks* that JDBC doesn't expose a `java.sql.Connection.quoteIdentifier(...)` or `DatabaseMetaData.quoteIdentifier(...)` method and backing SPI, as it means you're always doing the reserved-word dance. `getIdentifierQuoteString` doesn't do the job, it doesn't express the quoting rules. – Craig Ringer Feb 09 '14 at 13:30
@CraigRinger I amend my previous comment to "Stick to the standard, AND LOSE THE BAD HABITS YOU PICKED UP FROM USING MySQL - It's an abomination and it's not SQL". I've never had to quote an identifier in 20+ years of doing SQL on Oracle, Sybase, Postgres, even SQLlite. – Paul Tomblin Feb 09 '14 at 13:39
2

@PaulTomblin Heh, dedicated PostgreSQL user here. There's the odd time it's been necessary for me in contexts where Pg's parser struggles to differentiate between an unreserved keyword and a user identifier, but in general it's not overly problematic. I'm just irritated that the JDBC spec makes it harder than it should be for applications to defensively quote identifiers, and for the JDBC driver to expose its knowledge of correct quoting. (Which, unless you're MySQL or MS SQL Server, is ANSI SQL quoting anyway) – Craig Ringer Feb 09 '14 at 13:42
-1, because: (1) there is no such thing as "neutral names" in reality — specific implementations reserve additional words in addition to the common list; (2) [SQL standard defines a way to quote identifiers, but particular implementations may have alternative syntax for it](/q/10573922), (3) this is certainly not the ORM level, much lower (theoretically it might be higher than JDBC, but then JDBC should provide driver-specific details (like [those](/a/2038692), but even those may be insufficient) for doing it at higher levers; happily [JDBC now can do it by itself](/a/64863308)). – Sasha Jan 02 '21 at 10:13

How to quote/escape identifiers such as column names with JDBC?

3 Answers3

Linked