(unknown charset) Bug in Oracle JDBC thin driver (reversed parameters order)

From: (unknown charset) Marcin Kasperski <Marcin.Kasperski_at_softax.com.pl>
Date: Fri, 04 Jun 1999 05:51:26 GMT
Message-ID: <3757678B.1AC09CFD_at_softax.com.pl>

[ I'd prefer to send it to some Oracle support email, but after
searching www.oracle.com and www.technet.com for a few minutes I'm unable to find any - they only offer emails to report website problems. ]

The following program illustrates bug I found in JDBC Oracle thin driver.   

• Synopsis:

The parameters of prepared statement (I tested SELECT's and UPDATE's) are bound in the reverse order.
If one do:

     PreparedStatement p = connection.prepareStatement(
       "SELECT field FROM table WHERE first = ? and second = ?");
and then bind parameter 1 to "a" and parameter to "b":
     p.setString(1, "a");
     p.setString(2, "b");
then executing p yields the same results as executing
      SELECT field FROM table WHERE first = "b" and second = "a"
although it should be equivalent to
      SELECT field FROM table WHERE first = "a" and second = "b"
      

The bug is present in "thin" Oracle JDBC driver. Changing driver to "oci8" solves the problem. I have not tested other drivers.

• Version and platform info:

I detected the bug using Oracle 8.0.5 server for Linux. According to $ORACLE_HOME/jdbc/README.doc that is Oracle JDBC Drivers release 8.0.5.0.0 (Production Release)

• The program below:

The program below illustrates the bug by creating dummy two column table, inserting the row into it and then selecting the contents using prepared statement. Those operations are performed on both good (oci8) and bad (thin) connections, the results can be compared.
You may need to change SID, listener port and account data in getConnecton calls.

Sample program output:

$ javac ShowBug.java; java ShowBug
Output for both connections should be the same --------------- thin Driver ---------------
[ Non parametrized query: ]

aaa
[ The same - parametrized (should give one row): ]
[ The same - with buggy reversed order (should give no answers): ]
aaa
--------------- oci8 driver ---------------
[ Non parametrized query: ]

aaa
[ The same - parametrized (should give one row): ]
aaa
[ The same - with buggy reversed order (should give no answers): ]
--------------- The end ---------------

• The program demonstrating an error

// Small program which attempt to show bug in // Oracle JDBC library I use.

import java.sql.*;

class ShowBug
{

    public static void main (String args [])

        throws SQLException
    {

	// Load the Oracle JDBC driver
	DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
	
	System.out.println("Output for both connections should be the same");

	Connection buggyConnection 
   	= DriverManager.getConnection
("jdbc:oracle:thin:_at_localhost:1521:ORACLE",
				       "scott", "tiger");
	process("thin Driver", buggyConnection);

	Connection goodConnection
	= DriverManager.getConnection ("jdbc:oracle:oci8:",
				     "scott", "tiger");
	process("oci8 driver", goodConnection);

	System.out.println("--------------- The end ---------------");

    }

    public static void process(String title, Connection conn)

        throws SQLException
    {

        System.out.println("--------------- " + title + " ---------------");

        Statement stmt = conn.createStatement ();         

	stmt.execute(
	   "CREATE TABLE bug (id VARCHAR(10), val VARCHAR(10))");
	
	stmt.executeUpdate(
	   "INSERT INTO bug VALUES('aaa', 'bbb')");
	
	System.out.println("[ Non parametrized query: ]");
	ResultSet rset = stmt.executeQuery(
	   "select id from bug where id = 'aaa' and val = 'bbb'");
	while (rset.next ())
	    System.out.println (rset.getString (1));

	System.out.println("[ The same - parametrized (should give one row):
]");
	PreparedStatement prep = conn.prepareStatement(
           "select id from bug where id = ? and val = ?");
	prep.setString(1, "aaa");
	prep.setString(2, "bbb");
	rset = prep.executeQuery();
	while (rset.next ())
	    System.out.println (rset.getString (1));

	System.out.println("[ The same - with buggy reversed order (should give
no answers): ]");
	prep = conn.prepareStatement(
           "select id from bug where id = ? and val = ?");
	prep.setString(1, "bbb");
	prep.setString(2, "aaa");
	rset = prep.executeQuery();
	while (rset.next ())
	    System.out.println (rset.getString (1));

	stmt.execute("DROP TABLE bug");

    }
}

• Marcin Kasperski Marcin.Kasperski<at>softax.com.pl
• marckasp<at>friko6.onet.pl
• Moje pogl±dy s± moimi pogl±dami, nikogo poza mn± nie reprezentuj±.
• (My opinions are just my opinions.)

Received on Fri Jun 04 1999 - 07:51:26 CEST