[Geoserver-users] Oracle query column names are not proteced

GeoServer GeoServer User
1

Hello,

I use geoserver 2.2.4 with the oracle plugin and Oracle 11g. If I make a layer from an Oracle table, Geoserver retrieves data with queries like

SELECT * FROM (SELECT ID,CODE,TUNNEL_CODE,NUMBER,LINE,TYPE,TECHNICAL_ROOM_ID,EMERGENCY_RECESS_ID,NAME_FR,NAME_NL,NOTES,EMERGENCY_EXIT_ID,TUNNEL_SECTION_ID,VIEWING_CAMERA,GEOMETRY as GEOMETRY FROM EMERGENCY_PHONE) WHERE ROWNUM <= 50

In my case, as NUMBER is a reserved word (even if it is actually the column name), it leads to an oracle error ‘missing expression’.

As geoserver oracle plugin cannot prevent such things, it should protect every table or column name by double quotes.

I think that the fix would be simple.

Thanks

Vincent

2

On Fri, Jan 25, 2013 at 3:35 PM, Raman Vincent <Vincent.Raman@anonymised.com77…> wrote:

Hello,

I use geoserver 2.2.4 with the oracle plugin and Oracle 11g. If I make a layer from an Oracle table, Geoserver retrieves data with queries like

SELECT * FROM (SELECT ID,CODE,TUNNEL_CODE,NUMBER,LINE,TYPE,TECHNICAL_ROOM_ID,EMERGENCY_RECESS_ID,NAME_FR,NAME_NL,NOTES,EMERGENCY_EXIT_ID,TUNNEL_SECTION_ID,VIEWING_CAMERA,GEOMETRY as GEOMETRY FROM EMERGENCY_PHONE) WHERE ROWNUM <= 50

In my case, as NUMBER is a reserved word (even if it is actually the column name), it leads to an oracle error ‘missing expression’.

As geoserver oracle plugin cannot prevent such things, it should protect every table or column name by double quotes.

I think that the fix would be simple.

Yep, it sounds simple. But also low priority, the Oracle plugin has been in use for years with this limitation,
and this is the first time we see such a report.
For starters, can you open a bug report on jira.codehaus.org

If you can do a fix with a test, that would speed up a lot the inclusion in the official code base.
The Oracle store is among the GeoTools sources:
https://github.com/geotools/geotools/tree/master/modules/plugin/jdbc/jdbc-oracle

Cheers
Andrea

–

==
Our support, Your Success! Visit http://opensdi.geo-solutions.it for more information.

Ing. Andrea Aime
@geowolf
Technical Lead

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549

http://www.geo-solutions.it
http://twitter.com/geosolutions_it