Re: MD5-based passwords

Jeremy,

I think I would recommend moving most of this logic into a new class
under org.postgresql.util, called MD5.java?? (that is where the
UnixCrypt class is located).

I couldn't get what you sent to compile for me, I needed to add some casts:

hex_digest[0] = 'm'; hex_digest[1] = 'd'; hex_digest[2] = '5';

became:

hex_digest[0] = (byte)'m'; hex_digest[1] = (byte)'d'; hex_digest[2] =
(byte)'5';

Otherwise with this above fix it compiled fine under both jdk1.1 and
jdk1.2. I didn't do any testing of the result though.

thanks,
--Barry

PS. When sending diffs please use context diff format (i.e.-c). It
makes it much easier to review.

Jeremy Wohl wrote:

> On Wed, Nov 07, 2001 at 12:27:53AM -0500, Bruce Momjian wrote:
>
>>>Hey folks,
>>>
>>>I don't see MD5-based password code in the JDBC CVS tree. Is anyone
>>>working on this?
>>>
>>>I'll take a stab, if not.
>>>
>>There is no one working on it. ODBC needs it too. It wasn't on the
>>TODO list but I just added it.
>>
>>I can assist with any questions. See libpq for a sample implementation.
>>
>
> OK, how about this? Someone will have to help me with appropriate exception
> behavior and where the bytesToHex util is placed.
>
> I'm not clear on the SendInteger(5 + .. code, seen elsewhere. Why isn't
> this (4 + ...?
>
> Index: Connection.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/Connection.java,v
> retrieving revision 1.34
> diff -r1.34 Connection.java
> 6a7
>
>>import java.security.*;
>>
> 65a67
>
>> private static final int AUTH_REQ_MD5 = 5;
>>
> 183c185
> < // Get the password salt if there is one
> ---
>
>> // Get the crypt password salt if there is one
>>
> 190c192,204
> < DriverManager.println("Salt=" + salt);
> ---
>
>> DriverManager.println("Crypt salt=" + salt);
>> }
>>
>> // Or get the md5 password salt if there is one
>> if (areq == AUTH_REQ_MD5)
>> {
>> byte[] rst = new byte[4];
>> rst[0] = (byte)pg_stream.ReceiveChar();
>> rst[1] = (byte)pg_stream.ReceiveChar();
>> rst[2] = (byte)pg_stream.ReceiveChar();
>> rst[3] = (byte)pg_stream.ReceiveChar();
>> salt = new String(rst, 0, 4);
>> DriverManager.println("MD5 salt=" + salt);
>>
> 197,198c211,212
> < break;
> <
> ---
>
>> break;
>>
>>
> 223a238,266
>
>> case AUTH_REQ_MD5:
>> try {
>> MessageDigest md = MessageDigest.getInstance("MD5");
>> byte[] temp_digest, pass_digest;
>> byte[] hex_digest = new byte[35];
>>
>> DriverManager.println("postgresql: MD5");
>>
>> md.update(PG_PASSWORD.getBytes());
>> md.update(PG_USER.getBytes());
>> temp_digest = md.digest();
>>
>> bytesToHex(temp_digest, hex_digest, 0);
>> md.update(hex_digest, 0, 32);
>> md.update(salt.getBytes());
>> pass_digest = md.digest();
>>
>> bytesToHex(pass_digest, hex_digest, 3);
>> hex_digest[0] = 'm'; hex_digest[1] = 'd'; hex_digest[2] = '5';
>>
>> pg_stream.SendInteger(5 + hex_digest.length, 4);
>> pg_stream.Send(hex_digest);
>> pg_stream.SendInteger(0, 1);
>> pg_stream.flush();
>> } catch (Exception e) {
>> ; // "MessageDigest failure; " + e
>> }
>> break;
>>
>>
> 310a354,368
>
>> private static void bytesToHex(byte[] bytes, byte[] hex, int offset)
>> {
>> final char lookup[] = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
>> 'a', 'b', 'c', 'd', 'e', 'f' };
>>
>> int i, c, j, pos = offset;
>>
>> for (i = 0; i < 16; i++) {
>> c = bytes[i] & 0xFF; j = c >> 4;
>> hex[pos++] = (byte) lookup[j];
>> j = (c & 0xF);
>> hex[pos++] = (byte) lookup[j];
>> }
>> }
>>
>
> -jeremy
> _____________________________________________________________________
> jeremy wohl ..: http://igmus.org
>
>
> ------------------------------------------------------------------------
>
> package org.postgresql;
>
> import java.io.*;
> import java.net.*;
> import java.sql.*;
> import java.util.*;
> import java.security.*;
> import org.postgresql.Field;
> import org.postgresql.fastpath.*;
> import org.postgresql.largeobject.*;
> import org.postgresql.util.*;
> import org.postgresql.core.*;
>
> /**
> * $Id: Connection.java,v 1.34 2001/11/01 01:08:36 barry Exp $
> *
> * This abstract class is used by org.postgresql.Driver to open either the JDBC1 or
> * JDBC2 versions of the Connection class.
> *
> */
> public abstract class Connection
> {
> // This is the network stream associated with this connection
> public PG_Stream pg_stream;
>
> private String PG_HOST;
> private int PG_PORT;
> private String PG_USER;
> private String PG_PASSWORD;
> private String PG_DATABASE;
> private boolean PG_STATUS;
> private String compatible;
>
> /**
> * The encoding to use for this connection.
> */
> private Encoding encoding = Encoding.defaultEncoding();
>
> private String dbVersionNumber;
>
> public boolean CONNECTION_OK = true;
> public boolean CONNECTION_BAD = false;
>
> public boolean autoCommit = true;
> public boolean readOnly = false;
>
> public Driver this_driver;
> private String this_url;
> private String cursor = null; // The positioned update cursor name
>
> // These are new for v6.3, they determine the current protocol versions
> // supported by this version of the driver. They are defined in
> // src/include/libpq/pqcomm.h
> protected static final int PG_PROTOCOL_LATEST_MAJOR = 2;
> protected static final int PG_PROTOCOL_LATEST_MINOR = 0;
> private static final int SM_DATABASE = 64;
> private static final int SM_USER = 32;
> private static final int SM_OPTIONS = 64;
> private static final int SM_UNUSED = 64;
> private static final int SM_TTY = 64;
>
> private static final int AUTH_REQ_OK = 0;
> private static final int AUTH_REQ_KRB4 = 1;
> private static final int AUTH_REQ_KRB5 = 2;
> private static final int AUTH_REQ_PASSWORD = 3;
> private static final int AUTH_REQ_CRYPT = 4;
> private static final int AUTH_REQ_MD5 = 5;
>
> // New for 6.3, salt value for crypt authorisation
> private String salt;
>
> // These are used to cache oids, PGTypes and SQLTypes
> private static Hashtable sqlTypeCache = new Hashtable(); // oid -> SQLType
> private static Hashtable pgTypeCache = new Hashtable(); // oid -> PGType
> private static Hashtable typeOidCache = new Hashtable(); //PGType -> oid
>
> // Now handle notices as warnings, so things like "show" now work
> public SQLWarning firstWarning = null;
>
> /**
> * Cache of the current isolation level
> */
> private int isolationLevel = java.sql.Connection.TRANSACTION_READ_COMMITTED;
>
> // The PID an cancellation key we get from the backend process
> public int pid;
> public int ckey;
>
> /**
> * This is called by Class.forName() from within org.postgresql.Driver
> */
> public Connection()
> {}
>
> /**
> * This method actually opens the connection. It is called by Driver.
> *
> * @param host the hostname of the database back end
> * @param port the port number of the postmaster process
> * @param info a Properties[] thing of the user and password
> * @param database the database to connect to
> * @param u the URL of the connection
> * @param d the Driver instantation of the connection
> * @return a valid connection profile
> * @exception SQLException if a database access error occurs
> */
> protected void openConnection(String host, int port, Properties info, String database, String url, Driver d) throws SQLException
> {
> // Throw an exception if the user or password properties are missing
> // This occasionally occurs when the client uses the properties version
> // of getConnection(), and is a common question on the email lists
> if (info.getProperty("user") == null)
> throw new PSQLException("postgresql.con.user");
>
> this_driver = d;
> this_url = url;
> PG_DATABASE = database;
> PG_USER = info.getProperty("user");
> PG_PASSWORD = info.getProperty("password","");
> PG_PORT = port;
> PG_HOST = host;
> PG_STATUS = CONNECTION_BAD;
> if (info.getProperty("compatible") == null)
> {
> compatible = d.getMajorVersion() + "." + d.getMinorVersion();
> }
> else
> {
> compatible = info.getProperty("compatible");
> }
>
> // Now make the initial connection
> try
> {
> pg_stream = new PG_Stream(host, port);
> }
> catch (ConnectException cex)
> {
> // Added by Peter Mount <peter(at)retep(dot)org(dot)uk>
> // ConnectException is thrown when the connection cannot be made.
> // we trap this an return a more meaningful message for the end user
> throw new PSQLException ("postgresql.con.refused");
> }
> catch (IOException e)
> {
> throw new PSQLException ("postgresql.con.failed", e);
> }
>
> // Now we need to construct and send a startup packet
> try
> {
> // Ver 6.3 code
> pg_stream.SendInteger(4 + 4 + SM_DATABASE + SM_USER + SM_OPTIONS + SM_UNUSED + SM_TTY, 4);
> pg_stream.SendInteger(PG_PROTOCOL_LATEST_MAJOR, 2);
> pg_stream.SendInteger(PG_PROTOCOL_LATEST_MINOR, 2);
> pg_stream.Send(database.getBytes(), SM_DATABASE);
>
> // This last send includes the unused fields
> pg_stream.Send(PG_USER.getBytes(), SM_USER + SM_OPTIONS + SM_UNUSED + SM_TTY);
>
> // now flush the startup packets to the backend
> pg_stream.flush();
>
> // Now get the response from the backend, either an error message
> // or an authentication request
> int areq = -1; // must have a value here
> do
> {
> int beresp = pg_stream.ReceiveChar();
> switch (beresp)
> {
> case 'E':
> // An error occured, so pass the error message to the
> // user.
> //
> // The most common one to be thrown here is:
> // "User authentication failed"
> //
> throw new SQLException(pg_stream.ReceiveString(encoding));
>
> case 'R':
> // Get the type of request
> areq = pg_stream.ReceiveIntegerR(4);
>
> // Get the crypt password salt if there is one
> if (areq == AUTH_REQ_CRYPT)
> {
> byte[] rst = new byte[2];
> rst[0] = (byte)pg_stream.ReceiveChar();
> rst[1] = (byte)pg_stream.ReceiveChar();
> salt = new String(rst, 0, 2);
> DriverManager.println("Crypt salt=" + salt);
> }
>
> // Or get the md5 password salt if there is one
> if (areq == AUTH_REQ_MD5)
> {
> byte[] rst = new byte[4];
> rst[0] = (byte)pg_stream.ReceiveChar();
> rst[1] = (byte)pg_stream.ReceiveChar();
> rst[2] = (byte)pg_stream.ReceiveChar();
> rst[3] = (byte)pg_stream.ReceiveChar();
> salt = new String(rst, 0, 4);
> DriverManager.println("MD5 salt=" + salt);
> }
>
> // now send the auth packet
> switch (areq)
> {
> case AUTH_REQ_OK:
> break;
>
> case AUTH_REQ_KRB4:
> DriverManager.println("postgresql: KRB4");
> throw new PSQLException("postgresql.con.kerb4");
>
> case AUTH_REQ_KRB5:
> DriverManager.println("postgresql: KRB5");
> throw new PSQLException("postgresql.con.kerb5");
>
> case AUTH_REQ_PASSWORD:
> DriverManager.println("postgresql: PASSWORD");
> pg_stream.SendInteger(5 + PG_PASSWORD.length(), 4);
> pg_stream.Send(PG_PASSWORD.getBytes());
> pg_stream.SendInteger(0, 1);
> pg_stream.flush();
> break;
>
> case AUTH_REQ_CRYPT:
> DriverManager.println("postgresql: CRYPT");
> String crypted = UnixCrypt.crypt(salt, PG_PASSWORD);
> pg_stream.SendInteger(5 + crypted.length(), 4);
> pg_stream.Send(crypted.getBytes());
> pg_stream.SendInteger(0, 1);
> pg_stream.flush();
> break;
>
> case AUTH_REQ_MD5:
> try {
> MessageDigest md = MessageDigest.getInstance("MD5");
> byte[] temp_digest, pass_digest;
> byte[] hex_digest = new byte[35];
>
> DriverManager.println("postgresql: MD5");
>
> md.update(PG_PASSWORD.getBytes());
> md.update(PG_USER.getBytes());
> temp_digest = md.digest();
>
> bytesToHex(temp_digest, hex_digest, 0);
> md.update(hex_digest, 0, 32);
> md.update(salt.getBytes());
> pass_digest = md.digest();
>
> bytesToHex(pass_digest, hex_digest, 3);
> hex_digest[0] = 'm'; hex_digest[1] = 'd'; hex_digest[2] = '5';
>
> pg_stream.SendInteger(5 + hex_digest.length, 4);
> pg_stream.Send(hex_digest);
> pg_stream.SendInteger(0, 1);
> pg_stream.flush();
> } catch (Exception e) {
> ; // "MessageDigest failure; " + e
> }
> break;
>
> default:
> throw new PSQLException("postgresql.con.auth", new Integer(areq));
> }
> break;
>
> default:
> throw new PSQLException("postgresql.con.authfail");
> }
> }
> while (areq != AUTH_REQ_OK);
>
> }
> catch (IOException e)
> {
> throw new PSQLException("postgresql.con.failed", e);
> }
>
>
> // As of protocol version 2.0, we should now receive the cancellation key and the pid
> int beresp = pg_stream.ReceiveChar();
> switch (beresp)
> {
> case 'K':
> pid = pg_stream.ReceiveInteger(4);
> ckey = pg_stream.ReceiveInteger(4);
> break;
> case 'E':
> case 'N':
> throw new SQLException(pg_stream.ReceiveString(encoding));
> default:
> throw new PSQLException("postgresql.con.setup");
> }
>
> // Expect ReadyForQuery packet
> beresp = pg_stream.ReceiveChar();
> switch (beresp)
> {
> case 'Z':
> break;
> case 'E':
> case 'N':
> throw new SQLException(pg_stream.ReceiveString(encoding));
> default:
> throw new PSQLException("postgresql.con.setup");
> }
>
> firstWarning = null;
>
> // "pg_encoding_to_char(1)" will return 'EUC_JP' for a backend compiled with multibyte,
> // otherwise it's hardcoded to 'SQL_ASCII'.
> // If the backend doesn't know about multibyte we can't assume anything about the encoding
> // used, so we denote this with 'UNKNOWN'.
> //Note: begining with 7.2 we should be using pg_client_encoding() which
> //is new in 7.2. However it isn't easy to conditionally call this new
> //function, since we don't yet have the information as to what server
> //version we are talking to. Thus we will continue to call
> //getdatabaseencoding() until we drop support for 7.1 and older versions
> //or until someone comes up with a conditional way to run one or
> //the other function depending on server version that doesn't require
> //two round trips to the server per connection
>
> final String encodingQuery =
> "case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding() end";
>
> // Set datestyle and fetch db encoding in a single call, to avoid making
> // more than one round trip to the backend during connection startup.
>
> java.sql.ResultSet resultSet =
> ExecSQL("set datestyle to 'ISO'; select version(), " + encodingQuery + ";");
>
> if (! resultSet.next())
> {
> throw new PSQLException("postgresql.con.failed", "failed getting backend encoding");
> }
> String version = resultSet.getString(1);
> dbVersionNumber = extractVersionNumber(version);
>
> String dbEncoding = resultSet.getString(2);
> encoding = Encoding.getEncoding(dbEncoding, info.getProperty("charSet"));
>
> // Initialise object handling
> initObjectTypes();
>
> // Mark the connection as ok, and cleanup
> firstWarning = null;
> PG_STATUS = CONNECTION_OK;
> }
>
> private static void bytesToHex(byte[] bytes, byte[] hex, int offset)
> {
> final char lookup[] = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
> 'a', 'b', 'c', 'd', 'e', 'f' };
>
> int i, c, j, pos = offset;
>
> for (i = 0; i < 16; i++) {
> c = bytes[i] & 0xFF; j = c >> 4;
> hex[pos++] = (byte) lookup[j];
> j = (c & 0xF);
> hex[pos++] = (byte) lookup[j];
> }
> }
>
> // These methods used to be in the main Connection implementation. As they
> // are common to all implementations (JDBC1 or 2), they are placed here.
> // This should make it easy to maintain the two specifications.
>
> /**
> * This adds a warning to the warning chain.
> * @param msg message to add
> */
> public void addWarning(String msg)
> {
> DriverManager.println(msg);
>
> // Add the warning to the chain
> if (firstWarning != null)
> firstWarning.setNextWarning(new SQLWarning(msg));
> else
> firstWarning = new SQLWarning(msg);
>
> // Now check for some specific messages
>
> // This is obsolete in 6.5, but I've left it in here so if we need to use this
> // technique again, we'll know where to place it.
> //
> // This is generated by the SQL "show datestyle"
> //if(msg.startsWith("NOTICE:") && msg.indexOf("DateStyle")>0) {
> //// 13 is the length off "DateStyle is "
> //msg = msg.substring(msg.indexOf("DateStyle is ")+13);
> //
> //for(int i=0;i<dateStyles.length;i+=2)
> //if(msg.startsWith(dateStyles[i]))
> //currentDateStyle=i+1; // this is the index of the format
> //}
> }
>
> /**
> * Send a query to the backend. Returns one of the ResultSet
> * objects.
> *
> * <B>Note:</B> there does not seem to be any method currently
> * in existance to return the update count.
> *
> * @param sql the SQL statement to be executed
> * @return a ResultSet holding the results
> * @exception SQLException if a database error occurs
> */
> public java.sql.ResultSet ExecSQL(String sql) throws SQLException
> {
> return ExecSQL(sql, null);
> }
>
> /**
> * Send a query to the backend. Returns one of the ResultSet
> * objects.
> *
> * <B>Note:</B> there does not seem to be any method currently
> * in existance to return the update count.
> *
> * @param sql the SQL statement to be executed
> * @param stat The Statement associated with this query (may be null)
> * @return a ResultSet holding the results
> * @exception SQLException if a database error occurs
> */
> public java.sql.ResultSet ExecSQL(String sql, java.sql.Statement stat) throws SQLException
> {
> return new QueryExecutor(sql, stat, pg_stream, this).execute();
> }
>
> /**
> * In SQL, a result table can be retrieved through a cursor that
> * is named. The current row of a result can be updated or deleted
> * using a positioned update/delete statement that references the
> * cursor name.
> *
> * We support one cursor per connection.
> *
> * setCursorName sets the cursor name.
> *
> * @param cursor the cursor name
> * @exception SQLException if a database access error occurs
> */
> public void setCursorName(String cursor) throws SQLException
> {
> this.cursor = cursor;
> }
>
> /**
> * getCursorName gets the cursor name.
> *
> * @return the current cursor name
> * @exception SQLException if a database access error occurs
> */
> public String getCursorName() throws SQLException
> {
> return cursor;
> }
>
> /**
> * We are required to bring back certain information by
> * the DatabaseMetaData class. These functions do that.
> *
> * Method getURL() brings back the URL (good job we saved it)
> *
> * @return the url
> * @exception SQLException just in case...
> */
> public String getURL() throws SQLException
> {
> return this_url;
> }
>
> /**
> * Method getUserName() brings back the User Name (again, we
> * saved it)
> *
> * @return the user name
> * @exception SQLException just in case...
> */
> public String getUserName() throws SQLException
> {
> return PG_USER;
> }
>
> /**
> * Get the character encoding to use for this connection.
> */
> public Encoding getEncoding() throws SQLException
> {
> return encoding;
> }
>
> /**
> * This returns the Fastpath API for the current connection.
> *
> * <p><b>NOTE:</b> This is not part of JDBC, but allows access to
> * functions on the org.postgresql backend itself.
> *
> * <p>It is primarily used by the LargeObject API
> *
> * <p>The best way to use this is as follows:
> *
> * <p><pre>
> * import org.postgresql.fastpath.*;
> * ...
> * Fastpath fp = ((org.postgresql.Connection)myconn).getFastpathAPI();
> * </pre>
> *
> * <p>where myconn is an open Connection to org.postgresql.
> *
> * @return Fastpath object allowing access to functions on the org.postgresql
> * backend.
> * @exception SQLException by Fastpath when initialising for first time
> */
> public Fastpath getFastpathAPI() throws SQLException
> {
> if (fastpath == null)
> fastpath = new Fastpath(this, pg_stream);
> return fastpath;
> }
>
> // This holds a reference to the Fastpath API if already open
> private Fastpath fastpath = null;
>
> /**
> * This returns the LargeObject API for the current connection.
> *
> * <p><b>NOTE:</b> This is not part of JDBC, but allows access to
> * functions on the org.postgresql backend itself.
> *
> * <p>The best way to use this is as follows:
> *
> * <p><pre>
> * import org.postgresql.largeobject.*;
> * ...
> * LargeObjectManager lo = ((org.postgresql.Connection)myconn).getLargeObjectAPI();
> * </pre>
> *
> * <p>where myconn is an open Connection to org.postgresql.
> *
> * @return LargeObject object that implements the API
> * @exception SQLException by LargeObject when initialising for first time
> */
> public LargeObjectManager getLargeObjectAPI() throws SQLException
> {
> if (largeobject == null)
> largeobject = new LargeObjectManager(this);
> return largeobject;
> }
>
> // This holds a reference to the LargeObject API if already open
> private LargeObjectManager largeobject = null;
>
> /**
> * This method is used internally to return an object based around
> * org.postgresql's more unique data types.
> *
> * <p>It uses an internal Hashtable to get the handling class. If the
> * type is not supported, then an instance of org.postgresql.util.PGobject
> * is returned.
> *
> * You can use the getValue() or setValue() methods to handle the returned
> * object. Custom objects can have their own methods.
> *
> * In 6.4, this is extended to use the org.postgresql.util.Serialize class to
> * allow the Serialization of Java Objects into the database without using
> * Blobs. Refer to that class for details on how this new feature works.
> *
> * @return PGobject for this type, and set to value
> * @exception SQLException if value is not correct for this type
> * @see org.postgresql.util.Serialize
> */
> public Object getObject(String type, String value) throws SQLException
> {
> try
> {
> Object o = objectTypes.get(type);
>
> // If o is null, then the type is unknown, so check to see if type
> // is an actual table name. If it does, see if a Class is known that
> // can handle it
> if (o == null)
> {
> Serialize ser = new Serialize(this, type);
> objectTypes.put(type, ser);
> return ser.fetch(Integer.parseInt(value));
> }
>
> // If o is not null, and it is a String, then its a class name that
> // extends PGobject.
> //
> // This is used to implement the org.postgresql unique types (like lseg,
> // point, etc).
> if (o instanceof String)
> {
> // 6.3 style extending PG_Object
> PGobject obj = null;
> obj = (PGobject)(Class.forName((String)o).newInstance());
> obj.setType(type);
> obj.setValue(value);
> return (Object)obj;
> }
> else
> {
> // If it's an object, it should be an instance of our Serialize class
> // If so, then call it's fetch method.
> if (o instanceof Serialize)
> return ((Serialize)o).fetch(Integer.parseInt(value));
> }
> }
> catch (SQLException sx)
> {
> // rethrow the exception. Done because we capture any others next
> sx.fillInStackTrace();
> throw sx;
> }
> catch (Exception ex)
> {
> throw new PSQLException("postgresql.con.creobj", type, ex);
> }
>
> // should never be reached
> return null;
> }
>
> /**
> * This stores an object into the database.
> * @param o Object to store
> * @return OID of the new rectord
> * @exception SQLException if value is not correct for this type
> * @see org.postgresql.util.Serialize
> */
> public int putObject(Object o) throws SQLException
> {
> try
> {
> String type = o.getClass().getName();
> Object x = objectTypes.get(type);
>
> // If x is null, then the type is unknown, so check to see if type
> // is an actual table name. If it does, see if a Class is known that
> // can handle it
> if (x == null)
> {
> Serialize ser = new Serialize(this, type);
> objectTypes.put(type, ser);
> return ser.store(o);
> }
>
> // If it's an object, it should be an instance of our Serialize class
> // If so, then call it's fetch method.
> if (x instanceof Serialize)
> return ((Serialize)x).store(o);
>
> // Thow an exception because the type is unknown
> throw new PSQLException("postgresql.con.strobj");
>
> }
> catch (SQLException sx)
> {
> // rethrow the exception. Done because we capture any others next
> sx.fillInStackTrace();
> throw sx;
> }
> catch (Exception ex)
> {
> throw new PSQLException("postgresql.con.strobjex", ex);
> }
> }
>
> /**
> * This allows client code to add a handler for one of org.postgresql's
> * more unique data types.
> *
> * <p><b>NOTE:</b> This is not part of JDBC, but an extension.
> *
> * <p>The best way to use this is as follows:
> *
> * <p><pre>
> * ...
> * ((org.postgresql.Connection)myconn).addDataType("mytype","my.class.name");
> * ...
> * </pre>
> *
> * <p>where myconn is an open Connection to org.postgresql.
> *
> * <p>The handling class must extend org.postgresql.util.PGobject
> *
> * @see org.postgresql.util.PGobject
> */
> public void addDataType(String type, String name)
> {
> objectTypes.put(type, name);
> }
>
> // This holds the available types
> private Hashtable objectTypes = new Hashtable();
>
> // This array contains the types that are supported as standard.
> //
> // The first entry is the types name on the database, the second
> // the full class name of the handling class.
> //
> private static final String defaultObjectTypes[][] = {
> {"box", "org.postgresql.geometric.PGbox"},
> {"circle", "org.postgresql.geometric.PGcircle"},
> {"line", "org.postgresql.geometric.PGline"},
> {"lseg", "org.postgresql.geometric.PGlseg"},
> {"path", "org.postgresql.geometric.PGpath"},
> {"point", "org.postgresql.geometric.PGpoint"},
> {"polygon", "org.postgresql.geometric.PGpolygon"},
> {"money", "org.postgresql.util.PGmoney"}
> };
>
> // This initialises the objectTypes hashtable
> private void initObjectTypes()
> {
> for (int i = 0;i < defaultObjectTypes.length;i++)
> objectTypes.put(defaultObjectTypes[i][0], defaultObjectTypes[i][1]);
> }
>
> // These are required by other common classes
> public abstract java.sql.Statement createStatement() throws SQLException;
>
> /**
> * This returns a resultset. It must be overridden, so that the correct
> * version (from jdbc1 or jdbc2) are returned.
> */
> public abstract java.sql.ResultSet getResultSet(org.postgresql.Connection conn, java.sql.Statement stat, Field[] fields, Vector tuples, String status, int updateCount, int insertOID, boolean binaryCursor) throws SQLException;
>
> /**
> * In some cases, it is desirable to immediately release a Connection's
> * database and JDBC resources instead of waiting for them to be
> * automatically released (cant think why off the top of my head)
> *
> * <B>Note:</B> A Connection is automatically closed when it is
> * garbage collected. Certain fatal errors also result in a closed
> * connection.
> *
> * @exception SQLException if a database access error occurs
> */
> public void close() throws SQLException
> {
> if (pg_stream != null)
> {
> try
> {
> pg_stream.SendChar('X');
> pg_stream.flush();
> pg_stream.close();
> }
> catch (IOException e)
> {}
> pg_stream = null;
> }
> }
>
> /**
> * A driver may convert the JDBC sql grammar into its system's
> * native SQL grammar prior to sending it; nativeSQL returns the
> * native form of the statement that the driver would have sent.
> *
> * @param sql a SQL statement that may contain one or more '?'
> * parameter placeholders
> * @return the native form of this statement
> * @exception SQLException if a database access error occurs
> */
> public String nativeSQL(String sql) throws SQLException
> {
> return sql;
> }
>
> /**
> * The first warning reported by calls on this Connection is
> * returned.
> *
> * <B>Note:</B> Sebsequent warnings will be changed to this
> * SQLWarning
> *
> * @return the first SQLWarning or null
> * @exception SQLException if a database access error occurs
> */
> public SQLWarning getWarnings() throws SQLException
> {
> return firstWarning;
> }
>
> /**
> * After this call, getWarnings returns null until a new warning
> * is reported for this connection.
> *
> * @exception SQLException if a database access error occurs
> */
> public void clearWarnings() throws SQLException
> {
> firstWarning = null;
> }
>
>
> /**
> * You can put a connection in read-only mode as a hunt to enable
> * database optimizations
> *
> * <B>Note:</B> setReadOnly cannot be called while in the middle
> * of a transaction
> *
> * @param readOnly - true enables read-only mode; false disables it
> * @exception SQLException if a database access error occurs
> */
> public void setReadOnly(boolean readOnly) throws SQLException
> {
> this.readOnly = readOnly;
> }
>
> /**
> * Tests to see if the connection is in Read Only Mode. Note that
> * we cannot really put the database in read only mode, but we pretend
> * we can by returning the value of the readOnly flag
> *
> * @return true if the connection is read only
> * @exception SQLException if a database access error occurs
> */
> public boolean isReadOnly() throws SQLException
> {
> return readOnly;
> }
>
> /**
> * If a connection is in auto-commit mode, than all its SQL
> * statements will be executed and committed as individual
> * transactions. Otherwise, its SQL statements are grouped
> * into transactions that are terminated by either commit()
> * or rollback(). By default, new connections are in auto-
> * commit mode. The commit occurs when the statement completes
> * or the next execute occurs, whichever comes first. In the
> * case of statements returning a ResultSet, the statement
> * completes when the last row of the ResultSet has been retrieved
> * or the ResultSet has been closed. In advanced cases, a single
> * statement may return multiple results as well as output parameter
> * values. Here the commit occurs when all results and output param
> * values have been retrieved.
> *
> * @param autoCommit - true enables auto-commit; false disables it
> * @exception SQLException if a database access error occurs
> */
> public void setAutoCommit(boolean autoCommit) throws SQLException
> {
> if (this.autoCommit == autoCommit)
> return ;
> if (autoCommit)
> ExecSQL("end");
> else
> {
> if (haveMinimumServerVersion("7.1"))
> {
> ExecSQL("begin;" + getIsolationLevelSQL());
> }
> else
> {
> ExecSQL("begin");
> ExecSQL(getIsolationLevelSQL());
> }
> }
> this.autoCommit = autoCommit;
> }
>
> /**
> * gets the current auto-commit state
> *
> * @return Current state of the auto-commit mode
> * @exception SQLException (why?)
> * @see setAutoCommit
> */
> public boolean getAutoCommit() throws SQLException
> {
> return this.autoCommit;
> }
>
> /**
> * The method commit() makes all changes made since the previous
> * commit/rollback permanent and releases any database locks currently
> * held by the Connection. This method should only be used when
> * auto-commit has been disabled. (If autoCommit == true, then we
> * just return anyhow)
> *
> * @exception SQLException if a database access error occurs
> * @see setAutoCommit
> */
> public void commit() throws SQLException
> {
> if (autoCommit)
> return ;
> if (haveMinimumServerVersion("7.1"))
> {
> ExecSQL("commit;begin;" + getIsolationLevelSQL());
> }
> else
> {
> ExecSQL("commit");
> ExecSQL("begin");
> ExecSQL(getIsolationLevelSQL());
> }
> }
>
> /**
> * The method rollback() drops all changes made since the previous
> * commit/rollback and releases any database locks currently held by
> * the Connection.
> *
> * @exception SQLException if a database access error occurs
> * @see commit
> */
> public void rollback() throws SQLException
> {
> if (autoCommit)
> return ;
> if (haveMinimumServerVersion("7.1"))
> {
> ExecSQL("rollback; begin;" + getIsolationLevelSQL());
> }
> else
> {
> ExecSQL("rollback");
> ExecSQL("begin");
> ExecSQL(getIsolationLevelSQL());
> }
> }
>
> /**
> * Get this Connection's current transaction isolation mode.
> *
> * @return the current TRANSACTION_* mode value
> * @exception SQLException if a database access error occurs
> */
> public int getTransactionIsolation() throws SQLException
> {
> clearWarnings();
> ExecSQL("show xactisolevel");
>
> SQLWarning warning = getWarnings();
> if (warning != null)
> {
> String message = warning.getMessage();
> clearWarnings();
> if (message.indexOf("READ COMMITTED") != -1)
> return java.sql.Connection.TRANSACTION_READ_COMMITTED;
> else if (message.indexOf("READ UNCOMMITTED") != -1)
> return java.sql.Connection.TRANSACTION_READ_UNCOMMITTED;
> else if (message.indexOf("REPEATABLE READ") != -1)
> return java.sql.Connection.TRANSACTION_REPEATABLE_READ;
> else if (message.indexOf("SERIALIZABLE") != -1)
> return java.sql.Connection.TRANSACTION_SERIALIZABLE;
> }
> return java.sql.Connection.TRANSACTION_READ_COMMITTED;
> }
>
> /**
> * You can call this method to try to change the transaction
> * isolation level using one of the TRANSACTION_* values.
> *
> * <B>Note:</B> setTransactionIsolation cannot be called while
> * in the middle of a transaction
> *
> * @param level one of the TRANSACTION_* isolation values with
> * the exception of TRANSACTION_NONE; some databases may
> * not support other values
> * @exception SQLException if a database access error occurs
> * @see java.sql.DatabaseMetaData#supportsTransactionIsolationLevel
> */
> public void setTransactionIsolation(int level) throws SQLException
> {
> //In 7.1 and later versions of the server it is possible using
> //the "set session" command to set this once for all future txns
> //however in 7.0 and prior versions it is necessary to set it in
> //each transaction, thus adding complexity below.
> //When we decide to drop support for servers older than 7.1
> //this can be simplified
> isolationLevel = level;
> String isolationLevelSQL;
>
> if (!haveMinimumServerVersion("7.1"))
> {
> isolationLevelSQL = getIsolationLevelSQL();
> }
> else
> {
> isolationLevelSQL = "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ";
> switch (isolationLevel)
> {
> case java.sql.Connection.TRANSACTION_READ_COMMITTED:
> isolationLevelSQL += "READ COMMITTED";
> break;
> case java.sql.Connection.TRANSACTION_SERIALIZABLE:
> isolationLevelSQL += "SERIALIZABLE";
> break;
> default:
> throw new PSQLException("postgresql.con.isolevel",
> new Integer(isolationLevel));
> }
> }
> ExecSQL(isolationLevelSQL);
> }
>
> /**
> * Helper method used by setTransactionIsolation(), commit(), rollback()
> * and setAutoCommit(). This returns the SQL string needed to
> * set the isolation level for a transaction. In 7.1 and later it
> * is possible to set a default isolation level that applies to all
> * future transactions, this method is only necesary for 7.0 and older
> * servers, and should be removed when support for these older
> * servers are dropped
> */
> protected String getIsolationLevelSQL() throws SQLException
> {
> //7.1 and higher servers have a default specified so
> //no additional SQL is required to set the isolation level
> if (haveMinimumServerVersion("7.1"))
> {
> return "";
> }
> StringBuffer sb = new StringBuffer("SET TRANSACTION ISOLATION LEVEL");
>
> switch (isolationLevel)
> {
> case java.sql.Connection.TRANSACTION_READ_COMMITTED:
> sb.append(" READ COMMITTED");
> break;
>
> case java.sql.Connection.TRANSACTION_SERIALIZABLE:
> sb.append(" SERIALIZABLE");
> break;
>
> default:
> throw new PSQLException("postgresql.con.isolevel", new Integer(isolationLevel));
> }
> return sb.toString();
> }
>
> /**
> * A sub-space of this Connection's database may be selected by
> * setting a catalog name. If the driver does not support catalogs,
> * it will silently ignore this request
> *
> * @exception SQLException if a database access error occurs
> */
> public void setCatalog(String catalog) throws SQLException
> {
> //no-op
> }
>
> /**
> * Return the connections current catalog name, or null if no
> * catalog name is set, or we dont support catalogs.
> *
> * @return the current catalog name or null
> * @exception SQLException if a database access error occurs
> */
> public String getCatalog() throws SQLException
> {
> return PG_DATABASE;
> }
>
> /**
> * Overides finalize(). If called, it closes the connection.
> *
> * This was done at the request of Rachel Greenham
> * <rachel(at)enlarion(dot)demon(dot)co(dot)uk> who hit a problem where multiple
> * clients didn't close the connection, and once a fortnight enough
> * clients were open to kill the org.postgres server.
> */
> public void finalize() throws Throwable
> {
> close();
> }
>
> private static String extractVersionNumber(String fullVersionString)
> {
> StringTokenizer versionParts = new StringTokenizer(fullVersionString);
> versionParts.nextToken(); /* "PostgreSQL" */
> return versionParts.nextToken(); /* "X.Y.Z" */
> }
>
> /**
> * Get server version number
> */
> public String getDBVersionNumber()
> {
> return dbVersionNumber;
> }
>
> public boolean haveMinimumServerVersion(String ver) throws SQLException
> {
> return (getDBVersionNumber().compareTo(ver) >= 0);
> }
>
> /**
> * This method returns true if the compatible level set in the connection
> * (which can be passed into the connection or specified in the URL)
> * is at least the value passed to this method. This is used to toggle
> * between different functionality as it changes across different releases
> * of the jdbc driver code. The values here are versions of the jdbc client
> * and not server versions. For example in 7.1 get/setBytes worked on
> * LargeObject values, in 7.2 these methods were changed to work on bytea
> * values. This change in functionality could be disabled by setting the
> * "compatible" level to be 7.1, in which case the driver will revert to
> * the 7.1 functionality.
> */
> public boolean haveMinimumCompatibleVersion(String ver) throws SQLException
> {
> return (compatible.compareTo(ver) >= 0);
> }
>
>
> /**
> * This returns the java.sql.Types type for a PG type oid
> *
> * @param oid PostgreSQL type oid
> * @return the java.sql.Types type
> * @exception SQLException if a database access error occurs
> */
> public int getSQLType(int oid) throws SQLException
> {
> Integer sqlType = (Integer)typeOidCache.get(new Integer(oid));
>
> // it's not in the cache, so perform a query, and add the result to the cache
> if (sqlType == null)
> {
> ResultSet result = (org.postgresql.ResultSet)ExecSQL("select typname from pg_type where oid = " + oid);
> if (result.getColumnCount() != 1 || result.getTupleCount() != 1)
> throw new PSQLException("postgresql.unexpected");
> result.next();
> String pgType = result.getString(1);
> Integer iOid = new Integer(oid);
> sqlType = new Integer(getSQLType(result.getString(1)));
> sqlTypeCache.put(iOid, sqlType);
> pgTypeCache.put(iOid, pgType);
> result.close();
> }
>
> return sqlType.intValue();
> }
>
> /**
> * This returns the java.sql.Types type for a PG type
> *
> * @param pgTypeName PostgreSQL type name
> * @return the java.sql.Types type
> */
> public abstract int getSQLType(String pgTypeName);
>
> /**
> * This returns the oid for a given PG data type
> * @param typeName PostgreSQL type name
> * @return PostgreSQL oid value for a field of this type
> */
> public int getOID(String typeName) throws SQLException
> {
> int oid = -1;
> if (typeName != null)
> {
> Integer oidValue = (Integer) typeOidCache.get(typeName);
> if (oidValue != null)
> {
> oid = oidValue.intValue();
> }
> else
> {
> // it's not in the cache, so perform a query, and add the result to the cache
> ResultSet result = (org.postgresql.ResultSet)ExecSQL("select oid from pg_type where typname='"
> + typeName + "'");
> if (result.getColumnCount() != 1 || result.getTupleCount() != 1)
> throw new PSQLException("postgresql.unexpected");
> result.next();
> oid = Integer.parseInt(result.getString(1));
> typeOidCache.put(typeName, new Integer(oid));
> result.close();
> }
> }
> return oid;
> }
>
> /**
> * We also need to get the PG type name as returned by the back end.
> *
> * @return the String representation of the type of this field
> * @exception SQLException if a database access error occurs
> */
> public String getPGType(int oid) throws SQLException
> {
> String pgType = (String) pgTypeCache.get(new Integer(oid));
> if (pgType == null)
> {
> getSQLType(oid);
> pgType = (String) pgTypeCache.get(new Integer(oid));
> }
> return pgType;
> }
>
> }
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>