Uploaded image for project: 'Kuali Rice Development'
  1. Kuali Rice Development
  2. KULRICE-12842

RoleDaoJdbc generating wrong SQL when null qualifier passed in.

    Details

    • Type: Bug Fix
    • Status: Closed
    • Priority: 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
    • 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 )");
      

        Attachments

          Activity

          Hide
          kbtaylor Kristina Taylor (Inactive) added a comment -

          The use case here is best described by Gayathri:

          Regarding the SQL, when a proposal is first initiated, the proposal number is null which is why that null qualifier gets passed to Rice. I do not know how it worked in the past but it seems to me like irrespective, if there is a null value, the SQL needs to change accordingly. Rice is doing a check for it already but not handling it right.

          Thus, KC should not have to change how they fetch attributes attributed to proposals based on whether the proposal number exists or doesn't. The first time, they would be searching for any proposal number and subsequent times they would narrow down their search to a specific proposal number. This is a requirement that we need to handle empty qualifiers correctly, and the suggested code will do that.

          Show
          kbtaylor Kristina Taylor (Inactive) added a comment - The use case here is best described by Gayathri: Regarding the SQL, when a proposal is first initiated, the proposal number is null which is why that null qualifier gets passed to Rice. I do not know how it worked in the past but it seems to me like irrespective, if there is a null value, the SQL needs to change accordingly. Rice is doing a check for it already but not handling it right. Thus, KC should not have to change how they fetch attributes attributed to proposals based on whether the proposal number exists or doesn't. The first time, they would be searching for any proposal number and subsequent times they would narrow down their search to a specific proposal number. This is a requirement that we need to handle empty qualifiers correctly, and the suggested code will do that.

            People

            • Assignee:
              kbtaylor Kristina Taylor (Inactive)
              Reporter:
              gathreya 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