Query works in isql, not through java

#11

Even before I saw this post, I decided to try reverting to a previous
java version, and it fixed the problem. I had been running under JRE
1.6.0_14, so I tried the latest java 5 version (_20), because my
previous machine was using 5, and now it's working.

I'm going to dig a bit more to see if there's a workaround for this, or
if it's a known issue.

If anybody at Sybase wants to play with this, I'd love the help, but I
guess it's not really necessary since I have a functioning system again.

Thanks!
D

In article <4b1e821d@forums-1-dub>, paulley (AT) ianywhere (DOT) com says...

Quote:

Neither the iAnywhere bridge nor the server itself uses this kind of SQL
injection because (obviously) the result is typically a
syntactically-invalid statement. Moreover, since 9.x is mature software
we would know about this type of problem if it was an issue with the
JDBC bridge. My guess is that something in a layer above the bridge (but
not in your own code) is injecting the COUNT() aggregate function.

A hypothesis is that there is a Java method that you are calling
inadvertently that is attempting to determine the size of the result set
(in rows) before actually executing the statement. Replacing the
original SELECT list with *only* COUNT(*) will accomplish this for query
specifications, even those containing GROUP BY (but not DISTINCT, nor
will it work for query expressions - UNION, EXCEPT, INTERSECT). A bug in
this code that failed to eliminate the SELECT list in all cases would
match your symptoms. In my experience, SQL parsing by Java toolkits is
done only via rudimentary string-matching, and not actual parsing, and
hence is prone to these types of bugs.

What I have seen in Java toolkits (with Eclipse) is the overloading of
JDBC API calls by utility JARs, including basic calls such as
ExecuteQuery() or CreateQuery(). I wonder if you have JARs in your
Eclipse environment that do this kind of thing.

Glenn

David Kerber wrote:
In article <4b1d96ea$1@forums-1-dub>, "Nick Elson [Sybase iAnywhere]"
@nick@dot@elson@at@sybase@dot@com@> says...
I don't think this is coming from our client libraries or jars either.

I'd look to the Eclipse environment and (alternatively) possibly
another branch in your code.

I know it's not coming from my code, because I've checked the sql being
submitted to the query execute method, and it doesn't have the count()
in it. In addition, a full text search of my code turns up no instances
of COUNT in any of my queries at all.

Just for grins, I added a DISTINCT to the beginning of my sql, and the -
z log on the server then kicked it out with:

** EXTRA conn: 1 SELECT COUNT(*), DISTINCT S.GROSSSALES,
S.NETSALES, S.SALESTEMPERATURE, S.DATEGENERATED, S.SALESTYPEKEY,
S.SALESKEY, S.OBSERVATIONTIME, S.FUELSYSKEY, S.DATASUMMKEY, S.SHIFT,
GETALLBLENDEDSALES( S.DATASUMMKEY, D.DATEGENERATED, S.DATEGENERATED,
S.SALESTYPEKEY, 0 ) AS BLENDSALES, GETALLBLENDEDSALES( S.DATASUMMKEY,
D.DATEGENERATED, S.DATEGENERATED, S.SALESTYPEKEY, 1 ) AS NETBLENDSALES
FROM SALES S JOIN DATASUMM D ON D.DATASUMMKEY = S.DATASUMMKEY WHERE
S.DATASUMMKEY IN ( 623921, 624123, 624337, 624559, 624855, 625026,

#12

David,

If reverting to JRE 1.5 resolves the issue, then I have to wonder if you
were in fact using the wrong JDBC driver. When moving to JRE 1.6, one of
things that happens is that the Sun JDBC-ODBC bridge automatically
registers itself (ala JDBC 4.0). So, if the URL header you are using to
connect begins with "jdbc

dbc" and if you are relying on the
DriverManager to pick the correct JDBC driver (i.e.
DriverManager.getConnection( url, ... ) ), then there is a very good
chance you are getting the Sun JDBC-ODBC driver instead of the iAnywhere
JDBC driver. So, have a look at your URL header and verify that it
begins with "jdbc:ianywhere" and not "jdbc

dbc". If the URL header is
"jdbc

dbc", then change it to "jdbc:ianywhere", put JRE 1.6 back and
see if the problem goes away.

Karim
David Kerber wrote:

Quote:

Even before I saw this post, I decided to try reverting to a previous
java version, and it fixed the problem. I had been running under JRE
1.6.0_14, so I tried the latest java 5 version (_20), because my
previous machine was using 5, and now it's working.

I'm going to dig a bit more to see if there's a workaround for this, or
if it's a known issue.

If anybody at Sybase wants to play with this, I'd love the help, but I
guess it's not really necessary since I have a functioning system again.

Thanks!
D

In article <4b1e821d@forums-1-dub>, paulley (AT) ianywhere (DOT) com says...
Neither the iAnywhere bridge nor the server itself uses this kind of SQL
injection because (obviously) the result is typically a
syntactically-invalid statement. Moreover, since 9.x is mature software
we would know about this type of problem if it was an issue with the
JDBC bridge. My guess is that something in a layer above the bridge (but
not in your own code) is injecting the COUNT() aggregate function.

A hypothesis is that there is a Java method that you are calling
inadvertently that is attempting to determine the size of the result set
(in rows) before actually executing the statement. Replacing the
original SELECT list with *only* COUNT(*) will accomplish this for query
specifications, even those containing GROUP BY (but not DISTINCT, nor
will it work for query expressions - UNION, EXCEPT, INTERSECT). A bug in
this code that failed to eliminate the SELECT list in all cases would
match your symptoms. In my experience, SQL parsing by Java toolkits is
done only via rudimentary string-matching, and not actual parsing, and
hence is prone to these types of bugs.

What I have seen in Java toolkits (with Eclipse) is the overloading of
JDBC API calls by utility JARs, including basic calls such as
ExecuteQuery() or CreateQuery(). I wonder if you have JARs in your
Eclipse environment that do this kind of thing.

Glenn

David Kerber wrote:
In article <4b1d96ea$1@forums-1-dub>, "Nick Elson [Sybase iAnywhere]"
@nick@dot@elson@at@sybase@dot@com@> says...
I don't think this is coming from our client libraries or jars either.

I'd look to the Eclipse environment and (alternatively) possibly
another branch in your code.
I know it's not coming from my code, because I've checked the sql being
submitted to the query execute method, and it doesn't have the count()
in it. In addition, a full text search of my code turns up no instances
of COUNT in any of my queries at all.

Just for grins, I added a DISTINCT to the beginning of my sql, and the -
z log on the server then kicked it out with:

** EXTRA conn: 1 SELECT COUNT(*), DISTINCT S.GROSSSALES,
S.NETSALES, S.SALESTEMPERATURE, S.DATEGENERATED, S.SALESTYPEKEY,
S.SALESKEY, S.OBSERVATIONTIME, S.FUELSYSKEY, S.DATASUMMKEY, S.SHIFT,
GETALLBLENDEDSALES( S.DATASUMMKEY, D.DATEGENERATED, S.DATEGENERATED,
S.SALESTYPEKEY, 0 ) AS BLENDSALES, GETALLBLENDEDSALES( S.DATASUMMKEY,
D.DATEGENERATED, S.DATEGENERATED, S.SALESTYPEKEY, 1 ) AS NETBLENDSALES
FROM SALES S JOIN DATASUMM D ON D.DATASUMMKEY = S.DATASUMMKEY WHERE
S.DATASUMMKEY IN ( 623921, 624123, 624337, 624559, 624855, 625026,

#13

In article <4b1ea228$1@forums-1-dub>, kkhamis (AT) sybase (DOT) com says...

Quote:

David,

If reverting to JRE 1.5 resolves the issue, then I have to wonder if you
were in fact using the wrong JDBC driver. When moving to JRE 1.6, one of
things that happens is that the Sun JDBC-ODBC bridge automatically
registers itself (ala JDBC 4.0). So, if the URL header you are using to
connect begins with "jdbc

dbc" and if you are relying on the
DriverManager to pick the correct JDBC driver (i.e.
DriverManager.getConnection( url, ... ) ), then there is a very good
chance you are getting the Sun JDBC-ODBC driver instead of the iAnywhere
JDBC driver. So, have a look at your URL header and verify that it
begins with "jdbc:ianywhere" and not "jdbc

dbc". If the URL header is
"jdbc

dbc", then change it to "jdbc:ianywhere", put JRE 1.6 back and
see if the problem goes away.

I just finished testing while trying to build a simple repro to send to
the Sun NGs, and that is exactly what the problem was. Changing to
jdbc:ianywhere fixed it.

Thanks!!!!!!!
D

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Query works in isql, not through java

sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general

Re: Query works in isql, not through java - SOLVED - 12-08-2009 , 10:56 AM

Re: Query works in isql, not through java - SOLVED - 12-08-2009 , 12:59 PM

Re: Query works in isql, not through java - SOLVED - 12-08-2009 , 01:24 PM