How do I Fix Codeigniter Database Error in SQL

I still remember the first time I hit Log In on my brand-new CodeIgniter app and, instead of a warm dashboard, MySQL slapped me with this:

Database Error

INTO (`session_id`, `ip_address`, `user_agent`, `last_activity`)
VALUES ('826f937fed9185e2c121ec06b1b44b6a', '::1',
        'Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/53', 1442454002);
-- A Database Error Occurred
-- Error Number: 1064

Nothing kills excitement faster than a MySQL 1064. In this post I’ll walk through:

• What error 1064 really means
• Why CodeIgniter built a broken query in the first place
• How I fixed it (and how you can too)
• A clean, modern login flow you can drop into any CI project
• Extra “practice” features to harden the login against real-world abuse

Throughout, I’ll keep the code minimal and the language plain no fancy buzzwords, just working bits.

What MySQL 1064

Error 1064 is MySQL’s polite way of saying “your SQL is broken.”
It fires whenever a query violates MySQL grammar: a missing keyword, a stray comma, or like in my case a missing table name right after INSERT INTO.

If MySQL can’t parse the query, it won’t even try to run it. Instead, it sends error 1064 back to the driver (CodeIgniter, PDO, the CLI whoever issued the query).

Why CodeIgniter Produced a Broken INSERT

CodeIgniter’s Session library can store session rows in a table instead of a cookie.
The moment I enabled that option, CI started composing SQL like this:

INTO (`session_id`, `ip_address`, `user_agent`, `last_activity`)
VALUES (…);

Notice anything missing? That’s right no table name after INSERT INTO. My config lacked the setting that tells CI what table to use, so the driver built an incomplete query and MySQL balked.

The Explain Code

// application/config/config.php   (CodeIgniter 2.x)
$config['sess_use_database'] = TRUE;
$config['sess_table_name']   = 'ci_sessions';

// CodeIgniter 3.x and 4.x use a single key:
$config['sess_save_path']    = 'ci_sessions';

Once I added the key and created the table, the error vanished.

Creating the ci_sessions Table

Here’s the classic schema that still works fine today:

TABLE IF NOT EXISTS `ci_sessions` (
  session_id    VARCHAR(40)      NOT NULL DEFAULT '0',
  ip_address    VARCHAR(45)      NOT NULL DEFAULT '0',
  user_agent    VARCHAR(120)     NOT NULL,
  last_activity INT(10) UNSIGNED NOT NULL DEFAULT 0,
  user_data     TEXT             NOT NULL,
  PRIMARY KEY (session_id),
  KEY last_activity_idx (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Feel free to tweak column sizes, but keep the keys:

• session_id as PRIMARY KEY for fast look-ups
• last_activity_idx for garbage collection queries

A Clean, Working Login Flow

Below is the trimmed controller–model pair I now use in every new CI project. It:

• relies on CI’s session driver (database mode)
• uses PHP’s native password_hash() / password_verify()
• keeps logic readable and step-by-step

application/controllers/Auth.php

<?php  // Auth.php
class Auth extends CI_Controller
{
    public function __construct()
    {
        parent::__construct();
        $this->load->library(['session', 'form_validation']);
        $this->load->helper('url');
        $this->load->model('User_model');
    }

    public function login()
    {
        // Show form
        if ($this->input->method() === 'get') {
            return $this->load->view('auth/login');
        }

        // Validate
        $this->form_validation
             ->set_rules('username', 'Username', 'required|trim')
             ->set_rules('password', 'Password', 'required');

        if (!$this->form_validation->run()) {
            return $this->load->view('auth/login');
        }

        // Check credentials
        $user = $this->User_model->authenticate(
            $this->input->post('username', TRUE),
            $this->input->post('password')
        );

        if (!$user) {
            $this->session->set_flashdata('error', 'Wrong username or password');
            return redirect('login');
        }

        // Log in
        $this->session->sess_regenerate();
        $this->session->set_userdata([
            'user_id'   => $user->id,
            'username'  => $user->username,
            'logged_in' => TRUE
        ]);

        redirect('dashboard');
    }

    public function logout()
    {
        $this->session->sess_destroy();
        redirect('login');
    }
}

application/models/User_model.php

<?php  // User_model.php
class User_model extends CI_Model
{
    private $table = 'users';

    public function authenticate($username, $plainPassword)
    {
        $user = $this->db->get_where($this->table,
                                     ['username' => $username], 1)
                         ->row();

        if ($user && password_verify($plainPassword, $user->password_hash)) {
            return $user;
        }
        return FALSE;
    }
}

Tip: on first user creation, run
password_hash($plainPassword, PASSWORD_DEFAULT)
and store the result in password_hash.

Extra Practice

Below are two small projects you can bolt onto the flow above. Each teaches a real-world defence in bite-sized code.

Feel free to expand with two-factor codes, e-mail notifications on new device, or CAPTCHA after N failures. Each layer raises the bar for attackers

Final Thought

I started this post with a blank dashboard and a scary 1064, but a missing table name is thankfully an easy fix. Once the session table was in place, CodeIgniter settled down and the real fun crafting a solid login could begin.

• sessions stored safely in MySQL
• passwords handled by PHP’s strongest hash functions
• a clean controller model split that’s ready for features like lock-outs and “remember me” tokens