Bug #41730 SQL Injection when using U+00A5

Description:
SQL Injection occurs when

- using Connector/J
- client-side Prepared Statement
- String has U+00A5
- characterEncoding is not UTF-8

How to repeat:
MySQL Server 5.1.30
Connector/J 5.1.7

- sample data
mysql> select empno, ename from emp;
+-------+--------+
| empno | ename  |
+-------+--------+
|  7369 | smith  |
|  7499 | allen  |
|  7521 | ward   |
|  7566 | jones  |
|  7654 | martin |
|  7698 | blake  |
|  7782 | clark  |
|  7788 | scott  |
|  7839 | king   |
|  7844 | turner |
|  7876 | adams  |
|  7900 | james  |
|  7902 | ford   |
|  7934 | miller |
+-------+--------+
14 rows in set (0.00 sec)

- sample code
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class InjectionTest {
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/scott?characterEncoding=Windows-31J",
					"scott", "tiger");
			PreparedStatement pstmt = conn.prepareStatement("select empno from emp where ename = ?");
			pstmt.setString(1, "\u00a5' or 1 = 1#");

			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getInt(1));
			}

			rs.close();
			pstmt.close();
			conn.close();

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

- results
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

Suggested fix:
escape U+00A5 suitably.

After post, I learned that escaping U+00A5 thoughtlessly has side effects.

Now suggested fix I think is:
validate characters *after* converting them to 'characterEncoding'.

I wrote a prototype patch.

PreparedStatement#setString()
......
  for (int i = 0; i < stringLength; ++i) {
    char c = x.charAt(i);

    switch (c) {
    case 0: /* Must be escaped for 'mysql' */
      buf.append('\\');
      buf.append('0');
......
    default:
    //add the following
      String characterEncoding = this.connection.getEncoding();
      try{
        if(characterEncoding != null &&
          String.valueOf(c).getBytes(characterEncoding)[0] == 0x5c){
          buf.append('\\');
          buf.append('\\');
        }else {
          buf.append(c);
        }
      }catch(UnsupportedEncodingException e){}
      break;
......

or 
    case '\u00a5':
      String characterEncoding = this.connection.getEncoding();
      try{
        if(characterEncoding != null &&
          String.valueOf(c).getBytes(characterEncoding)[0] == 0x5c){
          buf.append('\\');
          buf.append('\\');
        }
      }catch(UnsupportedEncodingException e){}
      break;

We need to care about not only U+00A5, but also U+20A9 and possibly others...

Sadao, Yoshinori, we are having internal discussion about this bug and it's not forgotten.

Thanks.

fix + test

Attachment: bug41730.diff (text/x-diff), 3.98 KiB.

Pushed as rev 792. Will be released in 5.1.8.

An entry has been added to the 5.1.8 changelog:

SQL injection was possible when using a string containing U+00A5 in a client-side prepared statement, and the character set being used was SJIS/Windows-31J.