Обсуждение: Upgrading driver from 7.4 to 8.1

Hello!

Due to the recent security bugfix for PostgreSQL 8.1, I wanted to
upgrade my drivers according to the recommendations here
http://www.postgresql.org/docs/techdocs.49

Now we're currently using pg74.216.jdbc3.jar, even though we've long ago
upgraded our databases to version 8.1 (running 8.1.4 from today on). Our
applications are using ColdFusion MX 6.1 and the datasources are
configured using the official PostgreSQL JDBC driver. I just tried to
replace the old 7.4 driver with the latest 8.1 version
postgresql-8.1-407.jdbc3.jar. The datasource verifies okay, so the
connection actually works.

Some queries fail, however, as in this example:

select GROUP_ID, GROUP_NAME, [...] from MY_RESULT where RULE_READ <=
(param 1)

I suspect that this happens everytime we try to send out-of-line
parameters using ColdFusions cfqueryparam syntax in conjunction with
Query-of-Queries (i.e. executing a query on a resultset of a previous
query) like this:

<cfquery name="thisQuery" dbtype="QUERY">
select          GROUP_ID,
                        GROUP_NAME,
                        [...]
from            MY_RESULT
where           RULE_READ <= <cfqueryparam cfsqltype="cf_sql_integer"
value="#thisValue#">
</cfquery>

As far as I know, ColdFusions cfqueryparam not only deals with the
binding of parameters and checking the validity of the values, it also
uses SQLPrepare/SQLExecute for execution.

When I switch back to the 7.4 driver version, everything is fine again.
Now I don't really understand what's going on, as ColdFusion doesn't
really give me so much as an error _message_, just that an error has
ocurred in this query, even with debugging turned on. Now I do get a
java.sql.SQLException with a wonderfully useless stack trace, too (see
below), but as I am not an Adobe developer I cannot really make much of
this information. So I wonder if you'd have some suggestion as to the
differences between the two driver versions that could possibly lead to
symptoms such as this?

Here's the (probably useless) stacktrace (abbreviated), the
init.inc-script contains the aforementioned query:

java.sql.SQLException
    at coldfusion.sql.JdbcImpl.getConnection(JdbcImpl.java:73)
    at coldfusion.sql.JdbcImpl.getConnection(JdbcImpl.java:56)
    at coldfusion.sql.SqlImpl.execute(SqlImpl.java:214)
    at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:447)
    at
cfinit2einc50524585.runPage(/somepath/to/my/scripts/directory/init.inc:4
4)
    [...]

If there's no other way, I'll have to stay with the 7.4 version. Will
the client-side fixes for the 8.1.4 release be backported to the
7.4-line of the JDBC driver?

Kind regards

   Markus

"Markus Wollny" <Markus.Wollny@computec.de> writes:
> Due to the recent security bugfix for PostgreSQL 8.1, I wanted to
> upgrade my drivers according to the recommendations here
> http://www.postgresql.org/docs/techdocs.49

FWIW, I think that Java clients don't need to worry about those security
issues.  It's not possible for Java to emit an invalidly-coded UTF8
string, is it?  I thought that the internal representation of strings
is UCS2 or UCS4, and if so, it would take some exceedingly broken
code in the JVM for the output not to be valid UTF8.

Not that it's not a good idea to upgrade anyway.  Sorry I can't help you
with your ColdFusion problem.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> FWIW, I think that Java clients don't need to worry about those security
> issues.  It's not possible for Java to emit an invalidly-coded UTF8
> string, is it?  I thought that the internal representation of strings
> is UCS2 or UCS4, and if so, it would take some exceedingly broken
> code in the JVM for the output not to be valid UTF8.

It's UTF-16, just like Windows XP and above.

 http://java.sun.com/developer/technicalArticles/Intl/Supplementary/

FYI there are two ways to output UTF8:

DataOutputStream.writeUTF()
 outputs a non-standard UTF8, and is limited in size.

From 1.4:
 Charset.forName("UTF-8")....encode()
  outputs standard UTF8

Hi!

Tom Lane wrote:
> "Markus Wollny" <Markus.Wollny@computec.de> writes:
>> Due to the recent security bugfix for PostgreSQL 8.1, I wanted to
>> upgrade my drivers according to the recommendations here
>> http://www.postgresql.org/docs/techdocs.49
>
> FWIW, I think that Java clients don't need to worry about
> those security issues.  [...]

Thanks for the info, so we weren't really vulnerable after all, as any
public traffic to our databases is via JDBC.

> Not that it's not a good idea to upgrade anyway.  Sorry I
> can't help you with your ColdFusion problem.

Yes, I found out that it seems to be a bug an Adobe's behalf - and I'd
be very much surprised if they actually released a bug fix other than
"cough up the cash for CFMX 7". The bug is fixed in ColdFusion MX 7.0.1
Updater. Ah well, I guess I'll be stuck with the JDBC 7.4 line then for
the time being.

Kind regards

   Markus