How to prevent injection when user is supplying an arbitrary SQL identifier?

Question

I need to run LISTEN channel_name against a PostgreSQL database using JDBC, where channel_name is supplied by a user in a web app.

channel_name can be any PostgreSQL identifier.

I don't believe I can use parameterization, and there is no white-list to check against.

How can I allow a user to do so safely?

I considered regexp, but I was wondering if there was anything pre-built, as I don't want to make a mistake.

Current code (doesn't support quoted identifiers or non-ascii chars):

public String checkIdentifier(String value) {

    if (!value.matches("(?i)^[a-z_][a-z_0-9\\$]{0,63}$")) {
        throw new RuntimeException("Not a valid SQL identifier.");
    }
    return value;
}

Is there no "quote identifier" method available in JDBC? I suppose you could write a tiny PL/pgSQL function that used [`quote_ident`](http://www.postgresql.org/docs/current/interactive/functions-string.html#FUNCTIONS-STRING-OTHER) and some dynamic SQL but that seems unnecessarily complicated. — mu is too short, Apr 26 '14 at 19:25
There's a DatabaseMetaData.getIdentifierQuoteString() function but I think that only returns a " — Neil McGuigan, Apr 26 '14 at 19:27
Yeah, Craig Ringer's comments on [**How to quote/escape identifiers such as column names with JDBC?**](http://stackoverflow.com/q/2038169/479863) suggest that there's nothing in JDBC that does the Right Thing. — mu is too short, Apr 26 '14 at 19:30
Fortunately, it's rather easy to escape identifiers; just replace `"` with `""`, then stick `"`s on both ends. (Note, this comment only applies if your DB didn't decide to say "screw standards; i'll escape however i want" like MySQL did. :P Modern PostgreSQL does the right thing, though.) — cHao, Apr 26 '14 at 19:38

score 0 · Answer 1 · answered Apr 26 '14 at 19:05

0

Use a RegExp to parse the channel_name. This way you can verify that the user entry is in a right format and don'y contain any special character that could affect your request.

answered Apr 26 '14 at 19:05

Emrys Myrooin

2,179
14
39

Is there something pre-built? It's vital that there are no mistakes. – Neil McGuigan Apr 26 '14 at 19:07
I don't know your language but there is a implementation of regular expression in almost every language. Look for the doc of your language. – Emrys Myrooin Apr 26 '14 at 19:09
@EmrysMyrooin: I'm fairly certain he meant "some pre-built way to do what i want", not a pre-built RE api. – cHao Apr 26 '14 at 19:32

Denis de Bernardy · Accepted Answer · 2014-04-28T05:37:09.963

For the note, your current approach is the only sane one if you don't want warts in your database imo.

The built-in Postgres way, if you insist on using it, is to use quote_ident() as indicated in the comments. But be wary, because in that case inputs such as "(?!: . become valid identifiers. psql will handle that well (hopefully), but it can be a pain to type identifiers like that.

Perhaps your ORM has a wrapper for the latter function -- if not, and as highlighted by cHao it's about " character handling.

If it's a tablename, you could also cast the thing to regclass in order to ensure the identifier exists.

How to prevent injection when user is supplying an arbitrary SQL identifier?

2 Answers2