package kr.co.kihyun.beans.user; import java.sql.ResultSet; import java.sql.SQLException; import java.io.FileReader; import java.io.IOException; import kr.co.kihyun.beans.entity.MUser; import java.sql.ResultSetMetaData; import javax.jdo.PersistenceManager; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import kr.co.kihyun.db.DBManager; import kr.co.kihyun.db.*; import java.sql.*; import javax.jdo.Transaction; import kr.co.kihyun.beans.entity.util.MPersistenceManager; import kr.co.kihyun.beans.entity.util.PMF; public class MultiCheck extends DBManager { private static final Logger LOG = LoggerFactory.getLogger(MultiCheck.class); int cnt = 0; int count = 0; String[] array; String[] nameList; String[] getPassword; String[] deptID; String[] userId; String[] deptName; String[] adpInsttCode; String[] pNo; String[] kraOrgNm; String[] sOrginDeptFlag; //2014.08.19 Add by KWON,HAN : 원소속 부서 구분 int tcount = 0; ResultSet rs = null; ResultSetMetaData rsmd = null; int start = 0; int limit = 30; public void IdCheck(String id) throws Exception { StringBuilder sql = new StringBuilder(250); String sDualPostYn = ""; MUser mUser = null; PersistenceManager pm = new MPersistenceManager(PMF.get().getPersistenceManager()); Transaction tx = pm.currentTransaction(); try { tx.begin(); mUser = pm.detachCopy(pm.getObjectById(MUser.class, id)); sDualPostYn = mUser.getDualPostYn(); if (sDualPostYn == null) { sDualPostYn = ""; } tx.rollback(); //return executeLogin(userId, userPassword, ip, session, deptId); } catch (Exception e) { LOG.error("User '{}' IdCheck mUser failed: {}", id, e.getMessage()); //return 11; } finally { if (tx.isActive()) { tx.rollback(); } pm.close(); } FileReader in = null; try { // in = new FileReader("/edudocs/wasapps/atswas/moumi_gacp.properties"); //운영서버용 in = new FileReader("C:/edusys/moum00/was/moumi_gacp.properties"); //로컬서버용 //in = new FileReader("D:/NetBeansProjectsNeis/moumi5-war/src/main/webapp/moumi_gacp.properties"); //2014.08.19 Add By KWON,HAN // in = new FileReader("E:/02. Source/NEIS/moumi5-war/src/main/webapp/moumi_gacp.properties"); //2014.09.30 Add By Youngjun Cho int c; String s = new String(); while ((c = in.read()) != -1) { s = s + (char) c; } String[] data = s.split(","); String[] subData = null; String connection1 = data[0]; String connection2 = data[1]; String connection3 = data[2]; String loginTimeout = "10"; String maxActive = "100"; String maxIdle = "25"; String maxWait = "10"; String url = connection1; //개발 String user = connection2; String password = connection3; Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); //37.패스워드 평문 저장_CWE-256 Comment by YOUNGJUN,CHO // - 취약점의 해결책으로는 DB 연결시 암호화된 패스워드를 복호화해서 사용하라고 되어있음. // 현재 properties 파일에 평문으로 패스워드가 저장되어있는데, 이것을 암호화하여 시도별로 서버관리자가 properties 파일을 수정하거나 개발사업단에서 파일 수정 후 배포를 해야 한다. // 현재 테스트를 해볼 환경이 되지 않아 2014년도 추가개발에서는 해당 취약점에 대한 처리를 진행하지 않는것으로 함. // // - 향후 암호화 도입시 제안사항 // AES 128 이나 AES 256 알고리즘으로 진행하면 될거 같다. // 현재 프로젝트 소스에서 AES 128 은 사용이 가능하다. // 256 은 암호화 키를 32 bytes 로 해야하는데, illegal key size exception 이 발생한다. // 이를 해결하려면 256 암호화를 지원하는 unlimited strength crypto file(local_policy.jar, US_export_policy.jar) 이 필요하다. //===================================================== con = DriverManager.getConnection(url, user, password); //32.J2EE 직접 연결 관리_CWE-245 Comment by YOUNGJUN,CHO // context.xml 에 'jdbc/MoumiOB2' 라는 이름으로 GACKWD 에 접속하는 연결문자열을 정의해두고, lookup 을 통해 DB에 connection 을 할 수 있다. // 그러나 실제 운영상에선 tomcat 이 아니라 jeus 를 사용하는데, 현재 테스트를 해볼 환경이 되지 않아 2014년도 추가개발에서는 해당 취약점에 대한 처리를 진행하지 않는것으로 함. // - context.xml 파일에 추가했던 내용 // /* InitialContext initCtx = new InitialContext(); DataSource ds = (DataSource) initCtx.lookup("java:comp/env/jdbc/MoumiOB2"); con = ds.getConnection(); */ //===================================================== //2014.08.19 Update by KWON,HAN /* ORIGN_DEPT_FLAG */ /* N1 : 지방공무원 (겸임) */ /* N2 : 지방공무원(파견) */ /* N3 : 교원(겸임) */ /* N4 : 교원(파견) */ /* Y : 원소속 */ /* N5 : 병설유치원 */ //2015.01.26 Update by jskim /* 중복으로 겸임정보가 조회되는 문제로 겸임 파견기관은 모두 N1, 1로 수정 */ sql.append(" select us.USRID, dual.adp_dept_code, dual.adp_dptnm, dual.p_no \n") .append(" , 'N1' AS ORIGN_DEPT_FLAG \n") .append(" , '1' AS ORDER_BY \n") .append(" from TN_GPSEGOAD001 dual, TN_SYMUSMUM001 us, TC_SYMOCMOC000 dt \n") .append(" where us.P_NO = dual.P_NO and us.PSITN_ORG_CODE = dt.ORG_CODE \n") .append(" and ((dual.ADP_RELIS_YMD is not null and dual.ADP_RELIS_YMD >= to_char(sysdate, 'YYYYMMDD'))" + " or (dual.ADP_RELIS_YMD is null and dual.ADP_RELIS_PARNG_YMD >= to_char(sysdate, 'YYYYMMDD'))) \n") .append(" AND dual.GNFD_TRT_CODE = '2' \n") .append(" AND us.USRID='" + id + "' \n") .append(" AND DUAL.ADP_DEPT_CODE IS NOT NULL \n") .append(" union \n") .append(" select us.USRID, dual.DSPTC_DEPT_CODE, dual.dsptc_dptnm, dual.p_no \n") .append(" , 'N1' AS ORIGN_DEPT_FLAG \n") .append(" , '1' AS ORDER_BY \n") .append(" from TN_GPSEGODS001 dual, TN_SYMUSMUM001 us, TC_SYMOCMOC000 dt where us.P_NO = dual.P_NO \n") .append(" and us.PSITN_ORG_CODE = dt.ORG_CODE " ) .append(" and ((dual.DSPTC_END_YMD is not null and dual.DSPTC_END_YMD >= to_char(sysdate, 'YYYYMMDD'))" + " or (dual.DSPTC_END_YMD is null and dual.DSPTC_END_PARNG_YMD >= to_char(sysdate, 'YYYYMMDD'))) \n") .append(" AND dual.GNFD_TRT_CODE = '2' \n") .append(" AND us.USRID='" + id + "' \n") .append(" AND DUAL.DSPTC_DEPT_CODE IS NOT NULL \n") .append(" union \n") .append(" select us.USRID, dual.adp_dept_code, dual.adp_dptnm, dual.p_no \n") .append(" , 'N1' AS ORIGN_DEPT_FLAG \n") .append(" , '1' AS ORDER_BY \n") .append(" from TN_TPSEGOMR002 dual, TN_SYMUSMUM001 us, TC_SYMOCMOC000 dt where us.P_NO = dual.P_NO and us.PSITN_ORG_CODE = dt.ORG_CODE \n") .append(" and ((dual.ADP_RELIS_YMD is not null and dual.ADP_RELIS_YMD >= to_char(sysdate, 'YYYYMMDD'))" + " OR (dual.ADP_RELIS_YMD is null and dual.ADP_RELIS_PARNG_YMD >= to_char(sysdate, 'YYYYMMDD'))) ") .append(" AND dual.GNFD_TRT_CODE = '2' \n") .append(" AND us.USRID='" + id + "' \n") .append(" AND DUAL.ADP_DEPT_CODE IS NOT NULL \n") .append(" union \n") .append(" select us.USRID, dual.DSPTC_DEPT_CODE, dual.dsptc_dptnm, dual.p_no \n") .append(" , 'N1' AS ORIGN_DEPT_FLAG \n") .append(" , '1' AS ORDER_BY \n") .append(" from TN_TPSEGOTC001 dual, TN_SYMUSMUM001 us, TC_SYMOCMOC000 dt \n") .append(" where us.P_NO = dual.P_NO and us.PSITN_ORG_CODE = dt.ORG_CODE " ) .append(" and ((dual.DSPTC_END_YMD is not null and dual.DSPTC_END_YMD >= to_char(sysdate, 'YYYYMMDD'))" + " OR (dual.DSPTC_END_YMD is null and dual.DSPTC_END_PARNG_YMD >= to_char(sysdate, 'YYYYMMDD'))) \n") .append(" AND dual.GNFD_TRT_CODE = '2' \n") .append(" AND us.USRID='" + id + "' \n") .append(" AND DUAL.DSPTC_DEPT_CODE IS NOT NULL \n") .append(" union \n") .append(" select us.USRID,dt.ORG_CODE,dt.KRA_ORG_NM,us.P_NO \n") .append(" , 'Y' AS ORIGN_DEPT_FLAG \n") .append(" , '0' AS ORDER_BY \n") .append(" from TN_SYMUSMUM001 us, TC_SYMOCMOC000 dt \n") .append(" where us.PSITN_ORG_CODE = dt.ORG_CODE AND us.USRID='" + id + "' \n"); //2014.08.19 Add by KWON,HAN if ("Y".equals(sDualPostYn)) { sql.append(" union \n") .append(" SELECT '" + id + "' AS USRID \n") .append(" , A.ORG_CODE /* 병설유치원 부서ID */ \n") .append(" --, A.EAAA_YN /* 병설유치원 구분 */ \n") .append(" --, A.EAAA_SCHUL_CODE /* 원소속 부서ID */ \n") .append(" , DT.KRA_ORG_NM \n") .append(" , (SELECT T.P_NO FROM TN_SYMUSMUM001 T WHERE T.USRID = '" + id + "' AND ROWNUM = 1 ) AS P_NO \n") .append(" , 'N1' AS ORIGN_DEPT_FLAG \n") .append(" , '1' AS ORDER_BY \n") .append(" FROM TN_SYMOCMSI001 A \n") .append(" , TC_SYMOCMOC000 DT \n") .append(" WHERE A.EAAA_YN = 'Y' \n") .append(" AND A.ABSCH_YN != 'Y' \n") .append(" AND A.EAAA_SCHUL_CODE IS NOT NULL \n") .append(" AND A.EAAA_SCHUL_CODE IN ( SELECT DT.ORG_CODE \n") .append(" FROM TN_SYMUSMUM001 US, TC_SYMOCMOC000 DT \n") .append(" WHERE US.PSITN_ORG_CODE = DT.ORG_CODE \n") .append(" AND US.USRID = '" + id + "' ) \n") .append(" AND A.ORG_CODE = DT.ORG_CODE(+) \n") /* 분교 겸임업무 조회 2018.04.04 */ .append(" union \n") .append(" SELECT '" + id + "' AS USRID \n") .append(" , A.ORG_CODE /* 분교 부서ID */ \n") .append(" --, A.EAAA_YN /* 분교 구분 */ \n") .append(" --, A.EAAA_SCHUL_CODE /* 원소속 부서ID */ \n") .append(" , DT.KRA_ORG_NM \n") .append(" , (SELECT T.P_NO FROM TN_SYMUSMUM001 T WHERE T.USRID = '" + id + "' AND ROWNUM = 1 ) AS P_NO \n") .append(" , 'N1' AS ORIGN_DEPT_FLAG \n") .append(" , '1' AS ORDER_BY \n") .append(" FROM TN_SYMOCMSI001 A \n") .append(" , TC_SYMOCMOC000 DT \n") .append(" WHERE A.BNHH_YN = 'Y' \n") .append(" AND A.ABSCH_YN != 'Y' \n") .append(" AND A.MAISC_ORG_CODE IS NOT NULL \n") .append(" AND A.MAISC_ORG_CODE IN ( SELECT DT.ORG_CODE \n") .append(" FROM TN_SYMUSMUM001 US, TC_SYMOCMOC000 DT \n") .append(" WHERE US.PSITN_ORG_CODE = DT.ORG_CODE \n") .append(" AND US.USRID = '" + id + "' ) \n") .append(" AND A.ORG_CODE = DT.ORG_CODE(+) \n"); } sql.append(" ORDER BY ORDER_BY \n"); //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ System.out.println("sql -------------- " + sql.toString()); rs = execQuery(sql.toString()); if (rs != null) { rs.last(); tcount = rs.getRow(); rs.beforeFirst(); userId = new String[tcount]; adpInsttCode = new String[tcount]; deptName = new String[tcount]; pNo = new String[tcount]; sOrginDeptFlag = new String[tcount]; //2014.08.19 Add by KWON,HAN int i = 0; while(rs.next()){ userId[i] = rs.getString(1); adpInsttCode[i] = rs.getString(2); deptName[i] = rs.getString(3); pNo[i] = rs.getString(4); sOrginDeptFlag[i] = rs.getString(5); //2014.08.19 Add by KWON,HAN i++; }; } } catch (ClassNotFoundException cnfex) { cnfex.printStackTrace(); } catch (IOException ioex) { ioex.printStackTrace(); } catch (IllegalAccessException iaex) { iaex.printStackTrace(); } catch (InstantiationException istex) { istex.printStackTrace(); } catch (SQLException ex) { ex.printStackTrace(); } finally { close(rs); execClose(); } } public int deptUpdate(String id, String DeptID) throws SQLException { String sql = ""; int i = 0; try { sql = "UPDATE MOUMI_MUSER SET DEPT_ID = '" + DeptID + "' WHERE ID = '" + id + "'"; i = execUpdate(sql); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); //================================================ } catch (Exception ex) { ex.printStackTrace(); } finally { close(rs); execClose(); } return i; } /* 2014.08.20 Add by KWON,HAN */ public int deptUpdateOrign(String id, String DeptID, String orignDeptFlag) throws SQLException { String sql = ""; int i = 0; try { sql = sql + "UPDATE MOUMI_MUSER \n"; sql = sql + " SET DEPT_ID = ? \n"; if ("Y".equals(orignDeptFlag)) { sql = sql + " , ORIGN_DEPT_ID = NULL \n"; } else { sql = sql + " , ORIGN_DEPT_ID = DEPT_ID \n"; } sql = sql + " WHERE ID = ? \n"; i = execUpdate(sql.toString(), DeptID, id); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); //================================================ } catch (Exception ex) { ex.printStackTrace(); } finally { close(rs); execClose(); } return i; } /* * 2014.09.29 Add by KWON,HAN : 인사이동 후 이관여부 칼럼의 값 업데이트하기 */ public int updateModYnOfMuser(String id, String sModYn) throws SQLException { String sql = ""; int i = 0; try { sql = "UPDATE MOUMI_MUSER SET MOD_YN = '" + sModYn + "' WHERE ID = '" + id + "'"; i = execUpdate(sql); } catch (SQLException sqlEx) { sqlEx.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } finally { close(rs); execClose(); } return i; } public String[] getNameList() { return nameList; } public String[] getDeptID() { return adpInsttCode; } public String[] getUserId() { return userId; } public String[] getDeptName() { return deptName; } public String[] getPno() { return pNo; } public int getCount() { return tcount; } //2014.08.19 Add by KWON,HAN :원소속 부서 구분 /* ORIGN_DEPT_FLAG */ /* N1 : 지방공무원 (겸임) */ /* N2 : 지방공무원(파견) */ /* N3 : 교원(겸임) */ /* N4 : 교원(파견) */ /* Y : 원소속 */ /* N5 : 병설유치원 */ public String[] getOrignDeptFlag() { return sOrginDeptFlag; } }