Prevent SQL Injection in Codeigniter

Introduction

This tutorial will show you how to prevent SQL injection in Codeigniter application. It is very important to escape the variable you pass to the database query because, in web application security, SQL injections play an important role.

SQL injection is:

  • a malicious code injection technique that may destroy your database.
  • one of the most common web hacking techniques.
  • the placement of malicious code in SQL statements, via input fields in web page.

You usually use mysql_real_escape_string() function to prevent SQL injections, but you do not need to use this function if you are using PHP based Codeigniter framework for building your web application. In Codeigniter there are different ways to escape query, such as, Escaping Queries, Query Binding and Active Record to prevent SQL injection in Codeigniter.

Preventing SQL Injection

The following reference has been taken from Codeigniter documentation. There are three ways to prevent SQL injection using Codeigniter framework.

Escaping Queries

It’s a very good security practice to escape your data before submitting it into your database. CodeIgniter has three methods that help you do this:

$this->db->escape()

This function determines the data type so that it can escape only string data. It also automatically adds single quotes around the data so you don’t have to:

$sql = "INSERT INTO table (title) VALUES(".$this->db->escape($title).")";

$this->db->escape_str()

This function escapes the data passed to it, regardless of type. Most of the time you’ll use the above function rather than this one. Use the function like this:

$sql = "INSERT INTO table (title) VALUES('".$this->db->escape_str($title)."')";

$this->db->escape_like_str()

This method should be used when strings are to be used in LIKE conditions so that LIKE wildcards (‘%’, ‘_’) in the string are also properly escaped.

$search = '20% raise';
$sql = "SELECT id FROM table WHERE column LIKE '%" .
    $this->db->escape_like_str($search)."%' ESCAPE '!'";

Important

The escape_like_str() method uses ! (exclamation mark) to escape special characters for LIKE conditions. Because this method escapes partial strings that you would wrap in quotes yourself, it cannot automatically add the escape ! condition for you, and so you’ll have to manually do that.

Query Bindings

Bindings enable you to simplify your query syntax by letting the system put the queries together for you. Consider the following example:

$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick'));

The question marks in the above query are automatically replaced with the values in the array in the second parameter of the query function.

Binding also work with arrays, which will be transformed to IN sets:

$sql = "SELECT * FROM some_table WHERE id IN ? AND status = ? AND author = ?";
$this->db->query($sql, array(array(3, 6), 'live', 'Rick'));

The resulting query will be:

SELECT * FROM some_table WHERE id IN (3,6) AND status = 'live' AND author = 'Rick'

The secondary benefit of using binds is that the values are automatically escaped, producing safer queries. You don’t have to remember to manually escape data; the engine does it automatically for you.

Active Record

$this->db->insert() Generates an insert string based on the data you supply, and runs the query. You can either pass an array or an object to the function. All values are escaped automatically producing safer queries.

Prerequisites

Apache HTTP Server 2.4, Codeigniter 3.0.6/3.1.11, PHP 5.6/7.4.3, MySQL 5.6/8.0.17/8.0.22

Project Directory

It’s assumed that you have setup Apache, PHP and Codeigniter in Windows system.

Now I will create a project root directory called codeIgniter-sql-injection-prevention the Apache server’s htdocs folder.

Now move all the directories and files from CodeIgniter framework into codeIgniter-sql-injection-prevention directory.

I may not mention the project root directory in subsequent sections and I will assume that I am talking with respect to the project root directory.

MySQL table

I have created the following table to store blogs. If you are using MySQL version less than 8 then you need to specify the size of the column value for int data type.

CREATE TABLE `blog` (
  `blog_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `blog_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `blog_content` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `blog_date` datetime COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Autoload Configuration

You need some configurations, such as, auto-loading for helpers to avoid loading every time you need to use.

Modify application/config/autoload.php file for auto-loading libraries and helper functions.

This one time auto-loading gives flexibility to uniformly use the helpers and libraries anywhere throughout the application without loading repeatedly.

For my example I am auto-loading only database.

$autoload['libraries'] = array('database');
$autoload['helper'] = array('url', 'form');

Database Configurations

You need to configure database connection into application/config/database.php file. Please make sure to change according to your database settings. If you are using MySQL version less than 8 then you need to use utf8 for charset and collate.

$db['default'] = array(
	'dsn'	=> '',
	'hostname' => 'localhost',
	'username' => 'root',
	'password' => 'root',
	'database' => 'roytuts',
        ...
	'char_set' => 'utf8mb4',
	'dbcollat' => 'utf8mb4_unicode_ci',
        ...
);

View File

Create a view file add_blog.php under /application/views folder and this file will be used to save new blog. This view file is responsible for getting input for a new blog from the end user and this page is also responsible for showing the appropriate message while new blog saved successfully or if any error occurred during saving the new blog.

<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Codeigniter Database Escape</title>
        <style type="text/css">

            ::selection { background-color: #E13300; color: white; }
            ::-moz-selection { background-color: #E13300; color: white; }

            body {
                background-color: #fff;
                margin: 40px;
                font: 13px/20px normal Helvetica, Arial, sans-serif;
                color: #4F5155;
            }            

            #body {
                margin: 0 15px 0 15px;
            }

            #container {
                margin: 10px;
                border: 1px solid #D0D0D0;
                box-shadow: 0 0 8px #D0D0D0;
            }

            h1 {
                margin-left: 15px;
            }

            .error {
                color: #E13300;
            }

            .success {
                color: darkgreen;
            }
        </style>
    </head>
    <body>
        <div id="container">
            <h1>CodeIgniter Database Escape</h1>            
            <div id="body">
                <?php
                if (isset($success) && strlen($success)) {
                    echo '<div class="success">';
                    echo '<p>' . $success . '</p>';
                    echo '</div>';
                }
                if (validation_errors()) {
                    echo validation_errors('<div class="error">', '</div>');
                }
                ?>
                <?php
                $attributes = array('name' => 'add_blog_form', 'id' => 'add_blog_form');
                echo form_open($this->uri->uri_string(), $attributes);
                ?>
                <p>Title : <input type="text" name="title" id="title" size="54"/></p>
                <p>Content : <textarea id="content" name="content" rows="10" cols="50"></textarea></p>
                <p><input name="add_blog" value="Add Blog" type="submit" /></p>
                <?php
                echo form_close();
                ?>
            </div>

        </div>
    </body>
</html>

Controller

Create a Controller class file Blog.php under /application/controllers folder for handling client’s request and response.

This controller class also communicated with the model layer and finally perform the database activities in the model class to save the new blog.

This class also validates and required fields according to the rules set to the input fields.

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

/**
 * Description of Blog
 *
 * @author https://roytuts.com
 */
class Blog extends CI_Controller {

    function __construct() {
        parent::__construct();
        $this->load->model('blogmodel');
        $this->load->library('form_validation');
    }

    public function index() {
        $data = array();
        if ($this->input->post('add_blog')) {
            $this->form_validation->set_rules('title', 'Title', 'required');
            $this->form_validation->set_rules('content', 'Content', 'required');
            if ($this->form_validation->run()) {
                $title = $this->input->post('title');
                $content = $this->input->post('content');
                $result = $this->blogmodel->save_new_blog($title, $content);
                $data['success'] = 'Blog successfully added';
            }
        }
        $this->load->view('add_blog', $data);
    }

}

Model

Create a model class for interacting with database. Here in this model class I am performing database operations in order to save the new blog. I am also escaping the input values sent from the end user in order to prevent SQL injection in Codeigniter. Look carefully how I have used three types of methods to prevent SQL injection in Codeigniter.

<?php

defined('BASEPATH') OR exit('No direct script access allowed');

/**
 * Description of BlogModel
 *
 * @author https://roytuts.com
 */
class BlogModel extends CI_Model {

    private $blogs = 'blog';   // blog table

    function __construct() {
        parent::__construct();
    }

    //save new blog
    function save_new_blog($title, $content) {
        //Escaping Query
        $sql = "INSERT INTO " . $this->blogs . "(blog_title,blog_content,blog_date)"
                . " VALUES(" . $this->db->escape($title) . "," . $this->db->escape($content) .
                "," . $this->db->escape(date('Y-m-d H:i:s')) . ")";
        $this->db->query($sql);
        
        //Query Binding
        $sql = $sql = "INSERT INTO " . $this->blogs . "(blog_title,blog_content,blog_date)"
                . " VALUES(?,?,?)";
        $this->db->query($sql, array($title, $content, date('Y-m-d H:i:s')));
        
        //Active Record
        $data = array(
            'blog_title' => $title,
            'blog_content' => $content,
            'blog_date' => date('Y-m-d H:i:s')
        );		
        $this->db->insert($this->blogs, $data);
    }

}

Route Configuration

You want to open the page with default controller so you need to point to your own controller. Now modify /application/config/routes.php file for pointing the default controller class.

$route['default_controller'] = 'blog';

Testing the Application

Enough coding, now test the application you built. Make sure your MySQL server is up and running. You have created the required table in the MySQL server. Your Apache HTTP Server is also up and running.

Hitting the URL http://localhost/codeIgniter-sql-injection-prevention will give you a form where you can input title and content for your blog and hit the Add Blog button to save your blog into database. Once saved you will see the successful message Blog successfully added. Not inputting any value to the input fields will display errors.

You can also check your database to find the new entries. For example, I entered Test Blog Title/test Blog Content and the following image shown three entries in the blog table.

prevent sql injection in codeigniter

Source Code

Download

2 Thoughts to “Prevent SQL Injection in Codeigniter”

  1. BusyWitch

    Hi! Nice tutorial, but this inserts not one blog post at a time, but three blog posts. Any idea what is causing this to occur?

    1. Notice in the save_new_blog($title, $content) function, there are three ways to save blog. You need to use any one of them.

Leave a Comment