CodeIgniter Forums
ci_sessions and ion auth not working with sqlite - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: ci_sessions and ion auth not working with sqlite (/showthread.php?tid=66088)



ci_sessions and ion auth not working with sqlite - SirTom - 09-04-2016

I try to configure ci_sessions and ion auth for sqlite, but it will not work.

My config.php für the session data
Code:
$config['sess_driver'] = 'database';
$config['sess_cookie_name'] = 'ci_sessions';
$config['sess_expiration'] = 7200;
$config['sess_save_path'] = NULL;
$config['sess_match_ip'] = FALSE;
$config['sess_time_to_update'] = 300;
$config['sess_regenerate_destroy'] = FALSE;

My database-code
Code:
$db['default'] = array(
    'dsn'    => '',
    'hostname' => '',
    'username' => '',
    'password' => '',
    'database' => APPPATH.'/db/data.db',
    'dbdriver' => 'sqlite3',
    'dbprefix' => '',
    'pconnect' => FALSE,
    'db_debug' => (ENVIRONMENT !== 'production'),
    'cache_on' => FALSE,
    'cachedir' => '',
    'char_set' => 'utf8',
    'dbcollat' => 'utf8_unicode_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE
);

My sql-code for the sessions-table in sqlite
Code:
CREATE TABLE `ci_sessions` (
    `id`    TEXT NOT NULL,
    `ip_address`    TEXT NOT NULL,
    `timestamp`    INTEGER NOT NULL DEFAULT 0,
    `data`    BLOB NOT NULL,
    PRIMARY KEY(timestamp)
);
CREATE INDEX `ci_sessions_timestamp`  ON `ci_sessions` (`timestamp`);


My sql-code for ion auth in sqlite
Code:
CREATE TABLE `groups` (
 `id` integer NOT NULL,
 `name` varchar(20) NOT NULL,
 `description` varchar(100) NOT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `groups` (`id`, `name`, `description`) VALUES(1,'admin','Administrator');
INSERT INTO `groups` (`id`, `name`, `description`) VALUES(2,'members','General User');

CREATE TABLE `users` (
 `id` integer NOT NULL,
 `ip_address` varbinary(16) NOT NULL,
 `username` varchar(100) NOT NULL,
 `password` varchar(80) NOT NULL,
 `salt` varchar(40) DEFAULT NULL,
 `email` varchar(100) NOT NULL,
 `activation_code` varchar(40) DEFAULT NULL,
 `forgotten_password_code` varchar(40) DEFAULT NULL,
 `forgotten_password_time` integer DEFAULT NULL,
 `remember_code` varchar(40) DEFAULT NULL,
 `created_on` integer NOT NULL,
 `last_login` integer DEFAULT NULL,
 `active` integer DEFAULT NULL,
 `first_name` varchar(50) DEFAULT NULL,
 `last_name` varchar(50) DEFAULT NULL,
 `company` varchar(100) DEFAULT NULL,
 `phone` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
);

INSERT INTO `users` (`id`, `ip_address`, `username`, `password`, `salt`, `email`, `activation_code`, `forgotten_password_code`, `created_on`, `last_login`, `active`, `first_name`, `last_name`, `company`, `phone`) VALUES     ('1','0x7f000001','administrator','59beecdf7fc966e2f17fd8f65a4a9aeb09d4a3d4','9462e8eee0','[email protected]','',NULL,'1268889823','1268889823','1', 'Admin','istrator','ADMIN','0');

CREATE TABLE `users_groups` (
 `id` integer NOT NULL,
 `user_id` integer NOT NULL,
 `group_id` integer NOT NULL,
 PRIMARY KEY (`id`)
);

CREATE INDEX `fk_users_groups_users1_idx`  ON `users_groups`(`user_id`);
CREATE INDEX `fk_users_groups_groups1_idx` ON `users_groups`(`group_id`);

INSERT INTO `users_groups` (`id`, `user_id`, `group_id`) VALUES (1,1,1);
INSERT INTO `users_groups` (`id`, `user_id`, `group_id`) VALUES (2,1,2);

CREATE TABLE `login_attempts` (
 `id` integer NOT NULL,
 `ip_address` varbinary(16) NOT NULL,
 `login` varchar(100) NOT NULL,
 `time` integer DEFAULT NULL,
 PRIMARY KEY (`id`)
);




The first error is this one and nothing will insert into the ci_sessions table:
Code:
A PHP Error was encountered
Severity: Warning
Message: SQLite3::exec(): near "(": syntax error
Filename: sqlite3/sqlite3_driver.php
Line Number: 128
Backtrace:

And

CI Database Error Occurred
Error Number: 0
not an error
INSERT INTO ("id", "ip_address", "timestamp", "data") VALUES ('6bb6b147696bea36cdec329786e332402edd32db', '::1', 1472997259, '__ci_last_regenerate|i:1472997259;')
Filename: C:/xampp/htdocs/timetable/system/database/DB_driver.php
Line Number: 691

The Problem with ion out is, that I can not login with admin@admin.com / password. I will always be redirect to login.

I hope, someone can help me.

Thanks, Tom


RE: ci_sessions and ion auth not working with sqlite - arma7x - 09-04-2016

Using database as session storage only for mysql and postgres


RE: ci_sessions and ion auth not working with sqlite - InsiteFX - 09-04-2016

The sess_save_path has to be the database table name when using database sessions.

PHP Code:
$config['sess_driver'] = 'database';
$config['sess_save_path'] = 'ci_sessions'