17
votes

After upgrade Codeigniter to version 3.0 I get error DB after trying call any controller:

Error Number: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `id` = 'd1d384b0ceed0bd72fa210337acc666aab1a04e5'' at line 2

SELECT `data` WHERE `id` = 'd1d384b0ceed0bd72fa210337acc666aab1a04e5'

Filename: libraries/Session/drivers/Session_database_driver.php

When I reload page, I get that again with a other generated hash session. How to fix?

In config.php I set session store: $config['sess_driver'] = 'database';

I looked at file where is a error(line 138):

public function read($session_id)
    {
        if ($this->_get_lock($session_id) !== FALSE)
        {
            // Needed by write() to detect session_regenerate_id() calls
            $this->_session_id = $session_id;

            $this->_db
                ->select('data')
                ->from($this->_config['save_path'])
                ->where('id', $session_id);

            if ($this->_config['match_ip'])
            {
                $this->_db->where('ip_address', $_SERVER['REMOTE_ADDR']);
            }

            if (($result = $this->_db->get()->row()) === NULL) // Line 138
            {
                $this->_fingerprint = md5('');
                return '';
            }

            $this->_fingerprint = md5(rtrim($result->data));
            $this->_row_exists = TRUE;
            return $result->data;
        }

        $this->_fingerprint = md5('');
        return '';
    }
5
the correct syntax is SELECT column FROM tablename WHERE id=blah you are missing FROM tableName - Mihai
Why is your id escaped with ` but the value with ' ? - Yami
I understand, that sinta incorect, but how to fix? I downloaded this fork: github.com/bcit-ci/CodeIgniter/tree/feature/session - Danil

5 Answers

27
votes

I had this same problem and it took me a while to find the solution. On the surface, the instructions tell you to just load the session driver and everything is cool, but if you look down in the Database Driver section of the CodeIgniter documentation, you find that you need to configure the $config[‘sess_save_path’] variable in your config.php file. For example:

 $config[‘sess_save_path’] = ‘ci_sessions’

http://www.codeigniter.com/userguide3/libraries/sessions.html#initializing-a-session

Of course, you have to have the ci_sessions table set up in your database as well, but this solved the missing table name for me.

11
votes

With codeigniter 3.0 :

In config file :

$config['sess_driver'] = 'database';
$config['sess_save_path'] = 'ci_sessions';

And in your MySQL database :

CREATE TABLE IF NOT EXISTS `ci_sessions` (
    `id` varchar(40) NOT NULL,
    `ip_address` varchar(45) NOT NULL,
    `timestamp` int(10) unsigned DEFAULT 0 NOT NULL,
    `data` blob NOT NULL,
    PRIMARY KEY (id),
    KEY `ci_sessions_timestamp` (`timestamp`));

The name of the fields is different from the version 2.0

9
votes

Using Codeigniter Version 3

Edit the Config File and set session driver to "database" and sess_save_path to table name

$config['sess_driver'] = 'database';//enable session with db
$config['sess_cookie_name'] = 'ci_session';
$config['sess_expiration'] = 7200;
$config['sess_save_path'] = 'ci_sessions';//database table name
$config['sess_match_ip'] = FALSE;
$config['sess_time_to_update'] = 300;
$config['sess_regenerate_destroy'] = FALSE;

For MySql

CREATE TABLE IF NOT EXISTS `ci_sessions` (
        `id` varchar(40) NOT NULL,
        `ip_address` varchar(45) NOT NULL,
        `timestamp` int(10) unsigned DEFAULT 0 NOT NULL,
        `data` blob NOT NULL,
        KEY `ci_sessions_timestamp` (`timestamp`)
);

For PostgreSQL:

    CREATE TABLE "ci_sessions" (
            "id" varchar(40) NOT NULL,
            "ip_address" varchar(45) NOT NULL,
            "timestamp" bigint DEFAULT 0 NOT NULL,
            "data" text DEFAULT '' NOT NULL
    );

CREATE INDEX "ci_sessions_timestamp" ON "ci_sessions" ("timestamp");

Official Codeigniter Documentation for Session database driver

2
votes

I use the CI 3 and just alter:

$config['sess_save_path']           = 'ci_sessions';

Works.. =)

0
votes

SELECT 'data' FROM TABLE_NAME WHERE 'id' = 'd1d384b0ceed0bd72fa210337acc666aab1a04e5'

you are missing the tablename plus your code include special characters.