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

        Gayathri Athreya made changes -
        Field Original Value New Value
        Link This issue cloned from KCINFR-1021 [ KCINFR-1021 ]
        Gayathri Athreya made changes -
        Summary KIM Role broken RoleDaoOjb generating wrong SQL when null qualifier passed in.
        Description KC is throwing exception while creating new PD document and adding user roles.

        Verified in trunk.
        Steps to reproduce

        1. Login as aslusar or oblood (Proposal Aggregator)
        2. Create PD, Save and continue

        Exception
        org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00936: missing expression
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
        at org.kuali.rice.kim.impl.role.RoleDaoJdbc.getRoleMembersForRoleIds(RoleDaoJdbc.java:60)
        at org.kuali.rice.kim.impl.role.RoleServiceImpl.assignPrincipalToRole(RoleServiceImpl.java:1782)

        If user does not have Aggregator role, exception is thrown while creating Aggregator role for initiator, if not while adding roles from template.
        In KC when we assign a principal to a role, sometimes, we pass in a null 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

        {code:title="RoleDaoJdbc.java line 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 )");
        {code}

        The "where" clause is added to the SQL before the for loop begins so if there are no values, you end up with SQL that looks like this

        {code:title=SQL generated}
        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;
        {code}

        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.
        Gayathri Athreya made changes -
        Reporter Rajeev Mancheril [ rmancher ] Gayathri Athreya [ gathreya ]
        Assignee Gayathri Athreya [ gathreya ]
        Gayathri Athreya made changes -
        Description In KC when we assign a principal to a role, sometimes, we pass in a null 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

        {code:title="RoleDaoJdbc.java line 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 )");
        {code}

        The "where" clause is added to the SQL before the for loop begins so if there are no values, you end up with SQL that looks like this

        {code:title=SQL generated}
        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;
        {code}

        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.
        In KC when we assign a principal to a role, sometimes, we pass in a null 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

        {code:title=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 )");
        {code}

        The "where" clause is added to the SQL before the for loop begins so if there are no values, you end up with SQL that looks like this

        {code:title=SQL generated}
        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;
        {code}

        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.
        Gayathri Athreya made changes -
        Description In KC when we assign a principal to a role, sometimes, we pass in a null 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

        {code:title=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 )");
        {code}

        The "where" clause is added to the SQL before the for loop begins so if there are no values, you end up with SQL that looks like this

        {code:title=SQL generated}
        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;
        {code}

        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.
        In KC when we assign a principal to a role, sometimes, we pass in a null 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

        {code:title=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 )");
        {code}

        The "where" clause is added to the SQL before the for loop begins so if there are no values, you end up with SQL that looks like this

        {code:title=SQL generated}
        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;
        {code}

        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.
        Gayathri Athreya made changes -
        Description In KC when we assign a principal to a role, sometimes, we pass in a null 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

        {code:title=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 )");
        {code}

        The "where" clause is added to the SQL before the for loop begins so if there are no values, you end up with SQL that looks like this

        {code:title=SQL generated}
        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;
        {code}

        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.
        In KC when we assign a principal to a role, sometimes, we pass in a null 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

        {code:title=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 )");
        {code}

        The "where" clause is added to the SQL before the for loop begins so if there are no values in the qualifier, you end up with SQL that looks like this

        {code:title=SQL generated}
        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;
        {code}

        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.
        Gayathri Athreya made changes -
        Description In KC when we assign a principal to a role, sometimes, we pass in a null 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

        {code:title=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 )");
        {code}

        The "where" clause is added to the SQL before the for loop begins so if there are no values in the qualifier, you end up with SQL that looks like this

        {code:title=SQL generated}
        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;
        {code}

        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.
        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

        {code:title=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 )");
        {code}

        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

        {code:title=SQL generated}
        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 {color:red}WHERE (){color} AND B1.ROLE_MBR_ID = A0.ROLE_MBR_ID ) ORDER BY ROLE_MBR_ID;
        {code}

        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.
        Gayathri Athreya made changes -
        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

        {code:title=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 )");
        {code}

        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

        {code:title=SQL generated}
        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 {color:red}WHERE (){color} AND B1.ROLE_MBR_ID = A0.ROLE_MBR_ID ) ORDER BY ROLE_MBR_ID;
        {code}

        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.
        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

        {code:title=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 )");
        {code}

        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

        {code:title=SQL generated}
        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;
        {code}

        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.
        Gayathri Athreya made changes -
        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

        {code:title=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 )");
        {code}

        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

        {code:title=SQL generated}
        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;
        {code}

        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.
        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

        {code:title=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 )");
        {code}

        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 {color:red}WHERE (){color} 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.
        Gayathri Athreya made changes -
        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

        {code:title=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 )");
        {code}

        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 {color:red}WHERE (){color} 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.
        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

        {code:title=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 )");
        {code}

        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 {color:red}WHERE (){color} 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.

        Proposed Fix:
        {code}
        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 (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 (conditionCount != 0) {
                                       sql.append(") AND");
                                    }
                                    sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )");
        {code}
        Gayathri Athreya made changes -
        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

        {code:title=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 )");
        {code}

        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 {color:red}WHERE (){color} 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.

        Proposed Fix:
        {code}
        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 (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 (conditionCount != 0) {
                                       sql.append(") AND");
                                    }
                                    sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )");
        {code}
        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

        {code:title=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 )");
        {code}

        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 {color:red}WHERE (){color} 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.

        Proposed Fix:
        {code}
        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 (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 (conditionCount != 0) {
                                       sql1.append(") AND");
                                    }
                                    sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )");
        {code}
        Gayathri Athreya made changes -
        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

        {code:title=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 )");
        {code}

        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 {color:red}WHERE (){color} 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.

        Proposed Fix:
        {code}
        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 (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 (conditionCount != 0) {
                                       sql1.append(") AND");
                                    }
                                    sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )");
        {code}
        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

        {code:title=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 )");
        {code}

        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 {color:red}WHERE (){color} 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.
        {code}
        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 (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 (conditionCount != 0) {
                                       sql1.append(") AND");
                                    }
                                    sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )");
        {code}
        Gayathri Athreya made changes -
        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

        {code:title=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 )");
        {code}

        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 {color:red}WHERE (){color} 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.
        {code}
        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 (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 (conditionCount != 0) {
                                       sql1.append(") AND");
                                    }
                                    sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )");
        {code}
        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

        {code:title=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 )");
        {code}

        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 {color:red}WHERE (){color} 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.
        {code}
        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 "ADD"
                                    if (conditionCount != 0) {
                                       sql1.append(") AND");
                                    }
                                    sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )");
        {code}
        Gayathri Athreya made changes -
        Project KC Infrastructure [ 10580 ] Kuali Rice Development [ 10220 ]
        Key KCINFR-1022 KULRICE-12842
        Workflow custom [ 349669 ] Rice Workflow [ 349706 ]
        Include in Release Notes? Yes [ 14658 ]
        Fix Version/s 2.5 [ 17044 ]
        Fix Version/s Release 6.0 [ 17063 ]
        Security Public [ 10056 ]
        KTI Review Status Not Required [ 13053 ]
        KAI Review Status Not Required [ 13050 ]
        Requires Application Refactoring No [ 10923 ]
        Code Review Status Not Required [ 14985 ]
        Documentation Review Status Pending Review [ 14643 ]
        Responsible Team Rice Team [ 10940 ]
        Reviewed by Prioritization Committee No [ 10443 ]
        Affects Version/s 2.5 [ 17044 ]
        Affects Version/s Release 6.0 [ 17063 ]
        QA Review Status Pending Review [ 13669 ]
        KC Functional Area Proposal Development [ 14576 ]
        Application Requirement KC [ 10875 ]
        Rice Module KIM [ 11060 ]
        Proceed? Unhindered [ 10760 ]
        Component/s Development [ 11244 ]
        Component/s Rice Integration [ 13192 ]
        Forced Change No [ 11114 ]
        Gayathri Athreya made changes -
        Priority Critical [ 2 ] Blocker [ 1 ]
        Gayathri Athreya made changes -
        Summary RoleDaoOjb generating wrong SQL when null qualifier passed in. RoleDaoJdbc generating wrong SQL when null qualifier passed in.
        Gayathri Athreya made changes -
        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

        {code:title=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 )");
        {code}

        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 {color:red}WHERE (){color} 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.
        {code}
        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 "ADD"
                                    if (conditionCount != 0) {
                                       sql1.append(") AND");
                                    }
                                    sql1.append(" B1.ROLE_MBR_ID = A0.ROLE_MBR_ID )");
        {code}
        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

        {code:title=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 )");
        {code}

        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 {color:red}WHERE (){color} 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.
        {code}
        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 )");
        {code}
        Kristina Taylor (Inactive) made changes -
        Rank Ranked higher
        Kristina Taylor (Inactive) made changes -
        Sprint Core 2.5.0-m5 Sprint 1 [ 330 ]
        Kristina Taylor (Inactive) made changes -
        Rank Ranked lower
        Kristina Taylor (Inactive) made changes -
        Rank Ranked higher
        Kristina Taylor (Inactive) made changes -
        Original Estimate 2 hours [ 7200 ]
        Remaining Estimate 2 hours [ 7200 ]
        Kristina Taylor (Inactive) made changes -
        Sprint Core 2.5.0-m5 Sprint 1 [ 330 ] Core 2.5.0-m4 Sprint 2 [ 320 ]
        Kristina Taylor (Inactive) made changes -
        Rank Ranked higher
        Kristina Taylor (Inactive) made changes -
        Assignee Kristina Taylor [ kbtaylor ]
        Kristina Taylor (Inactive) made changes -
        Status Open [ 1 ] In Progress [ 3 ]
        Kristina Taylor (Inactive) made changes -
        Original Estimate 2 hours [ 7200 ] 1 hour [ 3600 ]
        Remaining Estimate 2 hours [ 7200 ] 1 hour [ 3600 ]
        Kristina Taylor (Inactive) logged work - 19/Jun/14 3:30 PM
        • Time Spent:
          1 hour
           
          <No comment>
        Kristina Taylor (Inactive) made changes -
        Remaining Estimate 1 hour [ 3600 ] 0 minutes [ 0 ]
        Time Spent 1 hour [ 3600 ]
        Worklog Id 96789 [ 96789 ]
        Kristina Taylor (Inactive) made changes -
        Status In Progress [ 3 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Hide
        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
        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.
        Gayathri Athreya made changes -
        Link This issue is fixed by KRACOEUS-7411 [ KRACOEUS-7411 ]
        Gayathri Athreya made changes -
        Link This issue is fixed by KRACOEUS-7411 [ KRACOEUS-7411 ]
        Matt Sargent made changes -
        Status Resolved [ 5 ] Closed [ 6 ]

          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