코드이그나이터 버전 : 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

$tnsname = '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.211.55.51)(PORT = 1521))

        (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))';

 

 

$db['default'] = array(

    'dsn' => '',

    'hostname' => $tnsname,

    'username' => 'ci3',

    'password' => 'ci3',

    'database' => '',

    'dbdriver' => 'oci8',    //mysqli, oci8

    'dbprefix' => '',

    'pconnect' => FALSE,

    'db_debug' => (ENVIRONMENT !== 'production'),

    'cache_on' => FALSE,

    'cachedir' => '',

    'char_set' => 'utf8',

    'dbcollat' => 'utf8_general_ci',

    'swap_pre' => '',

    'encrypt' => FALSE,

    'compress' => FALSE,

    'stricton' => FALSE,

    'failover' => array(),

    'save_queries' => TRUE

);



3. 테이블 만들기

CREATE TABLE CI_SESSIONS 

(

  SESSION_ID VARCHAR2(50 BYTE) DEFAULT '0' NOT NULL 

, TIMESTAMP NUMBER DEFAULT 0 NOT NULL 

, USER_AGENT VARCHAR2(256 BYTE) NOT NULL 

, USER_DATA VARCHAR2(1000 BYTE) 

, IP_ADDRESS VARCHAR2(256 BYTE) DEFAULT '0' NOT NULL 

) ;


CREATE UNIQUE INDEX CI_SESSIONS_PK ON CI_SESSIONS (SESSION_ID ASC

 

ALTER TABLE CI_SESSIONS ADD CONSTRAINT CI_SESSIONS_PK PRIMARY KEY (SESSION_ID);




4. /system/libraries/Session/drivers/Session_database_driver.php
변경(추가)된 부분은 빨간색으로 표시함.
-필드명과, 대소문자 주의

*오라클 사용자에게 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

 

 

 

CI3사용자에게 SYS.DBMS_LOCK 권한을 주는 방법 : 

SQLPLUS로  SYS로 로그인.

 

SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO CI3;

권한이 부여되었습니다.

 

 

 

 






<?php

/**

 * 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();

}

}







7e2431ed98c6d12fad6c84c8d60026f7_1534677020_9655.png




5. 참고

* 세션 데이터가 중복하여 생성되거나 새로고침 할 때마다 생기는 경우

3.0.x  버전에서 생기는 버그이므로 3.1.9 최신버전으로 업데이트 하면 된다.


'PHP' 카테고리의 다른 글

zendframework 3 설치  (0) 2019.02.28
PHPMailer 사용 SMTP  (0) 2019.01.08
PHP7 session_regenerate_id() 대체  (0) 2019.01.08
IIS PHP 헤더설정  (0) 2019.01.08
단방향 암호화 문자열 crypt sha512 암호화  (0) 2019.01.08
블로그 이미지

엘로드넷

,