Codeigniter Tutorials

Home » Codeigniter »

How to SQL Injection Prevention in codeignitr?

How to use SQL injection in codeigniter?

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.

SQL injection is an attack made on the database query. In PHP, we use mysql_real_escape_string() function to prevent this.

CodeIgniter provides inbuilt functions and libraries to prevent SQL injection.
you can apply SQL Injection in CodeIgniter in the following three ways −

Escaping Queries

Query Biding

Active Record Class

Escaping Queries : $this->db->escape()

File Name :

<?php
$email = $this->input->post('email');
$username = $this->db->escape($email);
$query = 'SELECT * FROM itechxpert WHERE user_name = '.$username;
$this->db->query($query);
?>

################### OR ############################

<?php
$email = $this->input->post('email');
$query = 'SELECT * FROM itechxpert WHERE user_name = '.$this->db->escape($email);
$this->db->query($query);

//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);
?>

$this->db->escape() function automatically adds single quotes around the data and determines the data type so that it can escape only string data.

$this->db->escape_str()

This function escapes the data passed to it, regardless of type.

File Name :

$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.

File Name :

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

Note : 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 Biding :

File Name :

<?php
$sql = "SELECT * FROM itechxpert WHERE id = ? AND status = ? AND author_name = ?";
$this->db->query($sql, array(1, 'active', 'Sana'));
?>

//Query Binding
$sql = $sql = "INSERT INTO " . $this->blogs . "(title,content,created_at)"
. " VALUES(?,?,?)";
$this->db->query($sql, array($title, $content, date('Y-m-d H:i:s')));

the question mark(?) will be replaced by the array in the second parameter of query() function. The main advantage of building query this way is that the values are automatically escaped which produce safe queries. CodeIgniter engine does it for you automatically

Active Record Class:-

Active record either pass an array or an object to the function. All values are escaped automatically producing safer queries.

File Name :

<?php
$this->db->get_where('itechxpert',array
('status'=> active','email' => 'info@itechxpert.in'));
?>

active records allows safer queries and values escape automatically.

File Name :

//Active Record
$data = array(
'title' => $title,
'content' => $content,
'created_at' => date('Y-m-d H:i:s')
);
$this->db->insert($this->blogs, $data);

Database Error

File Name :

Even if you have turned off the PHP errors, MySQL errors are still open. You can turn this off in application/config/database.php. Set the db_debug option in $db array to FALSE as shown below.

$db['default']['db_debug'] = FALSE;

Error log

File Name :

Another way is to transfer the errors to log files. So, it will not be displayed to users on the site. Simply, set the log_threshold value in $config array to 1 in application/cofig/config.php file as shown below.

$config['log_threshold'] = 1;

CSRF Prevention

File Name :

CSRF stands for cross-site request forgery. You can prevent this attack by enabling it in the application/config/config.php file as shown below.

$config['csrf_protection'] = TRUE;
When you are creating form using form_open() function, it will automatically insert a CSRF as hidden field. You can also manually add the CSRF using the get_csrf_token_name() and get_csrf_hash() function. The get_csrf_token_name() function will return the name of the CSRF and get_csrf_hash() will return the hash value of CSRF.

The CSRF token can be regenerated every time for submission or you can also keep it same throughout the life of CSRF cookie. By setting the value TRUE, in config array with key ‘csrf_regenerate’ will regenerate token as shown below.

$config['csrf_regenerate'] = TRUE;
You can also whitelist URLs from CSRF protection by setting it in the config array using the key ‘csrf_exclude_uris’ as shown below. You can also use regular expression.

$config['csrf_exclude_uris'] = array('api/person/add');

Password Handling

File Name :

Many developers do not know how to handle password in web applications, which is probably why numerous hackers find it so easy to break into the systems. One should keep in mind the following points while handling passwords −

DO NOT store passwords in plain-text format.

Always hash your passwords.

DO NOT use Base64 or similar encoding for storing passwords.

DO NOT use weak or broken hashing algorithms like MD5 or SHA1. Only use strong password hashing algorithms like BCrypt, which is used in PHP’s own Password Hashing functions.

DO NOT ever display or send a password in plain-text format.

DO NOT put unnecessary limits on your users’ passwords

File Name :

CodeIgniter's Active Record methods automatically escape queries for you, to prevent sql injection.

$this->db->select('*')->from('tablename')->where('var', $val1);
$this->db->get();

or

$this->db->insert('tablename', array('var1'=>$val1, 'var2'=>$val2));

File Name :

If you don't want to use Active Records, you can use query bindings to prevent against injection.

$sql = 'SELECT * FROM tablename WHERE var = ?';
$this->db->query($sql, array($val1));
Or for inserting you can use the insert_string() method.

$sql = $this->db->insert_string('tablename', array('var1'=>$val1, 'var2'=>$val2));
$this->db->query($sql);
There is also the escape() method if you prefer to run your own queries.

$val1 = $this->db->escape($val1);
$this->db->query("SELECT * FROM tablename WHERE var=$val1");

File Name :

But remember, it won’t work if you still do combining usual (partially) query function inside of active record function like this :

$query = $this->db->where("title LIKE '%$input%'");
Which actually could be changed like this.

$query = $this->db->like("title", $input);
The point is, use every bit of possibility of CodeIgniter’s Active Record and don’t mess with it.

But If that ain’t work, there is an alternative If you have a very long query and don’t bother to convert it to Active Record’s style, you can sanitised your input manually by using this function :

$sanitised_title = $this->db->escape($title);
// For use inside LIKE query

$sanitised_title = $this->db->escape_like_str($title);
And you can safely concatenate the sanitised/escaped input inside your query.

Itechtuto

Codeigniter Tutorials

How to SQL Injection Prevention in codeignitr?

How to use SQL injection in codeigniter?

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

File Name :

Connect Us Socially:

Quick Links