on
코드이그나이터 3.1.9 + 오라클 데이터베이스 세션
코드이그나이터 3.1.9 + 오라클 데이터베이스 세션
코드이그나이터 버전 : 3.1.9
오라클 : 12c
1. /application/config/config.php
$config['sess_driver'] = 'database';
$config['sess_save_path'] = 'CI_SESSIONS';
$config['sess_cookie_name'] = 'ci_session';
$config['sess_expiration'] = 3600 ;
$config['sess_expire_on_close'] = TRUE;
$config['sess_encrypt_cookie'] = TRUE;
$config['sess_use_database'] = TRUE;
$config['sess_table_name'] = 'CI_SESSIONS';
$config['sess_match_ip'] = TRUE;
$config['sess_match_useragent'] = TRUE;
$config['sess_time_to_update'] = 300 ;
$config['sess_regenerate_destroy'] = FALSE;
2. /application/config/database.php
);
'save_queries' => TRUE
'failover' => array(),
'stricton' => FALSE,
'compress' => FALSE,
'encrypt' => FALSE,
'swap_pre' => '',
'dbcollat' => 'utf8_general_ci',
'char_set' => 'utf8',
'cachedir' => '',
'cache_on' => FALSE,
'db_debug' => ( ENVIRONMENT !== 'production'),
'pconnect' => FALSE,
'dbprefix' => '',
'dbdriver' => 'oci8', //mysqli, oci8
'database' => '',
'password' => 'ci3',
'username' => 'ci3',
'hostname' => $tnsname,
'dsn' => '',
$db['default'] = array(
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))';
$tnsname = '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.211.55.51)(PORT = 1521))
3. 테이블 만들기
) ;
, IP_ADDRESS VARCHAR2(256 BYTE) DEFAULT '0' NOT NULL
, USER_DATA VARCHAR2(1000 BYTE)
, USER_AGENT VARCHAR2(256 BYTE) NOT NULL
, TIMESTAMP NUMBER DEFAULT 0 NOT NULL
SESSION_ID VARCHAR2(50 BYTE) DEFAULT '0' NOT NULL
(
CREATE TABLE CI_SESSIONS
ALTER TABLE CI_SESSIONS ADD CONSTRAINT CI_SESSIONS_PK PRIMARY KEY (SESSION_ID);
CREATE UNIQUE INDEX CI_SESSIONS_PK ON CI_SESSIONS (SESSION_ID ASC)
4. /system/libraries/Session/drivers/Session_database_driver.php
변경(추가)된 부분은 빨간색 으로 표시함.
-필드명과, 대소문자 주의
*오라클 사용자에게 SYS.DBMS_LOCK 권한이 주어져 있어야 함.
그렇지 않으면 다음과 같은 에러 메시지가 출력됨.
권한이 부여되었습니다.
SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO CI3;
SQLPLUS로 SYS로 로그인.
CI3사용자에게 SYS.DBMS_LOCK 권한을 주는 방법 :
Message: oci_execute(): ORA-06550: 줄 3, 열4:PLS-00201: 'SYS.DBMS_LOCK' 식별자가 정의되어야 합니다 ORA-06550: 줄 3, 열4:PL/SQL: Statement ignored ORA-06550: 줄 4, 열15:PLS-00201: 'SYS.DBMS_LOCK' 식별자가 정의되어야 합니다 ORA-06550: 줄 4, 열4:PL/SQL: Statement ignored
/**
* CodeIgniter
*
* An open source application development framework for PHP
*
* This content is released under the MIT License (MIT)
*
* Copyright (c) 2014 - 2018, British Columbia Institute of Technology
*
* Permission is hereby granted, free of charge, to any person obtaining a copy
* of this software and associated documentation files (the "Software"), to deal
* in the Software without restriction, including without limitation the rights
* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
* copies of the Software, and to permit persons to whom the Software is
* furnished to do so, subject to the following conditions:
*
* The above copyright notice and this permission notice shall be included in
* all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
* THE SOFTWARE.
*
* @package CodeIgniter
* @author EllisLab Dev Team
* @copyright Copyright (c) 2008 - 2014, EllisLab, Inc. (https://ellislab.com/)
* @copyright Copyright (c) 2014 - 2018, British Columbia Institute of Technology (http://bcit.ca/)
* @license http://opensource.org/licenses/MIT MIT License
* @link https://codeigniter.com
* @since Version 3.0.0
* @filesource
*/
defined('BASEPATH') OR exit('No direct script access allowed');
/**
* CodeIgniter Session Database Driver
*
* @package CodeIgniter
* @subpackage Libraries
* @category Sessions
* @author Andrey Andreev
* @link https://codeigniter.com/user_guide/libraries/sessions.html
*/
class CI_Session_database_driver extends CI_Session_driver implements SessionHandlerInterface {
/**
* DB object
*
* @var object
*/
protected $_db;
/**
* Row exists flag
*
* @var bool
*/
protected $_row_exists = FALSE;
/**
* Lock "driver" flag
*
* @var string
*/
protected $_platform;
// ------------------------------------------------------------------------
/**
* Class constructor
*
* @param array $params Configuration parameters
* @return void
*/
public function __construct(&$params)
{
parent::__construct($params);
$CI =& get_instance();
isset($CI->db) OR $CI->load->database();
$this->_db = $CI->db;
if ( ! $this->_db instanceof CI_DB_query_builder)
{
throw new Exception('Query Builder not enabled for the configured database. Aborting.');
}
elseif ($this->_db->pconnect)
{
throw new Exception('Configured database connection is persistent. Aborting.');
}
elseif ($this->_db->cache_on)
{
throw new Exception('Configured database connection has cache enabled. Aborting.');
}
$db_driver = $this->_db->dbdriver.(empty($this->_db->subdriver) ? '' : '_'.$this->_db->subdriver);
if (strpos($db_driver, 'mysql') !== FALSE)
{
$this->_platform = 'mysql';
}
elseif (in_array($db_driver, array('postgre', 'pdo_pgsql'), TRUE))
{
$this->_platform = 'postgre';
}
/* ellord */
elseif (strpos($db_driver,'oci8') !== FALSE)
{
$this->_platform = 'oracle';
}
// Note: BC work-around for the old 'sess_table_name' setting, should be removed in the future.
if ( ! isset($this->_config['save_path']) && ($this->_config['save_path'] = config_item('sess_table_name')))
{
log_message('debug', 'Session: "sess_save_path" is empty; using BC fallback to "sess_table_name".');
}
}
// ------------------------------------------------------------------------
/**
* Open
*
* Initializes the database connection
*
* @param string $save_path Table name
* @param string $name Session cookie name, unused
* @return bool
*/
public function open($save_path, $name)
{
if (empty($this->_db->conn_id) && ! $this->_db->db_connect())
{
return $this->_fail();
}
$this->php5_validate_id();
return $this->_success;
}
// ------------------------------------------------------------------------
/**
* Read
*
* Reads session data and acquires a lock
*
* @param string $session_id Session ID
* @return string Serialized session data
*/
public function read($session_id)
{
if ($this->_get_lock($session_id) !== FALSE)
{
// Prevent previous QB calls from messing with our queries
$this->_db->reset_query();
// Needed by write() to detect session_regenerate_id() calls
$this->_session_id = $session_id;
$this->_db
->select('USER_DATA')
->from($this->_config['save_path'])
->where('SESSION_ID', $session_id);
if ($this->_config['match_ip'])
{
$this->_db->where('IP_ADDRESS', $_SERVER['REMOTE_ADDR']);
}
if ( ! ($result = $this->_db->get()) OR ($result = $result->row()) === NULL)
{
// PHP7 will reuse the same SessionHandler object after
// ID regeneration, so we need to explicitly set this to
// FALSE instead of relying on the default ...
$this->_row_exists = FALSE;
$this->_fingerprint = md5('');
return '';
}
// PostgreSQL's variant of a BLOB datatype is Bytea, which is a
// PITA to work with, so we use base64-encoded data in a TEXT
// field instead.
if ($this->_platform === 'postgre')
{
$result=base64_decode(rtrim($result->data));
}
/* ellord */
elseif ($this->_platform === 'oracle')
{
//$result=base64_decode(rtrim($result->data->load()));
$result=base64_decode(rtrim($result->USER_DATA));
}
else {
$result=$result->data;
}
$this->_fingerprint = md5($result);
$this->_row_exists = TRUE;
return $result;
}
$this->_fingerprint = md5('');
return '';
}
// ------------------------------------------------------------------------
/**
* Write
*
* Writes (create / update) session data
*
* @param string $session_id Session ID
* @param string $session_data Serialized session data
* @return bool
*/
public function write($session_id, $session_data)
{
// Prevent previous QB calls from messing with our queries
$this->_db->reset_query();
// Was the ID regenerated?
if (isset($this->_session_id) && $session_id !== $this->_session_id)
{
if ( ! $this->_release_lock() OR ! $this->_get_lock($session_id))
{
return $this->_fail();
}
$this->_row_exists = FALSE;
$this->_session_id = $session_id;
}
elseif ($this->_lock === FALSE)
{
return $this->_fail();
}
if ($this->_row_exists === FALSE)
{
if ($this->_platform === 'oracle')
{
$dateTime = time() + 3600;
$sql = '
MERGE INTO "'.$this->_config['save_path'].'" A
USING DUAL
ON (A.SESSION_ID = :session_id)
WHEN MATCHED THEN
UPDATE SET
TIMESTAMP = :timestamp,
USER_AGENT = :user_agent,
USER_DATA = :user_data,
IP_ADDRESS = :ip_address
WHEN NOT MATCHED THEN
INSERT (SESSION_ID, TIMESTAMP, USER_AGENT, USER_DATA, IP_ADDRESS)
VALUES (:session_id, :timestamp, :user_agent, :user_data, :ip_address)
';
$enc_session_data = base64_encode($session_data);
$stmt = oci_parse($this->_db->conn_id, $sql);
oci_bind_by_name($stmt, ':session_id', $session_id, -1, SQLT_CHR);
oci_bind_by_name($stmt, ':ip_address', $_SERVER['REMOTE_ADDR'], -1, SQLT_CHR);
$time=time();
oci_bind_by_name($stmt, ':timestamp', $time, -1, SQLT_INT);
oci_bind_by_name($stmt, ':user_data', $enc_session_data, -1, SQLT_CHR);
oci_bind_by_name($stmt, ':user_agent', $_SERVER['HTTP_USER_AGENT'], -1, SQLT_CHR);
$bOk = oci_execute($stmt, OCI_DEFAULT);
if ($bOk)
{
oci_commit($this->_db->conn_id);
oci_free_statement($stmt);
$this->_fingerprint = md5($session_data);
$this->_row_exists = TRUE;
return $this->_success;
}
else
{
oci_rollback($this->_db->conn_id);
oci_free_statement($stmt);
return $this->_fail();
}
}else{
$insert_data = array(
'session_id' => $session_id,
'ip_address' => $_SERVER['REMOTE_ADDR'],
'timestamp' => time(),
'user_data' => ($this->_platform === 'postgre' ? base64_encode($session_data) : $session_data)
);
}
if ($this->_db->insert($this->_config['save_path'], $insert_data))
{
$this->_fingerprint = md5($session_data);
$this->_row_exists = TRUE;
return $this->_success;
}
return $this->_fail();
}
$this->_db->where('SESSION_ID', $session_id);
if ($this->_config['match_ip'])
{
$this->_db->where('IP_ADDRESS', $_SERVER['REMOTE_ADDR']);
}
$update_data = array('TIMESTAMP' => time());
if ($this->_fingerprint !== md5($session_data))
{
$update_data['USER_DATA'] = ($this->_platform === 'postgre' || $this->_platform === 'oracle')
? base64_encode($session_data)
: $session_data;
}
if ($this->_db->update($this->_config['save_path'], $update_data))
{
$this->_fingerprint = md5($session_data);
return $this->_success;
}
return $this->_fail();
}
// ------------------------------------------------------------------------
/**
* Close
*
* Releases locks
*
* @return bool
*/
public function close()
{
return ($this->_lock && ! $this->_release_lock())
? $this->_fail()
: $this->_success;
}
// ------------------------------------------------------------------------
/**
* Destroy
*
* Destroys the current session.
*
* @param string $session_id Session ID
* @return bool
*/
public function destroy($session_id)
{
if ($this->_lock)
{
// Prevent previous QB calls from messing with our queries
$this->_db->reset_query();
$this->_db->where('SESSION_ID', $session_id);
if ($this->_config['match_ip'])
{
$this->_db->where('IP_ADDRESS', $_SERVER['REMOTE_ADDR']);
}
if ( ! $this->_db->delete($this->_config['save_path']))
{
return $this->_fail();
}
}
if ($this->close() === $this->_success)
{
$this->_cookie_destroy();
return $this->_success;
}
return $this->_fail();
}
// ------------------------------------------------------------------------
/**
* Garbage Collector
*
* Deletes expired sessions
*
* @param int $maxlifetime Maximum lifetime of sessions
* @return bool
*/
public function gc($maxlifetime)
{
// Prevent previous QB calls from messing with our queries
$this->_db->reset_query();
return ($this->_db->delete($this->_config['save_path'], 'TIMESTAMP < '.(time() - $maxlifetime)))
? $this->_success
: $this->_fail();
}
// --------------------------------------------------------------------
/**
* Validate ID
*
* Checks whether a session ID record exists server-side,
* to enforce session.use_strict_mode.
*
* @param string $id
* @return bool
*/
public function validateId($id)
{
// Prevent previous QB calls from messing with our queries
$this->_db->reset_query();
$this->_db->select('1')->from($this->_config['save_path'])->where('SESSION_ID', $id);
empty($this->_config['match_ip']) OR $this->_db->where('IP_ADDRESS', $_SERVER['REMOTE_ADDR']);
$result = $this->_db->get();
empty($result) OR $result = $result->row();
return ! empty($result);
}
// ------------------------------------------------------------------------
/**
* Get lock
*
* Acquires a lock, depending on the underlying platform.
*
* @param string $session_id Session ID
* @return bool
*/
protected function _get_lock($session_id)
{
if ($this->_platform === 'mysql')
{
$arg = md5($session_id.($this->_config['match_ip'] ? '_'.$_SERVER['REMOTE_ADDR'] : ''));
if ($this->_db->query("SELECT GET_LOCK('".$arg."', 300) AS ci_session_lock")->row()->ci_session_lock)
{
$this->_lock = $arg;
return TRUE;
}
return FALSE;
}
elseif ($this->_platform === 'postgre')
{
$arg = "hashtext('".$session_id."')".($this->_config['match_ip'] ? ", hashtext('".$_SERVER['REMOTE_ADDR']."')" : '');
if ($this->_db->simple_query('SELECT pg_advisory_lock('.$arg.')'))
{
$this->_lock = $arg;
return TRUE;
}
return FALSE;
}
/* ellord */
elseif ($this->_platform === 'oracle')
{
$lockname=$session_id.($this->_config['match_ip'] ? '_'.$_SERVER['REMOTE_ADDR'] : '');
$lockid='';
$result=99;
$sql='
begin
SYS.DBMS_LOCK.ALLOCATE_UNIQUE (:lockname, :lockid);
:result := SYS.DBMS_LOCK.REQUEST (:lockid, DBMS_LOCK.X_MODE, 300, FALSE);
end;';
$stmt = oci_parse($this->_db->conn_id,$sql);
oci_bind_by_name($stmt,':lockname',$lockname,-1,SQLT_CHR);
oci_bind_by_name($stmt,':lockid',$lockid,256,SQLT_CHR);
oci_bind_by_name($stmt,':result',$result,2,SQLT_INT);
$bOk=oci_execute($stmt);
/*echo 'getlock ';
var_dump(array($result,$lockid)); */
if ($bOk && $result==0)
{
$this->_lock = $lockid;
return TRUE;
}
return FALSE;
}
return parent::_get_lock($session_id);
}
// ------------------------------------------------------------------------
/**
* Release lock
*
* Releases a previously acquired lock
*
* @return bool
*/
protected function _release_lock()
{
if ( ! $this->_lock)
{
return TRUE;
}
if ($this->_platform === 'mysql')
{
if ($this->_db->query("SELECT RELEASE_LOCK('".$this->_lock."') AS ci_session_lock")->row()->ci_session_lock)
{
$this->_lock = FALSE;
return TRUE;
}
return FALSE;
}
elseif ($this->_platform === 'postgre')
{
if ($this->_db->simple_query('SELECT pg_advisory_unlock('.$this->_lock.')'))
{
$this->_lock = FALSE;
return TRUE;
}
return FALSE;
}
/* ellord */
elseif ($this->_platform === 'oracle')
{
$sql='
begin
:result := SYS.DBMS_LOCK.RELEASE (:lockid);
end;';
$stmt = oci_parse($this->_db->conn_id,$sql);
$result=99;
oci_bind_by_name($stmt,':lockid',$this->_lock,256,SQLT_CHR);
oci_bind_by_name($stmt,':result',$result,2,SQLT_INT);
$bOk=oci_execute($stmt);
/*echo 'releaselock ';
var_dump(array($result,$this->_lock));*/
if ($bOk && $result==0)
{
$this->_lock = FALSE;
return TRUE;
}
return FALSE;
}
return parent::_release_lock();
}
}
from http://ellordnet.tistory.com/198 by ccl(A) rewrite - 2020-04-02 14:01:27