How To Prevent SQL Injections In CodeIgniter 4

Introduction

It is necessary to prevent malicious code injection in your application and one of the injections is SQL injection. Here I am going to show you how to prevent SQL injections in CodeIgniter 4 based applications.

A SQL injection attack consists of insertion of a SQL statement into your application via user input data. This malicious input is sent to the application via a traditional web client or API call.

SQL (Structured Query Language) injection generally involves the following activities:

  • A malicious code injection technique that may (completely) 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.

The following points should be considered to avoid SQL injections:

  • Use parameterized SQL statements
  • Use server-side input validations
  • Use LIMIT or other SQL controls to prevent mass disclosure of records

When you are working with MySQL database server and PHP language, you can easily escape input values using the function mysql_real_escape_string() but CodeIgniter 4 framework provides much more options to prevent SQL injections, of course, if you are using CodeIgniter 4 framework to build your web applications.

sql injections attack prevention

In CodeIgniter 4 framework, there are different ways to escape query, such as, Escaping Queries, Query Binding and Prepared Queries to prevent SQL injection in Codeigniter.

Related Posts:

SQL Injection Examples

An application uses untrusted data in the construction of the following vulnerable SQL call:

String sql = "SELECT * FROM customer WHERE custID = '" + request.getParameter("id") + "'";

Similarly, an application’s blind trust in frameworks may result in queries that are still vulnerable, (e.g. Hibernate Query Language (HQL)):

Query hsql = session.createQuery("FROM customer WHERE custID = '" + request.getParameter("id") + "'");

In both cases, the attacker modifies the id parameter value in their browser to send: ' or '1'='1. For example:

http://example.com/app/customerView?id=' or '1'='1

This would execute a database call that actually executes the SQL portion of the attack payload ' OR '1'='1, which will always return true. If this particular query returns true, it is possible to bypass authentication entirely without knowing the username or password. Since the ' OR 1=1 portion of the command actually evaluates to true, the statement completes and the next instruction is followed which in this case would be provisioning an action.

These changes of both queries to return all the records from the customer table. More dangerous attacks could modify or delete data, or even invoke stored procedures.

Another example is given below:

if(isset($_POST['submit_code'])){

    $code = $_POST['code'];
    $cost = $_POST['cost'];

    if(!empty($code)){
	
        // Insert into the database
        $result = $db->query("INSERT INTO postCode (code, cost) VALUES ('" . $code . "', '" . $cost . "')");

		//Do based on the $result
		
    } else {
	
        // Validation error
    
	}
	
}

The above code is open to SQL injection. Why? The values which going to be inserted into the database table are not sanitized or escaped and whatever you are getting as an input, you are just storing into the database.

Prevent SQL Injections

There are three ways to prevent SQL injections using Codeigniter 4 frameworkEscaping Queries, Query Binding and Prepared Queries and each of them has been explained below.

Escaping Queries

CodeIgniter has three methods that help you to escape queries:

$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(" . $db->escape($title) . ")";

$db->escapeString() This function escapes the data passed to it, regardless of type. Most of the time you’ll use the above function rather than the previous one. Use the function like this:

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

$db->escapeLikeString() 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 '%" . $db->escapeLikeString($search) . "%' ESCAPE '!'";

The escapeLikeString() 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

Query 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 = ?";
$db->query($sql, [3, 'live', 'Rick']);

The question marks in the 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 = ?";
$db->query($sql, [[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.

Named Bindings

Instead of using the question mark to mark the location of the bound values, you can name the bindings, allowing the keys of the values passed in to match placeholders in the query:

$sql = "SELECT * FROM some_table WHERE id = :id: AND status = :status: AND author = :name:";
$db->query($sql, [
    'id'     => 3,
    'status' => 'live',
    'name'   => 'Rick',
]);

Note that each name in the query MUST be surrounded by colons.

Prepared Queries

Most database engines support some form of prepared statements, that allow you to prepare a query once, and then run that query multiple times with new sets of data. This eliminates the possibility of SQL injection since the data is passed to the database in a different format than the query itself. When you need to run the same query multiple times it can be quite a bit faster, too. However, to use it for every query can have major performance hits, since you’re calling out to the database twice as often. Since the Query Builder and Database connections already handle escaping the data for you, the safety aspect is already taken care of for you. There will be times, though, when you need to ability to optimize the query by running a prepared statement, or prepared query.

Preparing the Query

This can be easily done with the prepare() method. This takes a single parameter, which is a Closure that returns a query object. Query objects are automatically generated by any of the “final” type queries, including insert, update, delete, replace, and get. This is handled the easiest by using the Query Builder to run a query. The query is not actually run, and the values don’t matter since they’re never applied, acting instead as placeholders. This returns a PreparedQuery object:

$pQuery = $db->prepare(function ($db) {
    return $db->table('user')->insert([
        'name'    => 'x',
        'email'   => 'y',
        'country' => 'US'
    ]);
});

If you don’t want to use the Query Builder you can create the Query object manually using question marks for value placeholders:

use CodeIgniter\Database\Query;

$pQuery = $db->prepare(function ($db) {
    $sql = "INSERT INTO user (name, email, country) VALUES (?, ?, ?)";

    return (new Query($db))->setQuery($sql);
});

If the database requires an array of options passed to it during the prepare statement phase you can pass that array through in the second parameter:

use CodeIgniter\Database\Query;

$pQuery = $db->prepare(function ($db) {
    $sql = "INSERT INTO user (name, email, country) VALUES (?, ?, ?)";

    return (new Query($db))->setQuery($sql);
}, $options);

Executing the Query

Once you have a prepared query you can use the execute() method to actually run the query. You can pass in as many variables as you need in the query parameters. The number of parameters you pass must match the number of placeholders in the query. They must also be passed in the same order as the placeholders appear in the original query:

// Prepare the Query
$pQuery = $db->prepare(function ($db) {
    return $db->table('user')->insert([
        'name'    => 'x',
        'email'   => 'y',
        'country' => 'US'
    ]);
});

// Collect the Data
$name    = 'John Doe';
$email   = 'j.doe@example.com';
$country = 'US';

// Run the Query
$results = $pQuery->execute($name, $email, $country);

This returns a standard result set.

Now let’s talk about the implementation part using CodeIgniter 4 and MySQL 8.

Prerequisites

Codeigniter 4.1.4, PHP 7.4.8, MySQL 8.0.26

Project Directory

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

Now I will create a project root directory called codeigniter-prevent-sql-injection anywhere as per your choice.

Now move all the directories and files from CodeIgniter framework into the project root 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;

Database Configuration

You need to setup database connection in order to fetch or write data to the table. The following configuration is done in the file app/Config/Database.php under the default group of database setting. Make sure you change or update the configuration according to yours. You can also setup your database using the .env file.

The main properties I have shown below and you can change according to your values:

...
'username' => 'root',
'password' => 'root',
'database' => 'roytuts',
...
'charset'  => 'utf8mb4',
'DBCollat' => 'utf8mb4_unicode_ci',
...

Model Class

The model class will perform fetch and save operations on the blog table. The following source code is written into app/Models/BlogModel.php file. So I will perform fetch, and save operations using different functions in the model class.

These functions will be called from controller class. I am not going to perform any database operations from controller class.

<?php

namespace App\Models;

use CodeIgniter\Model;

use CodeIgniter\Database\Query;

class BlogModel extends Model {
	
	protected $blog = 'blog';
	
	function get_blog_search_list($search) {
		if($search) {
			//Escaping Query
			//$db->escapeLikeString()
			$sql = "SELECT * FROM " . $this->blog .  " WHERE blog_title LIKE '%" . $this->db->escapeLikeString($search) . "%'";		
			$query = $this->db->query($sql);
			
			//Query Binding
			//Named Binding
			$sql = "SELECT * FROM " . $this->blog .  " WHERE blog_title = :title:";		
			//$query = $this->db->query($sql, ['title' => $search]);
			
			return $query->getResult();
		} else {
			$query = $this->db->table($this->blog)->get();
        
			return $query->getResult();
		}
    }
	
	//save new blog
    function save_new_blog($title, $content) {
        //Escaping Query
		//$db->escape()
        $sql = "INSERT INTO " . $this->blog . "(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);
		
		//$db->escapeString()
        $sql = "INSERT INTO " . $this->blog . "(blog_title,blog_content,blog_date)"
                . " VALUES('" . $this->db->escapeString($title) . "','" . $this->db->escapeString($content) .
                "','" . $this->db->escapeString(date('Y-m-d H:i:s')) . "')";
        $this->db->query($sql);		
		
        
        //Query Binding
        $sql = "INSERT INTO " . $this->blog . "(blog_title,blog_content,blog_date)"
                . " VALUES(?,?,?)";
        $this->db->query($sql, array($title, $content, date('Y-m-d H:i:s')));
        
        //Active Record Style
        $data = array(
            'blog_title' => $title,
            'blog_content' => $content,
            'blog_date' => date('Y-m-d H:i:s')
        );		
        $this->db->table($this->blog)->insert($data);
		
		//Prepared Query
		$pQuery = $this->db->prepare(function ($db) {
			return $this->db->table($this->blog)->insert([
				'blog_title' => 'title',
				'blog_content' => 'content',
				'blog_date' => 'date'
			]);
		});	
		// Run the Query
		$pQuery->execute($title, $content, date('Y-m-d H:i:s'));
		
		$pQuery = $this->db->prepare(function ($db) {
			$sql = "INSERT INTO " . $this->blog . "(blog_title,blog_content,blog_date)"
                . " VALUES(?,?,?)";

			return (new Query($this->db))->setQuery($sql);
		});		
		// Run the Query
		$pQuery->execute($title, $content, date('Y-m-d H:i:s'));
		
    }
	
}

In the above model class you will find functions – get_blog_search_list() and save_new_blog().

The get_blog_search_list() function takes an input and if the input is not passed to this function then it will simply return all rows from the database. If input is passed to this function then there are two things – like search and exact search. In the “like search” I have used escapeLikeString() function to sanitize the input. In the exact search I have used query binding (:title:) and later I have passed value for the parameter title.

The save_new_blog() function takes two parameters – title and content of the blog. In the first escaping query I have used escape() function and in the second escaping query I have used escapeString() function. The first function is better instead of using the second function because you need wrap your value around single or double quote (‘ or “) while you use the second function (escapeString()).

In the third query binding I have used place holders (?) and later I have replaced those place holders by the actual value.

The fourth query I have used as an active record style and this is like Codeigniter version 3.

In the fifth prepared query I have used query builder and put the initial values though those initial values are not applicable and instead act as place holders. The actual values are passed using the execute() function.

In the sixth prepared query I have created query manually using question marks for value place holders. The values passed to this query is same as the fifth one.

Remember you need the following line in order to use the prepared query:

use CodeIgniter\Database\Query;

Controller Class

The controller class is the entry point for the web application and a controller class handles request and response coming from and going back to clients.

The controller class performs the business logic for the application. The controller class is also responsible for validating, sanitizing, filtering the malformed request data before the data can be processed further for the application requirements.

The code for controller class is written into a file app/Controllers/Home.php.

<?php

namespace App\Controllers;

use App\Models\BlogModel;

class Home extends BaseController {
	
    public function index($search = '') {
		$model = new BlogModel();
		
		$data['blog_list'] = $model->get_blog_search_list($search);
		
        return view('blog_list', $data);
    }
	
	public function create() {
		helper(['form', 'url']);

		if($this->request->getPost('submit')) {
			$validation =  \Config\Services::validation();
			
			$validation->setRules([
				'title' => ['label' => 'Title', 'rules' => 'required'],
				'content' => ['label' => 'Content', 'rules' => 'required']
			]);
			
			if (!$validation->withRequest($this->request)->run()) {
				echo view('blog_add', ['errors' => $validation->getErrors()]);
			} else {
				$model = new BlogModel();
				
				$model->save_new_blog($this->request->getPost('title'), $this->request->getPost('content'));
				
				echo view('blog_add', ['success' => 'Blog successfully saved']);
			}
		} else {
			echo view('blog_add');
		}
	}
	
}

In the above controller class the index() function displays the blog data. If input is passed to the index() method and data are found in the database table then those are returned and displayed otherwise all rows from the blog table are displayed.

The create() function validates the input for blog data and saves into the blog table and simply displays the error or success message.

View File

The view or template file is responsible for displaying data to the end user.

List Of Blogs

List of blogs is shown using the view file – app/Views/blog_list.php.

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>CodeIgniter 4 and MySQL 8 - Prevent SQL Injections</title>
	<meta name="description" content="The small framework with powerful features">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<link rel="shortcut icon" type="image/png" href="/favicon.ico"/>

	<!-- STYLES -->

	<style>
	#blogs {
	  font-family: Arial, Helvetica, sans-serif;
	  border-collapse: collapse;
	  width: 100%;
	}

	#blogs td, #blogs th {
	  border: 1px solid #ddd;
	  padding: 8px;
	}

	#blogs tr:nth-child(even){background-color: #f2f2f2;}

	#blogs tr:hover {background-color: #ddd;}

	#blogs th {
	  padding-top: 12px;
	  padding-bottom: 12px;
	  text-align: left;
	  background-color: black;
	  color: white;
	}
	</style>
</head>
<body>

<div>
	<h1>CodeIgniter 4 and MySQL 8 - Prevent SQL Injections</h1>

	<div id="body">
		<p><a href="<?php echo site_url('home/create') ?>">Add New Blog</a></p>
	
		<?php
			if ($blog_list) {
		?>
        <table id="blogs">
            <thead>
				<tr>
					<th>Id</th>
					<th>Title</th>
					<th>Content</th>
					<th>Created</th>
                </tr>
            </thead>
			<tbody>
				<?php
					foreach ($blog_list as $blog) {
					?>
					<tr>
						<td>
							<?php echo $blog->blog_id; ?>
						</td>
						<td>
							<?php echo $blog->blog_title; ?>
						</td>
						<td>
							<?php echo $blog->blog_content; ?>
						</td>
						<td>
							<?php echo $blog->blog_date; ?>
						</td>
					</tr>
					<?php
				}
				?>
			</tbody>
        </table>
    <?php
        } else {
            echo '<div style="color:red;"><p>No Record Found!</p></div>';
        }
    ?>
	</div>
</div>

</body>
</html>

The above view file displays a list of blogs either from the search result of the blog table or all blogs directly from the blog tables.

This view file is called from the index() function of the Home controller class. I have also created a hyper link to the create blog page for easy navigation.

Create New Blog

The following view file is used to create a new blog content – app/Views/blog_add.php.

<!DOCTYPE html>
<html>
<head>
    <title>CodeIgniter 4 and MySQL 8 - Prevent SQL Injections</title>
	<meta name="description" content="The small framework with powerful features">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<link rel="shortcut icon" type="image/png" href="/favicon.ico"/>
</head>
<body>

	<div style="width: 600px; margin: auto;">
		<p><a href="<?php echo site_url('home') ?>">Blog Home</a></p>
	
		<?php
			if(isset($errors)):
		?>
			<ul style="list-style: none; color: red;">
				<?php foreach ($errors as $error) : ?>
				<li><?= esc($error) ?></li>
				<?php endforeach ?>
			</ul>
		<?php
			endif;
		?>
		
		<?php
			if(isset($success)):
		?>
			<ul style="list-style: none; color: green;">
				<li><?= esc($success) ?></li>
			</ul>
		<?php
			endif;
		?>

		<?= form_open('home/create') ?>

			<h5>Title</h5>
			<input type="text" name="title" value="<?php echo set_value('title'); ?>" />

			<h5>Content</h5>
			<textarea type="text" name="content"><?php echo set_value('content'); ?></textarea>

			<p/>
			<div><input type="submit" name="submit" value="Submit" /></div>

		</form>
	</div>

</body>
</html>

In the above view file, I have created two fields – title and content – whose values will be saved to the blog table.

I have also created a hyper link to the home page for easy navigation.

Testing the Application

I am not going to use any external server but CLI command to run the application. Make sure you start the MySQL database server before you start your application. If you want to use external server to run your application you can use. Execute the following command on your project root directory to run your application.

php spark serve

Your application will be running on localhost and port 8080.

As usually the blog table does not have any record, so on home page (http://localhost:8080) you will see nothing:

prevent sql injections codeigniter 4

Now click on Add New Blog link and create a new blog. If you do not put any value and try to save then you will see the following validation error:

prevent sql injections in codeigniter

Input some value and save, you will see the following success message:

sql injections prevention

Now if you check in the blog table, you will find 6 rows have been inserted. Why? Because I have called insert statement 6 times using different ways in the save_new_blog() function in model class.

block sql injections attack

Next clicking on Blog Home link you take you to the home page and you will see the following blog details:

prevent sql injection attack

Search Blog

Now if you want to search blog then you should pass your input to the URL itself. For example, http://localhost:8080/index.php/home/index/test%20blog, where 8080 is the port at localhost, home is the name of the controller and index is the home controller’s function and test%20blog is the input value, %20 is the value for empty space. In this case you will get the same results as shown in the home page in the above image.

Source Code

Download

Leave a Reply

Your email address will not be published. Required fields are marked *