Kuali Rice Development
  1. Kuali Rice Development
  2. KULRICE-12842

RoleDaoJdbc generating wrong SQL when null qualifier passed in.

    Details

    • Type: Bug Fix Bug Fix
    • Status: Closed Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.5
    • Fix Version/s: 2.5
    • Component/s: Development
    • Security Level: Public (Public: Anyone can view)
    • Labels:
      None
    • Similar issues:
      KULRICE-13302RoleDaoJdbc creates invalid sql statement when saving new proposal
      KULRICE-7263Passing Collections.emptyMap() as role qualifier causes failures in KIM
      KULRICE-6846null Lookup criteria causes sql exception
      KULRICE-5315KimRoleTypeService (if configured) should get to decide how to handle null qualifier match
      KULRICE-1365SQL error when doing a Rule Lookup by person reviewer when attempting to search workgroup membership
      KULRICE-10640BusinessObjectAuthorizationServiceImpl unmask methods do not consult authorizers for role qualifiers
      KULRICE-7612Update core and location services to not throw exceptions when passed null arguments
      KULRICE-6320Allow passing in null PermissionDetails when having to perform generic permission match
      KULRICE-2451Bad query generated when invoking KimRoleDaoOjb.getDelegationImplMapFromRoleIds
      KULRICE-13327IT Failure PostProcessorTest in CI, passes locally
    • Rice Module:
      KIM
    • Application Requirement:
      KC
    • Sprint:
      Core 2.5.0-m4 Sprint 2
    • KAI Review Status:
      Not Required
    • KTI Review Status:
      Not Required
    • Code Review Status:
      Not Required
    • Include in Release Notes?:
      Yes

      Description

      In KC when we assign a principal to a role, sometimes, we pass in a qualifier that has a value of null, so something like qualifier

      {key="proposal",value="null"}

      . When this happens, then in the RoleDaoJdbc.java code, the wrong SQL is generated in lines

      RoleDaoJdbc.java lines 149-160
       sql1.append(" EXISTS (SELECT B1.ROLE_MBR_ID FROM KRIM_ROLE_MBR_ATTR_DATA_T B1 WHERE (");
                                  for (Map.Entry<String, String> qualifier : qual.entrySet()) {
                                      if (StringUtils.isNotEmpty(qualifier.getValue())) {
                                          String value = (qualifier.getValue()).replace('*', '%');
                                          sql1.append(" (B1.ATTR_VAL LIKE ? AND B1.KIM_ATTR_DEFN_ID = ? ) ");
                                          params1.add(value);
                                          params1.add(qualifier.getKey());
                                      }
                                      sql1.append("OR");
                                  }
                                  sql1.delete(sql1.length() - 2, sql1.length());
                                  sql1.append(") AND B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )");
      

      The "where" clause is added to the SQL before checking if qualifier.getValue is empty. Therefore, you end up with SQL that looks like this

      SELECT A0.ROLE_MBR_ID AS ROLE_MBR_ID,A0.ROLE_ID AS ROLE_ID,A0.MBR_ID AS MBR_ID,A0.MBR_TYP_CD AS MBR_TYP_CD,A0.VER_NBR AS ROLE_MBR_VER_NBR,A0.OBJ_ID AS ROLE_MBR_OBJ_ID,A0.ACTV_FRM_DT AS ROLE_MBR_ACTV_FRM_DT ,A0.ACTV_TO_DT AS ROLE_MBR_ACTV_TO_DT, BO.KIM_TYP_ID AS KIM_TYP_ID, BO.KIM_ATTR_DEFN_ID AS KIM_ATTR_DEFN_ID, BO.ATTR_VAL AS ATTR_VAL, BO.ATTR_DATA_ID AS ATTR_DATA_ID, BO.OBJ_ID AS ATTR_DATA_OBJ_ID, BO.VER_NBR AS ATTR_DATA_VER_NBR, C0.KIM_ATTR_DEFN_ID AS KIM_ATTR_DEFN_ID, C0.OBJ_ID AS ATTR_DEFN_OBJ_ID, C0.VER_NBR as ATTR_DEFN_VER_NBR, C0.NM AS ATTR_NAME, C0.LBL as ATTR_DEFN_LBL, C0.ACTV_IND as ATTR_DEFN_ACTV_IND, C0.NMSPC_CD AS ATTR_DEFN_NMSPC_CD, C0.CMPNT_NM AS ATTR_DEFN_CMPNT_NM FROM KRIM_ROLE_MBR_T A0 JOIN KRIM_ROLE_MBR_ATTR_DATA_T BO ON A0.ROLE_MBR_ID = BO.ROLE_MBR_ID JOIN KRIM_ATTR_DEFN_T C0 ON BO.KIM_ATTR_DEFN_ID = C0.KIM_ATTR_DEFN_ID WHERE A0.ROLE_ID IN ('1000000') AND A0.MBR_TYP_CD = 'P' AND EXISTS (SELECT B1.ROLE_MBR_ID FROM KRIM_ROLE_MBR_ATTR_DATA_T B1 WHERE () AND B1.ROLE_MBR_ID = A0.ROLE_MBR_ID ) ORDER BY ROLE_MBR_ID;

      Notice the empty WHERE clause. This causes MySQL to throw a "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND B1.ROLE_MBR_ID = A0.ROLE_MBR_ID ) ORDER BY ROLE_MBR_ID' at line 1"

      The above WHERE clause should only be added if the qualifier is not empty.

      The following fix seems to work for us. Please review and modify as needed.

      sql1.append(" EXISTS (SELECT B1.ROLE_MBR_ID FROM KRIM_ROLE_MBR_ATTR_DATA_T B1 WHERE ");
                                  int conditionCount = 0;
                                  for (Map.Entry<String, String> qualifier : qual.entrySet()) {
                                      if (StringUtils.isNotEmpty(qualifier.getValue())) {
                                          conditionCount++;
                                          // if encoutering for first time then open paren
                                          if (conditionCount == 1) {
                                          sql1.append("(");
                                          }
                                          String value = (qualifier.getValue()).replace('*', '%');
                                          sql1.append(" (B1.ATTR_VAL LIKE ? AND B1.KIM_ATTR_DEFN_ID = ? ) ");
                                          params1.add(value);
                                          params1.add(qualifier.getKey());
                                      }
                                      sql1.append("OR");
                                  }
                                  sql1.delete(sql1.length() - 2, sql1.length());
                                  // if attribute values were found then close paren and add "AND"
                                  if (conditionCount != 0) {
                                     sql1.append(") AND");
                                  }
                                  sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )");
      

        Activity

          People

          • Assignee:
            Kristina Taylor (Inactive)
            Reporter:
            Gayathri Athreya
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Time Tracking

              Estimated:
              Original Estimate - 1 hour
              1h
              Remaining:
              Remaining Estimate - 0 minutes
              0m
              Logged:
              Time Spent - 1 hour
              1h

                Agile

                  Structure Helper Panel