Bug #6743 updateRow() generate illegal escape sequence.
Submitted: 21 Nov 2004 23:26 Modified: 22 Nov 2004 15:51
Reporter: Toshifumi Mikuni Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.0.14 and 3.0.16 also OS:Any (Any)
Assigned to: Mark Matthews CPU Architecture:Any

[21 Nov 2004 23:26] Toshifumi Mikuni
Description:
UpdatableResultSet syncUpdate() Illegal escape will generate when string field have multi-byte character include 0x5C code.
 For example,  server charset use sjis and sjis character (Katakana - So : 0x83 0x5C ) escape as '{0x83}{0x5C}{0x5C}'. and this will make SQL syntax error.

How to repeat:
varchar field has sjis japanese katakana-so ( 0x83 0x5C )
and call updateRow(), that make Syntax error.

Suggested fix:
Please consider field data type in syncUpdate() method.

diff UpdatableResultSet.java UpdatableResultSet.java.0
27c27
< import java.sql.Types;
---
>
1922,1937d1921
<       private void setData( PreparedStatement updater, int typ, int idx, byte
[] data, String characterEncoding ) throws java.io.UnsupportedEncodingException,
 SQLException {
<               switch ( typ ) {
<                 case Types.CHAR:
<                 case Types.VARCHAR:
<                 case Types.LONGVARCHAR:
<           case Types.CLOB:
<             String currentVal = ((characterEncoding == null)
<                  ? new String(data)
<                  : new String(data, characterEncoding));
<               updater.setString(idx, currentVal);
<             break;
<           default:
<               updater.setBytes(idx, data);
<                       break;
<               }
<       }
1960,1964d1943
<
<         String characterEncoding = null;
<         if (connection.useUnicode()) {
<             characterEncoding = connection.getEncoding();
<         }
1966,1967c1945
<         try {
<           for (int i = 0; i < numFields; i++) {
---
>         for (int i = 0; i < numFields; i++) {
1969c1947
<               setData( updater,  fields[i].getSQLType(), i + 1, thisRow[i], ch
aracterEncoding );
---
>                 updater.setBytes(i + 1, thisRow[i]);
1973c1951
<           }
---
>         }
1975c1953
<           int numKeys = primaryKeyIndicies.size();
---
>         int numKeys = primaryKeyIndicies.size();
1977c1955
<           if (numKeys == 1) {
---
>         if (numKeys == 1) {
1980,1981c1958,1959
<             setData( updater, index, numFields + 1, keyData, characterEncoding
 );
<           } else {
---
>             updater.setBytes(numFields + 1, keyData);
>         } else {
1983,1984c1961,1962
<               int kid = ((Integer) primaryKeyIndicies.get(i)).intValue();
<                 byte[] currentVal = thisRow[kid];
---
>                 byte[] currentVal = thisRow[((Integer) primaryKeyIndicies.get(
i))
>                     .intValue()];
1987c1965
<                           setData( updater,  fields[kid].getSQLType(), numFiel
ds + i + 1, currentVal, characterEncoding );
---
>                     updater.setBytes(numFields + i + 1, currentVal);
1992,1995d1969
<           }
<         } catch (java.io.UnsupportedEncodingException encodingEx) {
<             throw new SQLException("Unsupported character encoding '"
<                 + connection.getEncoding() + "'");
[22 Nov 2004 15:51] Mark Matthews
It seems if the connection is correctly configured to use 'sjis' as the character encoding, this bug isn't there (i.e. this testcase works):

    public void testBug6743() throws Exception {
    	// 0x835C	U+30BD	# KATAKANA LETTER SO
    	String katakanaStr = "\u30BD";
    	
    	Properties props = new Properties();
    	
    	props.setProperty("useUnicode", "true");
    	props.setProperty("characterEncoding", "SJIS");
    	
    	Connection sjisConn = null; 
    	Statement sjisStmt = null;
    	
    	try {
    		sjisConn = getConnectionWithProps(props);
    		sjisStmt = sjisConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
    		
    		sjisStmt.executeUpdate("DROP TABLE IF EXISTS testBug6743");
    		StringBuffer queryBuf = new StringBuffer("CREATE TABLE testBug6743 (pkField INT NOT NULL PRIMARY KEY, field1 VARCHAR(32)");
    		
    		if (versionMeetsMinimum(4, 1)) {
    			queryBuf.append(" CHARACTER SET SJIS");
    		}
    		
    		queryBuf.append(")");
    		sjisStmt.executeUpdate(queryBuf.toString());
    		sjisStmt.executeUpdate("INSERT INTO testBug6743 VALUES (1, 'abc')");
    		
    		this.rs = sjisStmt.executeQuery("SELECT pkField, field1 FROM testBug6743");
    		this.rs.next();
    		this.rs.updateString(2, katakanaStr);
    		this.rs.updateRow();
    		
    		String retrString = this.rs.getString(2);
    		assertTrue(katakanaStr.equals(retrString));
    		
    		this.rs = sjisStmt.executeQuery("SELECT pkField, field1 FROM testBug6743");
    		this.rs.next();
    		
    		retrString = this.rs.getString(2);
    		assertTrue(katakanaStr.equals(retrString));
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug6743");
    		
    		if (sjisStmt != null) {
    			sjisStmt.close();
    		}
    		
    		if (sjisConn != null) {
    			sjisConn.close();
    		}
    	}
    }

Are you configuring your server and/or connection to actually use 'sjis'?
[23 Nov 2004 6:43] Toshifumi Mikuni
Yes, I use sjis encoding.

Below code makes syntax error.
I think this problem happen when 0x5C character in selected but not update field.
this field use byte copy, but byte sequence \\ escape were not correct for sjis character.

this.rs = sjisStmt.executeQuery("SELECT pkField, field1 FROMtestBug6743");
this.rs.next();
this.rs.updateString(2, katakanaStr);
this.rs.updateRow();

this.rs = sjisStmt.executeQuery("SELECT pkField, field1 FROMtestBug6743");
this.rs.next();
this.rs.updateString(1, 1);  // <= only 1 update, 2 were selected but not update
this.rs.updateRow();          // <= Syntax error exception
[27 Nov 2004 4:03] Toshifumi Mikuni
I can repeat this problem.

I think this problem happen when 0x5C character in selected but not update
field.
These field wil use byte copy, but byte sequence \\ escape were not correct for sjis character.

Please try, selected but not update field contains 0x5C code.

Ex)
field2 has '{0x83}{0x5C}' -- Katakana-So

rs = ps.executeQuery("select field1, field2 from testBug6743" ); 
rs.next();
rs.updateString( "field1", rs.getString("field1") );
rs.updateRow();