What is the best way to escape unwanted characters in order to avoid further database syntax errors when executing insert/update queries when submitting forms?
eg. $note = $this->db->escape( $data['note'] );
INSERT query renders a further syntax error.
ie.
$this->db->query("INSERT INTO notes (note_id, note) VALUES ('$note_id','$note')");
as it mentioned in Codeignitier documentation when using query builder class
All values are escaped automatically producing safer queries.
so its safer to use query builder class for both error handling and security
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 '!'";
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 '!' the condition for you, and so you’ll have to manually do that.