Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-1039

setQueryTimeout not honored by CallableStatement for procedures depending on security context (2.7 only)

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 2.7.7
    • 2.7.8
    • Other
    • None

    Description

      When as a non-SUPER user calling a stored procedure with a DEFINER having full privileges and DEFINER security context no timeout happens when using CallableStatement with setQueryTimeout set.

      It is only this combination that leads to no timeout being triggered.
      If the connection user has full privileges: times out just fine
      If the procedure has a non-full priv. definer, or INVOKER security context: times out just fine
      Using regular Statment instead of CallableStatement: times out just fine
      C/J 3.x instead of 2.7.x: times out just fine.

      How to reproduce:

      SQL setup

      CREATE USER user@localhost IDENTIFIED BY 'Secret23!';
      GRANT ALL ON test.* TO user@localhost;
       
      DELIMITER //
       
      CREATE DEFINER=root@localhost PROCEDURE p_r_d() SQL SECURITY DEFINER
      BEGIN
      	SELECT SLEEP(60), "hello";
      END;
      //
       
      CREATE DEFINER=root@localhost PROCEDURE p_r_i() SQL SECURITY INVOKER
      BEGIN
      	SELECT SLEEP(60), "hello";
      END;
      //
       
      CREATE DEFINER=user@localhost PROCEDURE p_u_d() SQL SECURITY DEFINER
      BEGIN
      	SELECT SLEEP(60), "hello";
      END;
      //
       
      CREATE DEFINER=user@localhost PROCEDURE p_u_i() SQL SECURITY INVOKER
      BEGIN
      	SELECT SLEEP(60), "hello";
      END;
      //
       
      DELIMITER ;
      

      Java test code:

      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.Statement;
      import java.sql.CallableStatement;
      import java.sql.ResultSet;
      import java.sql.SQLException;
       
      public class CS0492599 {
          public static void run_query(String title, String query, String connect_url) {
      	System.out.print(title);
      	System.out.print(" -> ");
       
      	try {
      	    Connection con = DriverManager.getConnection(connect_url);
      	    
                  Statement st = con.createStatement();
      	    st.setQueryTimeout(10);
                  ResultSet rs = st.executeQuery(query);
      	    
                  while (rs.next()) {
                      System.out.print(rs.getString(1));
                      System.out.print(": ");
                      System.out.println(rs.getString(2));
                  }
      	    
                  con.close();
      	    
                  System.out.println();
              } catch (SQLException e) {
                  System.out.println(e.getMessage());
              }
          }
       
          public static void run_callable(String title, String query, String connect_url) {
      	System.out.print(title);
      	System.out.print(" -> ");
       
      	try {
      	    Connection con = DriverManager.getConnection(connect_url);
      	    
                  CallableStatement st = con.prepareCall(query);
      	    st.setQueryTimeout(10);
                  boolean rs = st.execute();
       
      	    if (rs) {
      		System.out.print("success");
      	    } else {
      		System.out.print("fail");
      	    }
      	    
                  con.close();
      	    
                  System.out.println();
              } catch (SQLException e) {
                  System.out.println(e.getMessage());
              }
          }
       
          public static void main(String[] args){
      	String connect_url = "jdbc:mariadb://localhost/test"
      	    + "?user=user"
      	    + "&password=Secret23!";
      	
      	System.out.println("using Statement:");
      	run_query("root, definer", "CALL p_r_d;", connect_url);    
      	run_query("root, invoker", "CALL p_r_i;", connect_url);    
      	run_query("user, definer", "CALL p_u_d;", connect_url);    
      	run_query("user, invoker", "CALL p_u_i;", connect_url);    
       
      	System.out.println("using CallableStatement:");
      	run_callable("root, definer", "CALL p_r_d;", connect_url);    
      	run_callable("root, invoker", "CALL p_r_i;", connect_url);    
      	run_callable("user, definer", "CALL p_u_d;", connect_url);    
      	run_callable("user, invoker", "CALL p_u_i;", connect_url);    
          }
       
      }
      
      

      Using 2.7.7 this leads to:

      using Statement:
      root, definer -> (conn=52) Query execution was interrupted (max_statement_time exceeded)
      root, invoker -> (conn=53) Query execution was interrupted (max_statement_time exceeded)
      user, definer -> (conn=54) Query execution was interrupted (max_statement_time exceeded)
      user, invoker -> (conn=55) Query execution was interrupted (max_statement_time exceeded)
      using CallableStatement:
      root, definer -> success
      root, invoker -> (conn=58) Query timed out
      user, definer -> (conn=60) Query timed out
      user, invoker -> (conn=62) Query timed out
      

      Using 3.0.9 or 3.1.0:

      using Statement:
      root, definer -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)
      (conn=64) Query execution was interrupted (max_statement_time exceeded)
      root, invoker -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)
      (conn=65) Query execution was interrupted (max_statement_time exceeded)
      user, definer -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)
      (conn=66) Query execution was interrupted (max_statement_time exceeded)
      user, invoker -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)
      (conn=67) Query execution was interrupted (max_statement_time exceeded)
      using CallableStatement:
      root, definer -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)
      (conn=68) Query execution was interrupted (max_statement_time exceeded)
      root, invoker -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)
      (conn=69) Query execution was interrupted (max_statement_time exceeded)
      user, definer -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)
      (conn=70) Query execution was interrupted (max_statement_time exceeded)
      user, invoker -> [ WARN] (main) Error: 1969-70100: Query execution was interrupted (max_statement_time exceeded)
      (conn=71) Query execution was interrupted (max_statement_time exceeded)
      

      Attachments

        Activity

          People

            diego dupin Diego Dupin
            hholzgra Hartmut Holzgraefe
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.