기본 콘텐츠로 건너뛰기

코드이그나이터 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-03-07 10:55:41

댓글

댓글 쓰기

이 블로그의 인기 게시물

[PHP] 코드이그니터 - 파일업로드 구현

[PHP] 코드이그니터 - 파일업로드 구현 파일 업로드 이번에 PHP 프레임워크인 코드 이그니터(Codeigniter)를 사용하여 홈페이지를 만드는데 사용한 이미지 업로드용 코드 입니다. upload 라이브러리를 사용하고 app~ 와 같은 위치에 upload 폴더를 만드고 다음 코드를 사용한다음 ajax 로 호출하여 파일을 업로드 합니다. function index() { // Upload 설정 $config['upload_path'] = './upload/'; $config[\'allowed_types\'] = 'gif|jpg|png'; $config['max_size'] = 100; // 100k $config['max_width'] = 1024; $config['max_height'] = 768; $this->load->library('upload', $config); $data = array(); if (! $this->upload->do_upload("service_image")) { $error = array('error' => $this->upload->display_errors()); } else { //$data = array('upload_data' => $this->upload->data()); $this->output->set_output("./upload/".$this->upload->data('file_name')); } } jquery 를 이용한 파일 업로드 호출 코드 function upload() { var datas, xhr; datas = new FormData(); datas.append( 'service_image', $( ...
댓글 쓰기
자세한 내용 보기

이클립스 코드이그나이터 연동 ( eclipse codeigniter )

이클립스 코드이그나이터 연동 ( eclipse codeigniter ) https://ellislab.com/codeigniter/user-guide/installation/downloads.html 위의 사이트에서 코드이그나이터를 다운 받는다. 다운받은 압축 파일을 풀어 준다. 이클립스에서 php 프로젝트를 생성한 공간에 코드이그나이터 압축파일을 복사 붙여넣기 해준다. 위와 같은 화면이 나오면 정상적으로 연동이 된 것 입니다. from http://nahosung.tistory.com/22 by ccl(A) rewrite - 2020-03-06 16:20:55
댓글 쓰기
자세한 내용 보기

MariaDB 에서 access denied for user 'root' 문제

MariaDB 에서 access denied for user 'root' 문제 heidisql 등의 원격 접속 툴을 이용해도 접속이 안 됐다. 포트, 방화벽 설정 등등 모두 확인해 봤고 로컬에서 잘 돌아가는 데도 원격 접속이 안됐다. 사실 원격 접속만 안 되면 상관 없는데, codeigniter에서도 똑같이 로그인을 못해서 자꾸 에러가 났었다. 일단, MariaDB는 10.4부터 root 권한 소유자에게 따로 비밀번호를 물어보지 않는다. 즉, 루트 계정으로 로그인했으면 그냥 콘솔에 mysql 치면 접속이 된다. 물론 다른 유저는 전처럼 비밀번호를 물어본다. 나는 이 root 계정으로 로그인을 며칠째 계속 시도해 봤는데 계속 안됐다.... 포트 문제도 아니었고 딱히 통신 문제도 아니었다. 심지어 SSH 접속 계정도 root 였다. 해답은 새로운 계정을 파서, 그 계정으로 접속을 하니까 잘 됐다. 계정 만드는 SQL 문이야 검색하면 바로 나올 거니까 따로 적진 않겠다.사실 별 내용 없는 건 아는데 새 계정을 파서 시도하란 말을 며칠 내내 구글을 뒤진 다음에야 본 것 같아서, 혹시 같은 어려움을 겪는 사람들이 있다면 빨리 해결할 수 있었으면 좋겠다. from http://skyseven73.tistory.com/5 by ccl(A) rewrite - 2020-03-11 02:54:31
댓글 쓰기
자세한 내용 보기