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 Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:Current OS:Any
Assigned to: Alexander Soklakov CPU Architecture:Any
Triage: D2 (Serious)

[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().