codeigniter Securing your web application SQL Injection Prevention
Example
SQL injection is an attack made on the database query. In PHP, we use mysql_real_escape_string() function to prevent this along with other techniques but CodeIgniter provides inbuilt functions and libraries to prevent this.
We can prevent SQL Injection in CodeIgniter in the following three ways −
- Escaping Queries
- Query Biding
- Active Record Class
Escaping Queries
<?php
$username = $this->input->post('username');
$query = 'SELECT * FROM subscribers_tbl WHERE user_name = '.
$this->db->escape($email);
$this->db->query($query);
?>
$this->db->escape() function automatically adds single quotes around the data and determines the data type so that it can escape only string data.
Query Biding
<?php
$sql = "SELECT * FROM some_table WHERE id = ? AND status = ? AND author = ?";
$this->db->query($sql, array(3, 'live', 'Rick'));
?>
In the above example, the question mark(?) will be replaced by the array in the second parameter of the 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, so you do not have to remember it.
Active Record Class
<?php
$this->db->get_where('subscribers_tbl',array('status'=> active','email' => '[email protected]'));
?>
Using active records, query syntax is generated by each database adapter. It also allows safer queries, since the values escape automatically.
