Escaping SQL identifiers

[Murray Cumming]

Is there any general jOOQ method to escape (and maybe quote) SQL
identifiers such as table names and field names for use in raw SQL
queries?

This documentation
http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#field%
28java.lang.String,%20java.lang.Class,%20org.jooq.QueryPart...%29
rightly says "Be sure to properly ... escape literals ...", but it does
not recommend how best to do that.

--
mur...@murrayc.com
www.murrayc.com
www.openismus.com

[Lukas Eder]

Hello Murray,

Yes, that method is the best way to do what you intend. You can do
something along these lines:

create().select(field("length({1})", String.class, fieldByName("TITLE")))
.from(tableByName("T_BOOK"))
.fetch();

I'll add this example to the Javadocs

Cheers
Lukas

2012/5/19 Murray Cumming <mur...@murrayc.com>:

[Murray Cumming]

On Sun, 2012-05-20 at 17:22 +0200, Lukas Eder wrote:
> Hello Murray,
>
> Yes, that method is the best way to do what you intend. You can do
> something along these lines:
>
> create().select(field("length({1})", String.class, fieldByName("TITLE")))
> .from(tableByName("T_BOOK"))
> .fetch();
>
> I'll add this example to the Javadocs

Sorry, I linked to Factory.field() just to show what part of the
documentation contained that quote. But I'm trying to escape (and quote)
arbitrary SQL.

In this case, it's so I can supply the table names and field names to
CREATE DATBASE and CREATE TABLE SQL queries.

Murray

> 2012/5/19 Murray Cumming <mur...@murrayc.com>:
> > Is there any general jOOQ method to escape (and maybe quote) SQL
> > identifiers such as table names and field names for use in raw SQL
> > queries?
> >
> > This documentation
> > http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#field%
> > 28java.lang.String,%20java.lang.Class,%20org.jooq.QueryPart...%29
> > rightly says "Be sure to properly ... escape literals ...", but it does
> > not recommend how best to do that.
> >
> > --
> > mur...@murrayc.com
> > www.murrayc.com
> > www.openismus.com
> >

--
mur...@murrayc.com
www.murrayc.com
www.openismus.com

[Lukas Eder]

> Sorry, I linked to Factory.field() just to show what part of the
> documentation contained that quote. But I'm trying to escape (and quote)
> arbitrary SQL.
>
> In this case, it's so I can supply the table names and field names to
> CREATE DATBASE and CREATE TABLE SQL queries.

After all, an org.jooq.Name type as I had suggested here would indeed
come in handy! :-)
https://groups.google.com/d/msg/jooq-user/SPq6lKX-BtM/Qyrly-JrjC0J

I will implement #1431 in the next release 2.4.0:
https://sourceforge.net/apps/trac/jooq/ticket/1431

It will contain the following method, which can be used with various
plain SQL constructs:
Name Factory.name(String)

Cheers
Lukas

[Murray Cumming]

On Sun, 2012-05-20 at 17:39 +0200, Lukas Eder wrote:
> After all, an org.jooq.Name type as I had suggested here would indeed
> come in handy! :-)
> https://groups.google.com/d/msg/jooq-user/SPq6lKX-BtM/Qyrly-JrjC0J
>
> I will implement #1431 in the next release 2.4.0:
> https://sourceforge.net/apps/trac/jooq/ticket/1431
>
> It will contain the following method, which can be used with various
> plain SQL constructs:
> Name Factory.name(String)

Many thanks.

I guess there will be some way to the get a string from that Name, for
use when building a raw SQL query via string concatenation.

--
mur...@murrayc.com
www.murrayc.com
www.openismus.com

[Lukas Eder]

> Many thanks.
>
> I guess there will be some way to the get a string from that Name, for
> use when building a raw SQL query via string concatenation.

Yes, an org.jooq.Name should expose its underlying name

Cheers
Lukas

[Murray Cumming]

I'm finally trying this, using code like this (with some checks):

Name jooqName = Factory.name(name);
String[] nameParts = jooqName.getName();
String escaped = nameParts[0];

However, I just get the same string that I passed into Factory.name(),
with no escaping.

Also, I would expect to have to mention the SQLDialect (or the
connection) somewhere, because the escaping would depend on the server
used.

--
mur...@murrayc.com
www.murrayc.com
www.openismus.com

[Lukas Eder]

Hi Murray,

2013/2/15 Murray Cumming <mur...@murrayc.com>:

> On Sun, 2012-05-20 at 17:44 +0200, Murray Cumming wrote:
>> On Sun, 2012-05-20 at 17:39 +0200, Lukas Eder wrote:
>> > After all, an org.jooq.Name type as I had suggested here would
>> indeed
>> > come in handy! :-)
>> > https://groups.google.com/d/msg/jooq-user/SPq6lKX-BtM/Qyrly-JrjC0J
>> >
>> > I will implement #1431 in the next release 2.4.0:
>> > https://sourceforge.net/apps/trac/jooq/ticket/1431
>> >
>> > It will contain the following method, which can be used with various
>> > plain SQL constructs:
>> > Name Factory.name(String)
>>
>> Many thanks.
>>
>> I guess there will be some way to the get a string from that Name, for
>> use when building a raw SQL query via string concatenation.
>
> I'm finally trying this, using code like this (with some checks):
>
> Name jooqName = Factory.name(name);
> String[] nameParts = jooqName.getName();
> String escaped = nameParts[0];
>
> However, I just get the same string that I passed into Factory.name(),
> with no escaping.

Yes, these string literals are the raw data. In order to apply
escaping, you have to "render" them as SQL. For instance:

Name jooqName = Factory.name(name);
new Factory(dialect).render(jooqName);

See the relevant Javadoc here:
http://www.jooq.org/javadoc/latest/org/jooq/impl/Factory.html#render(org.jooq.QueryPart)

> Also, I would expect to have to mention the SQLDialect (or the
> connection) somewhere, because the escaping would depend on the server
> used.

The SQLDialect is always available in the context of a "Configuration"
(e.g. a Factory). jOOQ's QueryParts are dialect-independent. Some
information about this can be found here (and in subsequent sections):
http://www.jooq.org/doc/2.6/manual/sql-building/factory/

Cheers
Lukas

[Murray Cumming]

Thanks. That works great.

Murray