/** * Set up model. Calls base class Setup and adds encryption support * for ssn. */ public function setUp() { parent::setup(); if (KeyHandler::keyExists()) { $key = KeyHandler::readKey(); $this->addListener(new EncryptionListener('ssn', $key)); } }
public function testPrepareEncryptFields() { $key = KeyHandler::readKey(); $values[] = '123456'; $values[] = 'abcd'; $values[] = 'pqr'; $fields[] = 'emp_ssn_num'; $fields[] = 'ebsal_basic_salary'; $fields[] = 'hs_hr_emp_language'; $expected[] = "AES_ENCRYPT(123456, '{$key}')"; $expected[] = "AES_ENCRYPT(abcd, '{$key}')"; $expected[] = "pqr"; $result = CryptoQuery::prepareEncryptFields($fields, $values); $this->assertEquals($expected, $result); $fields = null; $values = null; $expected = null; $fields[] = 'EMP_NUMBER'; $fields[] = 'EMP_SMOKER'; $fields[] = 'ETHNIC_RACE_CODE'; $fields[] = 'EMP_BIRTHDAY'; $fields[] = 'NATION_CODE'; $fields[] = 'EMP_GENDER'; $fields[] = 'EMP_MARITAL_STATUS'; $fields[] = 'EMP_SSN_NUM'; $fields[] = 'EMP_SIN_NUM'; $fields[] = 'EMP_OTHER_ID'; $fields[] = 'EMP_DRI_LICE_NUM'; $fields[] = 'EMP_DRI_LICE_EXP_DATE'; $fields[] = 'EMP_MILITARY_SERVICE'; $values[] = '001'; $values[] = '0'; $values[] = null; $values[] = '0000-00-00'; $values[] = null; $values[] = '1'; $values[] = '0'; $values[] = '125'; $values[] = '123'; $values[] = ''; $values[] = ''; $values[] = '0000-00-00'; $values[] = ''; foreach ($values as $value) { if ($value == null) { $expected[] = null; } elseif ($value == 125) { $expected[] = "AES_ENCRYPT({$value}, '{$key}')"; } else { $expected[] = $value; } } $result = CryptoQuery::prepareEncryptFields($fields, $values); $this->assertEquals($expected, $result); }
/** * Configure doctrine connections to use tablename prefix hs_hr_ */ public function configureDoctrine(Doctrine_Manager $manager) { $manager->setAttribute(Doctrine_Core::ATTR_VALIDATE, Doctrine_Core::VALIDATE_ALL); $manager->setAttribute(Doctrine_Core::ATTR_USE_NATIVE_ENUM, true); // // If using encryption, enable dql callbacks. Needed by EncryptionListener // if (KeyHandler::keyExists()) { $manager->setAttribute(Doctrine_Core::ATTR_USE_DQL_CALLBACKS, true); } //$manager->setAttribute(Doctrine::ATTR_TBLNAME_FORMAT, 'hs_hr_%s'); }
public static function readKey() { if (!self::keyExists()) { throw new KeyHandlerException('Key file does not exist', KeyHandlerException::KEY_DOES_NOT_EXIST); } if (!is_readable(ROOT_PATH . self::$filePath)) { throw new KeyHandlerException('Key is not readable', KeyHandlerException::KEY_NOT_READABLE); } if (!self::$keySet) { self::$key = trim(file_get_contents(ROOT_PATH . self::$filePath)); self::$keySet = true; } return self::$key; }
/** * Initilizes cryptographic scheme */ private static function init() { if (is_null(self::$cryptographicScheme)) { $key = KeyHandler::readKey(); $mysqlKey = ""; for ($a = 0; $a < strlen($key); $a++) { $mysqlKey[$a % 16] = chr(ord($mysqlKey[$a % 16]) ^ ord($key[$a])); } $aes = new Crypt_Rijndael(CRYPT_RIJNDAEL_MODE_ECB); $aes->setKeyLength(128); $aes->setBlockLength(128); $aes->setKey($mysqlKey); self::$cryptographicScheme = $aes; } }
/** * Sets up the fixture, for example, open a network connection. * This method is called before a test is executed. * * @access protected */ protected function setUp() { $this->exportObj = new MilleniumPayrollExport(); $conf = new Conf(); $this->connection = mysql_connect($conf->dbhost . ":" . $conf->dbport, $conf->dbuser, $conf->dbpass); mysql_select_db($conf->dbname); $this->_truncateTables(); $sql = "INSERT INTO hs_hr_employee" . "(emp_number, employee_id, emp_lastname, emp_firstname, emp_middle_name, " . "emp_nick_name, emp_smoker, ethnic_race_code, emp_birthday, nation_code, " . "emp_gender, emp_marital_status, emp_ssn_num, emp_sin_num, emp_other_id, " . "emp_dri_lice_num, emp_dri_lice_exp_date, emp_military_service, emp_status, " . "job_title_code, eeo_cat_code, work_station, " . "emp_street1, emp_street2, city_code, coun_code, provin_code, emp_zipcode, " . "emp_hm_telephone, emp_mobile, emp_work_telephone, emp_work_email, " . "sal_grd_code, joined_date,\temp_oth_email) VALUES (" . "'10', 'E1921A', 'Karunadasa', 'Kamal', 'K', " . "NULL, NULL, NULL, '1974-11-20', NULL, " . "1, NULL, '987654320', '', '', " . "null, NULL, NULL, 'EST001', " . "NULL, NULL, NULL, " . "'111 Main Street', 'SUITE A29', 'Houston', 'US', 'TX', '77845', " . "'', '', '', NULL, " . "NULL, '1997-12-11', NULL)"; if (KeyHandler::keyExists()) { $key = KeyHandler::readKey(); $sql = str_replace("'987654320'", "AES_ENCRYPT('987654320', '{$key}')", $sql); } $this->assertTrue(mysql_query($sql), mysql_error()); $sql = "INSERT into hs_hr_emp_us_tax(emp_number, tax_federal_status, tax_federal_exceptions, " . "tax_state, tax_state_status, tax_state_exceptions, tax_unemp_state,tax_work_state) VALUES (" . "10, 'NRA', 2, 'MD', 'NA', 3, 'VA', 'AZ')"; $this->assertTrue(mysql_query($sql), mysql_error()); }
/** * Configure doctrine connections to use tablename prefix hs_hr_ */ public function configureDoctrine(Doctrine_Manager $manager) { $manager->setAttribute(Doctrine_Core::ATTR_VALIDATE, Doctrine_Core::VALIDATE_ALL); $manager->setAttribute(Doctrine_Core::ATTR_USE_NATIVE_ENUM, true); $manager->setAttribute(Doctrine_Core::ATTR_QUERY_CLASS, 'ohrmDoctrineQuery'); // // If using encryption, enable dql callbacks. Needed by EncryptionListener // if (KeyHandler::keyExists()) { $manager->setAttribute(Doctrine_Core::ATTR_USE_DQL_CALLBACKS, true); } //$manager->setAttribute(Doctrine::ATTR_TBLNAME_FORMAT, 'hs_hr_%s'); // Allow running doctrine:build-schema without error $isCli = php_sapi_name() == "cli"; if (true == $isCli) { Doctrine_Manager::getInstance()->setAttribute(Doctrine::ATTR_AUTO_ACCESSOR_OVERRIDE, false); } }
public static function prepareEncryptFields($encryptFieldsArray, $encryptValuesArray) { $encOn = KeyHandler::KeyExists(); $valuesArray = array(); $encryptFieldsArrayCount = count($encryptFieldsArray); for ($i = 0; $i < $encryptFieldsArrayCount; $i++) { if ($encOn && self::isEncField($encryptFieldsArray[$i])) { $key = KeyHandler::readKey(); if ($encryptValuesArray[$i] == null) { $valuesArray[$i] = null; } else { $valuesArray[$i] = "AES_ENCRYPT({$encryptValuesArray[$i]}, '{$key}')"; } } else { $valuesArray[$i] = $encryptValuesArray[$i]; } } return $valuesArray; }
/** * Get CSV data as string * * @return string formatted csv data */ public function getCSVData() { $sql = "SELECT hs_hr_employee.emp_number, employee_id, emp_lastname, emp_firstname, emp_middle_name, emp_street1, emp_street2," . "city_code,provin_code,emp_zipcode,emp_gender,emp_birthday,emp_ssn_num,emp_status,joined_date, " . "tax_federal_status, tax_federal_exceptions, tax_state, tax_state_status, tax_state_exceptions, " . "tax_unemp_state,tax_work_state,custom1,custom2,custom3,custom4,custom5,custom6,custom7,custom8,custom9,custom10, " . " pay.payperiod_code,sal.ebsal_basic_salary,loc.loc_name,comp.title " . " FROM hs_hr_employee " . " LEFT JOIN hs_hr_emp_us_tax tax on (tax.emp_number = hs_hr_employee.emp_number) " . " LEFT JOIN hs_hr_emp_basicsalary sal on (hs_hr_employee.emp_number = sal.emp_number) " . " LEFT JOIN hs_hr_payperiod pay on (sal.payperiod_code = pay.payperiod_code) " . " LEFT JOIN hs_hr_compstructtree comp on (hs_hr_employee.work_station = comp.id) " . " LEFT JOIN hs_hr_location loc on (comp.loc_code = loc.loc_code) "; if (KeyHandler::keyExists()) { $key = KeyHandler::readKey(); $sql = str_replace("emp_ssn_num", "IF(`emp_ssn_num` IS NOT NULL, AES_DECRYPT(emp_ssn_num, '{$key}'), '') AS `emp_ssn_num`", $sql); $sql = str_replace("sal.ebsal_basic_salary", "IF(`ebsal_basic_salary` IS NOT NULL, AES_DECRYPT(ebsal_basic_salary, '{$key}'), '') AS `ebsal_basic_salary`", $sql); } $conn = new DMLFunctions(); $result = $conn->executeQuery($sql); $csv = ""; if ($result === false) { throw new Exception("Error in query: " . $sql); } while ($row = mysql_fetch_assoc($result)) { $csv .= $this->_getCSVRow($row) . "\n"; } return $csv; }
/** * Sets up the fixture, for example, open a network connection. * This method is called before a test is executed. * * @access protected */ protected function setUp() { $conf = new Conf(); $this->connection = mysql_connect($conf->dbhost . ":" . $conf->dbport, $conf->dbuser, $conf->dbpass); mysql_select_db($conf->dbname); $this->_truncateTables(); // insert some test data $this->_runQuery("INSERT INTO hs_hr_custom_export(export_id, name, fields, headings) VALUES (1, 'Export 1', 'empId,lastName,firstName,middleName,street1,street2,city', '')"); $this->_runQuery("INSERT INTO hs_hr_custom_export(export_id, name, fields, headings) VALUES (2, 'Export 2', 'empId,lastName,firstName,city', 'Employee Id,Last Name,First Name,City')"); $this->_runQuery("INSERT INTO hs_hr_custom_export(export_id, name, fields, headings) VALUES (3, 'Export 3', 'empId,street1,street2,city', 'Employee Id,Address1, Address2, City')"); // insert some employee data $sql = "INSERT INTO hs_hr_employee" . "(emp_number, employee_id, emp_lastname, emp_firstname, emp_middle_name, " . "emp_nick_name, emp_smoker, ethnic_race_code, emp_birthday, nation_code, " . "emp_gender, emp_marital_status, emp_ssn_num, emp_sin_num, emp_other_id, " . "emp_dri_lice_num, emp_dri_lice_exp_date, emp_military_service, emp_status, " . "job_title_code, eeo_cat_code, work_station, " . "emp_street1, emp_street2, city_code, coun_code, provin_code, emp_zipcode, " . "emp_hm_telephone, emp_mobile, emp_work_telephone, emp_work_email, " . "sal_grd_code, joined_date,\temp_oth_email, " . "custom1, custom2, custom3, custom4, custom5, " . "custom6, custom7, custom8, custom9, custom10) VALUES (" . "'10', 'E1921A', 'Karunadasa', 'Kamal', 'K', " . "NULL, NULL, NULL, '1974-11-20', NULL, " . "1, NULL, '987654320', '', '', " . "null, NULL, NULL, 'EST001', " . "NULL, NULL, NULL, " . "'111 Main Street', 'SUITE A29', 'Houston', 'US', 'TX', '77845', " . "'', '', '', NULL, " . "NULL, '1997-12-11', NULL, " . "'c1', 'c2', 'c3', 'c4', 'c5'," . "'c6', 'c7', 'c8', 'c9', 'c10'" . ")"; if (KeyHandler::keyExists()) { $key = KeyHandler::readKey(); $sql = str_replace("'987654320'", "AES_ENCRYPT('987654320', '{$key}')", $sql); } $this->_runQuery($sql); $sql = "INSERT into hs_hr_emp_us_tax(emp_number, tax_federal_status, tax_federal_exceptions, " . "tax_state, tax_state_status, tax_state_exceptions, tax_unemp_state,tax_work_state) VALUES (" . "10, 'NRA', 2, 'MD', 'NA', 3, 'VA', 'AZ')"; $this->_runQuery($sql); // Add direct debit information $dd = new EmpDirectDebit(); $dd->setEmpNumber(10); $dd->setRoutingNumber(11111); $dd->setAccount('AC 1'); $dd->setAmount(121); $dd->setAccountType('CHECKING'); $dd->setTransactionType('BLANK'); $this->assertTrue($dd->add(), mysql_error()); $dd = new EmpDirectDebit(); $dd->setEmpNumber(10); $dd->setRoutingNumber(22222); $dd->setAccount('AC #2'); $dd->setAmount(23); $dd->setAccountType('SAVINGS'); $dd->setTransactionType('FLATMINUS'); $this->assertTrue($dd->add(), mysql_error()); }
public function constructSelectClauseForListGroup($selectStatement, $displayFieldGroup, $displayFields) { $fieldList = ''; $isEncryptEnabled = KeyHandler::keyExists(); foreach ($displayFields as $field) { $fieldName = $field->getName(); if ($isEncryptEnabled && $field->getIsEncrypted()) { $fieldName = 'AES_DECRYPT(UNHEX(' . $fieldName . '),"' . KeyHandler::readKey() . '")'; } // If null, change to empty string since CONCAT_WS will skip nulls, causing problems with the field list order. $fieldName = 'IFNULL(' . $fieldName . ",'')"; if (empty($fieldList)) { $fieldList = $fieldName; } else { $fieldList .= ',' . $fieldName; } } $alias = "DisplayFieldGroup" . $displayFieldGroup->getId(); $clause = "CONCAT_WS('|^^|', " . $fieldList . ")"; $clause = "GROUP_CONCAT(DISTINCT " . $clause . " SEPARATOR '|\\n|' ) AS " . $alias; if (empty($selectStatement)) { $selectStatement = $clause; } else { $selectStatement .= ',' . $clause; } return $selectStatement; }
/** * Get CSV Export Data * * @return DaoException */ public function getCSVExportData() { $csv = array(); try { $sql = "SELECT hs_hr_employee.emp_number, employee_id, emp_lastname, emp_firstname, emp_middle_name, emp_street1, emp_street2," . "city_code,provin_code,emp_zipcode,emp_gender,emp_birthday,emp_ssn_num,emp_status,joined_date, " . "tax_federal_status, tax_federal_exceptions, tax_state, tax_state_status, tax_state_exceptions, " . "tax_unemp_state,tax_work_state,custom1,custom2,custom3,custom4,custom5,custom6,custom7,custom8,custom9,custom10, " . " pay.payperiod_code,sal.ebsal_basic_salary,loc.loc_name,comp.title as workstation" . " FROM hs_hr_employee " . " LEFT JOIN hs_hr_emp_us_tax tax on (tax.emp_number = hs_hr_employee.emp_number) " . " LEFT JOIN hs_hr_emp_basicsalary sal on (hs_hr_employee.emp_number = sal.emp_number) " . " LEFT JOIN hs_hr_payperiod pay on (sal.payperiod_code = pay.payperiod_code) " . " LEFT JOIN hs_hr_compstructtree comp on (hs_hr_employee.work_station = comp.id) " . " LEFT JOIN hs_hr_location loc on (comp.loc_code = loc.loc_code) "; if (KeyHandler::keyExists()) { $key = KeyHandler::readKey(); $sql = str_replace("emp_ssn_num", "IF(`emp_ssn_num` IS NOT NULL, AES_DECRYPT(emp_ssn_num, '{$key}'), '') AS `emp_ssn_num`", $sql); $sql = str_replace("sal.ebsal_basic_salary", "IF(`ebsal_basic_salary` IS NOT NULL, AES_DECRYPT(ebsal_basic_salary, '{$key}'), '') AS `ebsal_basic_salary`", $sql); } // // Direct query using PDO connection // $dbh = Doctrine_Manager::connection()->getDbh(); $result = $dbh->query($sql); if ($result) { $csv = $result->fetchAll(); } return $csv; } catch (Exception $e) { throw new DaoException($e->getMessage()); } }
public function testConstructSelectClauseForDisplayField() { $displayField = new DisplayField(); $fieldName = 'Acme'; $displayField->setName($fieldName); $options = array(array('is_value_list' => true, 'is_encrypted' => true, 'field_alias' => 'Abcd'), array('is_value_list' => true, 'is_encrypted' => true, 'field_alias' => null), array('is_value_list' => true, 'is_encrypted' => false, 'field_alias' => 'Abcd'), array('is_value_list' => true, 'is_encrypted' => false, 'field_alias' => null), array('is_value_list' => false, 'is_encrypted' => false, 'field_alias' => 'Abcd'), array('is_value_list' => false, 'is_encrypted' => false, 'field_alias' => null), array('is_value_list' => false, 'is_encrypted' => true, 'field_alias' => 'Abcd'), array('is_value_list' => false, 'is_encrypted' => true, 'field_alias' => null)); $encrypt = KeyHandler::keyExists(); if ($encrypt) { $key = KeyHandler::readKey(); } foreach ($options as $option) { $displayField = new DisplayField(); $displayField->setName($fieldName); $displayField->setIsValueList($option['is_value_list']); $displayField->setIsEncrypted($option['is_encrypted']); $displayField->setFieldAlias($option['field_alias']); $expected = $fieldName; if ($encrypt && $option['is_encrypted']) { $expected = 'AES_DECRYPT(UNHEX(' . $fieldName . '),"' . $key . '")'; } if ($option['is_value_list']) { $expected = 'GROUP_CONCAT(DISTINCT ' . $expected . " SEPARATOR '|" . '\\n' . "|' ) "; } if ($option['field_alias']) { $expected = $expected . ' AS ' . $option['field_alias']; } $selectStatement = null; $selectStatement = $this->reportGeneratorService->constructSelectClauseForDisplayField($selectStatement, $displayField); $this->assertEquals($expected, $selectStatement); $selectStatement = "x"; $expected = 'x,' . $expected; $selectStatement = $this->reportGeneratorService->constructSelectClauseForDisplayField($selectStatement, $displayField); $this->assertEquals($expected, $selectStatement); } }
function simpleSelect($selectTable, $selectFields, $selectConditions = null, $selectOrderBy = null, $selectOrder = null, $selectLimit = null) { /* For Encryption : Begins */ $encOn = KeyHandler::KeyExists(); if ($encOn && CryptoQuery::isEncTable($this->table_name)) { $selectFields = CryptoQuery::prepareDecryptFields($selectFields); } /* For Encryption : Ends */ $query = $this->_buildSelect($selectFields) . " FROM {$selectTable} "; if (isset($selectConditions)) { $query .= $this->_buildWhere($selectConditions); } if (isset($selectOrderBy)) { $query .= " ORDER BY {$selectOrderBy} {$selectOrder}"; } if (isset($selectLimit)) { $query .= " LIMIT {$selectLimit}"; } return $query; }
/** * @todo Implement testDeleteKey(). */ public function testDeleteKey() { // When key is not available try { KeyHandler::deleteKey(); } catch (Exception $e) { $this->assertEquals(KeyHandlerException::KEY_DOES_NOT_EXIST, $e->getCode()); } // When key is existing $filePath = ROOT_PATH . '/lib/confs/cryptokeys/key.ohrm'; $this->assertTrue(KeyHandler::createKey()); $this->assertTrue(KeyHandler::deleteKey()); $this->assertFalse(file_exists($filePath)); // When key is existing, but cannot be deleted $this->assertTrue(KeyHandler::createKey()); system("chmod 000 {$filePath}"); try { KeyHandler::deleteKey(); } catch (Exception $e) { $this->assertEquals(KeyHandlerException::KEY_DELETION_FAILIURE, $e->getCode()); } }