Bugs item #640991, was opened at 2002-11-20 00:15
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=640991&group_id=22866
Category: JBossCMP
Group: v3.0 Rabbit Hole
Status: Closed
Resolution: Duplicate
Priority: 5
Submitted By: Adam Heath (doogie)
Assigned to: Dain Sundstrom (dsundstrom)
Summary: field names are not quoted
Initial Comment:
Certain databases have certain keywords reserved. To
prevent this, *all* table names, and *all* column names
must be quoted.
To observe this very broken behaviour, do the following:
1. Download ITracker(www.sf.net/projects/itracker,
version 1.4.0)
2. Convert the ear to an unpacked form(unzip, rename,
unpack, etc)
3. Change standardjbosscmp-jdbc.xml to use a type
mapping of PostgreSQL.
4. Disable(or remove)
server/default/deploy/hsqldb-service.xml
5. Add postgresql.jar to server/default/deploy
6. Add postgresql-service.xml to server/default/deploy
7. Add itracker.ear to server/default/deploy
8. bin/run.sh
Postgres will have an error parsing the generated sql,
because this code uses a CMR of 'user', which is a
reserved word. If I cut and paste the generated CREATE
TABLE command into psql, and use "" to quote user, it
will eventually deploy. But then, later on, it'll fail
as it tries to query and set the values.
Since JBoss can have no way of knowing this kind of
information, *all* such non-sql keywords should be quoted.
This bug first discovered on 3.0.1, and I just
downloaded a brand new 3.0.4, to verify it's
existance(which is how I knew the exact steps to
duplicate it).
I'm running postgres 7.2.1. Debian unstable(sid), i386.
----------------------------------------------------------------------
Comment By: Kevin Conner (kevinconner)
Date: 2002-11-20 09:17
Message:
Logged In: YES
user_id=598569
Hiya Dain.
I have already got a patch for the 3.0.2 source that fixes
this. I've been meaning to post it for a while but things
have gotten very hectic here :-), personally and
professionally.
I'm going to have a look at moving my patches to the
3.0.4 source this weekend and will post something at
the beginning of next week.
BTW I had to jump through a lot of hoops to get the
metadata consistent, would you expect this?
Kev
----------------------------------------------------------------------
Comment By: Adam Heath (doogie)
Date: 2002-11-20 07:01
Message:
Logged In: YES
user_id=9692
Well, I still consider this a bug, that should remain open.
'user' is a very common name, both in database
implementations and in user code.
If you can tell me that the only place sql is generated is
in the compiler classes, then I can have a go at adding code
to do this escaping. My question then becomes how best to
export this feature into the configuration space. Should it
be part of standardjbosscmp-jdbc.xml, globally, or inside
each ejb, per field/entity, or a combination of all?
As to making the sql harder to read, why is that a problem?
JBoss is supposed to hide all this database complexity, no
one should ever need to read the sql, *ever*. If everything
works, then all a user/developer needs to be concerned with
is calilng methods on entities. This kind of bug exposes
problems with the underlying database, which is one of the
main driving forces j2ee container systems are supposed to hide.
The note about case sensitvity is a valid point, however.
It's such a shame sql is so shitty in this regard.
----------------------------------------------------------------------
Comment By: Dain Sundstrom (dsundstrom)
Date: 2002-11-20 06:26
Message:
Logged In: YES
user_id=251431
Adam please read the feature list before posting. This has
been a know issue for a long time (see feature request
555315). The reason I didn't code it that way, was I
personally would never use any name that had to be quoted in
a database.
Anyway, it is not true that all databases support quoted
identifiers. We actually need to check the database
metadata first. Also I think we should not use quotes
unless absolutely necessary as it makes the SQL harder to read.
-dain
----------------------------------------------------------------------
Comment By: Jeremy Boynes (jboynes)
Date: 2002-11-20 06:07
Message:
Logged In: YES
user_id=378919
Another consequence of quoting is to make name matching case
sensitive (normally it is case blind). As least that what
SQL-92 says, not all databases do it.
I believe this would lead to confusion as JBoss would be
using case-sensitive quoted identifiers and most other
applications and tools would not.
Quoting some of the time (e.g. only keywords) would be even
more confusing.
A simple workaround might be to quote the identifier in
jbosscmp-jdbc.xml (e.g. <table-name>"user"</table-name> or
<column-name>"user"</column-name> ) as I believe this is
passed directly into the SQL text.
Of course, you could also set a table/column name here that
didn't conflict and save everyone some confusion.
----------------------------------------------------------------------
Comment By: Adam Heath (doogie)
Date: 2002-11-20 01:32
Message:
Logged In: YES
user_id=9692
Also, wouldn't it be easier to always quote, instead of
having to maintain a list of common keywords, and merging
that with each particular jdbc driver's list?
----------------------------------------------------------------------
Comment By: Adam Heath (doogie)
Date: 2002-11-20 01:28
Message:
Logged In: YES
user_id=9692
So, this is a valid bug? I looked briefly at the
JDBCEJBQLCompiler code in 3.0.1, and didn't see anything
that looked like field name escaping going on.
----------------------------------------------------------------------
Comment By: David Jencks (d_jencks)
Date: 2002-11-20 01:17
Message:
Logged In: YES
user_id=60525
It is certainly unnecessary to quote all column names.
Lists of sql-92 keywords exist, and drivers are required to
provide a list of their other keywords in
DatabaseMetaData.getSQLKeywords().
----------------------------------------------------------------------
You can respond by visiting:
https://sourceforge.net/tracker/?func=detail&atid=376685&aid=640991&group_id=22866
-------------------------------------------------------
This sf.net email is sponsored by: To learn the basics of securing
your web site with SSL, click here to get a FREE TRIAL of a Thawte
Server Certificate: http://www.gothawte.com/rd524.html
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development
