Bug #35653 executeQuery() in Statement.java let "TRUNCATE" queries being executed

• Submitted: 28 Mar 2008 15:46
• Modified: 22 Jan 2013 22:56
• Reporter: Vincent VILLEGENTE
• Status: Closed
• Category: Connector / J
• Severity: S2 (Serious)
• Version: Current
• OS: Any
• Assigned to: Alexander Soklakov
• CPU Architecture: Any

[28 Mar 2008 15:46] Vincent VILLEGENTE

Description:
An executeQuery() in statement.java prevent any request that modify the database ("INSERT", "UPDATE", "DELETE", "DROP", "CREATE", "ALTER"), but a TRUNCATE operation is not filter.

How to repeat:
Connection locConnection = getMySqlConnection();
Statement locStatement = locConnection.createStatement();
ResultSet locResult = locPreparedStatement.executeQuery("TRUNCATE `my_table`");

And so the truncate is executed.

Suggested fix:
In: Statement.java:protected void checkForDml(String sql, char firstStatementChar), "StringUtils.startsWithIgnoreCaseAndWs(parSqlQuery, "TRUNCATE")" should be added to the test to prevent these queries to be executed:

protected void checkForDml(String sql, char firstStatementChar)throws SQLException {
		if ((firstStatementChar == 'I') || (firstStatementChar == 'U')
				|| (firstStatementChar == 'D') || (firstStatementChar == 'A')
				|| (firstStatementChar == 'C')
				|| (firstStatementChar == 'T')) {
			if (StringUtils.startsWithIgnoreCaseAndWs(sql, "INSERT") //$NON-NLS-1$
					|| StringUtils.startsWithIgnoreCaseAndWs(sql, "UPDATE") //$NON-NLS-1$
					|| StringUtils.startsWithIgnoreCaseAndWs(sql, "DELETE") //$NON-NLS-1$
					|| StringUtils.startsWithIgnoreCaseAndWs(sql, "DROP") //$NON-NLS-1$
					|| StringUtils.startsWithIgnoreCaseAndWs(sql, "CREATE") //$NON-NLS-1$
					|| StringUtils.startsWithIgnoreCaseAndWs(sql, "ALTER")
					|| StringUtils.startsWithIgnoreCaseAndWs(sql, "TRUNCATE")) { //$NON-NLS-1$
				throw SQLError.createSQLException(Messages
						.getString("Statement.57"), //$NON-NLS-1$
						SQLError.SQL_STATE_ILLEGAL_ARGUMENT); //$NON-NLS-1$
			}
		}
	}

[31 Mar 2008 15:41] Tonci Grgin

Hi Vincent and thanks for your report.

Verified by looking into latest c/J 5.0 code.

[22 Jan 2013 22:56] John Russell

Added to changelog for 5.1.23: 

executeQuery() in Statement.java let TRUNCATE queries be executed,
although that method is supposed to block any request that modifies
the database. TRUNCATE and RENAME are now filtered for
executeQuery().