741 Todd Farmer 2008-10-14
BUG#40031 - allow CallableStatement.execute() to call a stored procedure that has been defined as NO SQL or SQL READ DATA.
added:
src/testsuite/simple/ReadOnlyCallableStatementTest.java
modified:
src/com/mysql/jdbc/CallableStatement.java
src/com/mysql/jdbc/DatabaseMetaData.java
src/com/mysql/jdbc/DatabaseMetaDataUsingInfoSchema.java
src/com/mysql/jdbc/PreparedStatement.java
src/testsuite/regression/ConnectionRegressionTest.java
740 mark@stripped 2008-10-13
Fixed BUG#34185 - Statement.getGeneratedKeys() does not raise exception when statement was not created with Statement.RETURN_GENERATED_KEYS flags.
modified:
CHANGES
src/com/mysql/jdbc/CallableStatement.java
src/com/mysql/jdbc/StatementImpl.java
src/testsuite/regression/StatementRegressionTest.java
=== modified file 'src/com/mysql/jdbc/CallableStatement.java'
--- a/src/com/mysql/jdbc/CallableStatement.java 2008-10-13 16:46:27 +0000
+++ b/src/com/mysql/jdbc/CallableStatement.java 2008-10-15 05:12:44 +0000
@@ -34,7 +34,9 @@ import java.sql.Clob;
import java.sql.Date;
import java.sql.ParameterMetaData;
import java.sql.Ref;
+import java.sql.ResultSet;
import java.sql.SQLException;
+import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
@@ -147,6 +149,10 @@ public class CallableStatement extends P
Map parameterMap;
+ boolean isReadOnlySafeProcedure = false;
+
+ boolean isReadOnlySafeChecked = false;
+
/**
* Constructor that converts a full list of parameter metadata into one
* that only represents the placeholders present in the {CALL ()}.
@@ -161,6 +167,8 @@ public class CallableStatement extends P
int[] localParameterMap = placeholderToParameterIndexMap;
int parameterMapLength = localParameterMap.length;
+ this.isReadOnlySafeProcedure = fullParamInfo.isReadOnlySafeProcedure;
+ this.isReadOnlySafeChecked = fullParamInfo.isReadOnlySafeChecked;
parameterList = new ArrayList(fullParamInfo.numParameters);
parameterMap = new HashMap(fullParamInfo.numParameters);
@@ -2352,5 +2360,71 @@ public class CallableStatement extends P
setNCharacterStream(getNamedParamIndex(parameterName, false), value, length);
}
+
+ /**
+ * Check whether the stored procedure alters any data or is safe for read-only usage.
+ *
+ * @return true if procedure does not alter data
+ * @throws SQLException
+ */
+ private boolean checkReadOnlyProcedure() throws SQLException {
+ if (this.connection.getNoAccessToProcedureBodies()) {
+ return false;
+ }
+
+ synchronized(this.paramInfo){
+ if(this.paramInfo.isReadOnlySafeChecked){
+ return this.paramInfo.isReadOnlySafeProcedure;
+ }
+ }
+
+ ResultSet rs = null;
+ try {
+ String procName = extractProcedureName();
+
+
+ String catalog = this.currentCatalog;
+
+ if (procName.indexOf(".") != -1) {
+ catalog = procName.substring(0, procName.indexOf("."));
+ procName = procName.substring(procName.indexOf(".") + 1);
+ procName = new String(StringUtils.stripEnclosure(procName.getBytes(), "`", "`"));
+ }
+ PreparedStatement ps = ((DatabaseMetaData) this.connection.getMetaData()).prepareMetaDataSafeStatement(
+ "SELECT SQL_DATA_ACCESS FROM "
+ + " information_schema.routines "
+ + " WHERE routine_schema = ? "
+ + " AND routine_name = ?");
+
+ ps.setString(1, catalog);
+ ps.setString(2, procName);
+ rs = ps.executeQuery();
+ if(rs.next()) {
+ String sqlDataAccess = rs.getString(1);
+ rs.close();
+ ps.close();
+ if(sqlDataAccess.equals("READS SQL DATA") || sqlDataAccess.equals("NO SQL")){
+ synchronized(this.paramInfo){
+ this.paramInfo.isReadOnlySafeChecked = true;
+ this.paramInfo.isReadOnlySafeProcedure = true;
+ }
+ return true;
+ }
+ }
+ } catch (SQLException e) {
+ // swallow the Exception
+ }
+ synchronized(this.paramInfo){
+ this.paramInfo.isReadOnlySafeChecked = false;
+ this.paramInfo.isReadOnlySafeProcedure = false;
+ }
+ return false;
+
+ }
+
+ protected boolean checkReadOnlySafeStatement() throws SQLException {
+ return (super.checkReadOnlySafeStatement() || this.checkReadOnlyProcedure());
+ }
+
}
=== modified file 'src/com/mysql/jdbc/DatabaseMetaData.java'
--- a/src/com/mysql/jdbc/DatabaseMetaData.java 2008-09-09 17:58:50 +0000
+++ b/src/com/mysql/jdbc/DatabaseMetaData.java 2008-10-15 05:12:44 +0000
@@ -8129,4 +8129,26 @@ public class DatabaseMetaData implements
public boolean supportsStoredFunctionsUsingCallSyntax() throws SQLException {
return true;
}
+
+ /**
+ * Get a prepared statement to query information_schema tables.
+ *
+ * @return PreparedStatement
+ * @throws SQLException
+ */
+ protected PreparedStatement prepareMetaDataSafeStatement(String sql)
+ throws SQLException {
+ // Can't use server-side here as we coerce a lot of types to match
+ // the spec.
+ PreparedStatement pStmt = (PreparedStatement) this.conn
+ .clientPrepareStatement(sql);
+
+ if (pStmt.getMaxRows() != 0) {
+ pStmt.setMaxRows(0);
+ }
+
+ pStmt.setHoldResultsOpenOverClose(true);
+
+ return pStmt;
+ }
}
=== modified file 'src/com/mysql/jdbc/DatabaseMetaDataUsingInfoSchema.java'
--- a/src/com/mysql/jdbc/DatabaseMetaDataUsingInfoSchema.java 2008-02-21 02:52:52 +0000
+++ b/src/com/mysql/jdbc/DatabaseMetaDataUsingInfoSchema.java 2008-10-15 05:12:44 +0000
@@ -1223,18 +1223,5 @@ public class DatabaseMetaDataUsingInfoSc
}
}
- private PreparedStatement prepareMetaDataSafeStatement(String sql)
- throws SQLException {
- // Can't use server-side here as we coerce a lot of types to match
- // the spec.
- PreparedStatement pStmt = (PreparedStatement) this.conn.clientPrepareStatement(sql);
- if (pStmt.getMaxRows() != 0) {
- pStmt.setMaxRows(0);
- }
-
- pStmt.setHoldResultsOpenOverClose(true);
-
- return pStmt;
- }
}
=== modified file 'src/com/mysql/jdbc/PreparedStatement.java'
--- a/src/com/mysql/jdbc/PreparedStatement.java 2008-07-31 13:21:48 +0000
+++ b/src/com/mysql/jdbc/PreparedStatement.java 2008-10-15 05:12:44 +0000
@@ -894,6 +894,16 @@ public class PreparedStatement extends c
bytesOut.write(buf, lastwritten, size - lastwritten);
}
}
+
+ /**
+ * Check to see if the statement is safe for read-only slaves after failover.
+ *
+ * @return true if safe for read-only.
+ * @throws SQLException
+ */
+ protected boolean checkReadOnlySafeStatement() throws SQLException {
+ return ((!this.connection.isReadOnly()) || (this.firstCharOfStmt == 'S'));
+ }
/**
* Some prepared statements return multiple results; the execute method
@@ -911,10 +921,10 @@ public class PreparedStatement extends c
ConnectionImpl locallyScopedConn = this.connection;
- if (locallyScopedConn.isReadOnly() && (this.firstCharOfStmt != 'S')) {
- throw SQLError.createSQLException(Messages.getString("PreparedStatement.20") //$NON-NLS-1$
- + Messages.getString("PreparedStatement.21"), //$NON-NLS-1$
- SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
+ if(!checkReadOnlySafeStatement()) {
+ throw SQLError.createSQLException(Messages.getString("PreparedStatement.20") //$NON-NLS-1$
+ + Messages.getString("PreparedStatement.21"), //$NON-NLS-1$
+ SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
}
ResultSetInternalMethods rs = null;
=== modified file 'src/testsuite/regression/ConnectionRegressionTest.java'
--- a/src/testsuite/regression/ConnectionRegressionTest.java 2008-09-29 18:31:30 +0000
+++ b/src/testsuite/regression/ConnectionRegressionTest.java 2008-10-15 05:12:44 +0000
@@ -1606,7 +1606,7 @@ public class ConnectionRegressionTest ex
}
}
- private Connection getMasterSlaveReplicationConnection()
+ protected Connection getMasterSlaveReplicationConnection()
throws SQLException {
Connection replConn = new ReplicationDriver().connect(
=== added file 'src/testsuite/simple/ReadOnlyCallableStatementTest.java'
--- a/src/testsuite/simple/ReadOnlyCallableStatementTest.java 1970-01-01 00:00:00 +0000
+++ b/src/testsuite/simple/ReadOnlyCallableStatementTest.java 2008-10-15 05:12:44 +0000
@@ -0,0 +1,126 @@
+package testsuite.simple;
+
+import java.sql.CallableStatement;
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.Properties;
+
+import testsuite.regression.ConnectionRegressionTest;
+
+public class ReadOnlyCallableStatementTest extends ConnectionRegressionTest {
+ public ReadOnlyCallableStatementTest(String name) {
+ super(name);
+
+ // TODO Auto-generated constructor stub
+ }
+
+ public void testReadOnlyWithProcBodyAccess() throws Exception {
+ if (versionMeetsMinimum(5, 0)) {
+ Connection replConn = null;
+ Properties props = getMasterSlaveProps();
+ props.setProperty("autoReconnect", "true");
+
+
+ try {
+ createProcedure("testProc1", "()\n"
+ + "READS SQL DATA\n"
+ + "begin\n"
+ + "SELECT NOW();\n"
+ + "end\n");
+
+ createProcedure("`testProc.1`", "()\n"
+ + "READS SQL DATA\n"
+ + "begin\n"
+ + "SELECT NOW();\n"
+ + "end\n");
+
+ replConn = getMasterSlaveReplicationConnection();
+ replConn.setReadOnly(true);
+
+ CallableStatement stmt = replConn.prepareCall("CALL testProc1()");
+ stmt.execute();
+ stmt.execute();
+
+ stmt = replConn.prepareCall("CALL test.testProc1()");
+ stmt.execute();
+
+ stmt = replConn.prepareCall("CALL test.`testProc.1`()");
+ stmt.execute();
+
+ } finally {
+
+ closeMemberJDBCResources();
+
+ if (replConn != null) {
+ replConn.close();
+ }
+ }
+ }
+ }
+
+ public void testNotReadOnlyWithProcBodyAccess() throws Exception {
+ if (versionMeetsMinimum(5, 0)) {
+
+ Connection replConn = null;
+ Properties props = getMasterSlaveProps();
+ props.setProperty("autoReconnect", "true");
+
+
+ try {
+ createProcedure("testProc2", "()\n"
+ + "MODIFIES SQL DATA\n"
+ + "begin\n"
+ + "SELECT NOW();\n"
+ + "end\n");
+
+ createProcedure("`testProc.2`", "()\n"
+ + "MODIFIES SQL DATA\n"
+ + "begin\n"
+ + "SELECT NOW();\n"
+ + "end\n");
+
+ replConn = getMasterSlaveReplicationConnection();
+ replConn.setReadOnly(true);
+
+ CallableStatement stmt = replConn.prepareCall("CALL testProc2()");
+
+ try{
+ stmt.execute();
+ fail("Should not execute because procedure modifies data.");
+ } catch (SQLException e) {
+ assertEquals("Should error for read-only connection.", e.getSQLState(), "S1009");
+ }
+
+ stmt = replConn.prepareCall("CALL test.testProc2()");
+
+ try{
+ stmt.execute();
+ fail("Should not execute because procedure modifies data.");
+ } catch (SQLException e) {
+ assertEquals("Should error for read-only connection.", e.getSQLState(), "S1009");
+ }
+
+ stmt = replConn.prepareCall("CALL test.`testProc.2`()");
+
+ try{
+ stmt.execute();
+ fail("Should not execute because procedure modifies data.");
+ } catch (SQLException e) {
+ assertEquals("Should error for read-only connection.", e.getSQLState(), "S1009");
+ }
+
+
+ } finally {
+
+ closeMemberJDBCResources();
+
+ if (replConn != null) {
+ replConn.close();
+ }
+ }
+ }
+ }
+
+
+
+}
| Thread |
|---|
| • bzr push into connector-j/branches/branch_5_1 branch (todd.farmer:740to 741) Bug#40031 | Todd Farmer | 15 Oct |
