Menu
I am inserting some data into a MySQL table using CodeIgniter. Because I am using INSERT IGNORE INTO and do not want to edit the active records class to enable this feature, I am generating the SQL query manually.
$this->db->query("INSERT IGNORE INTO my_table(lat, lng, date, type)
VALUES ('" . $data['lat'] . "', '" . $data['lng'] . "', '" . $data['date'] . "', '" . $data['type'] . "')");
Problem: The query failed when the string in $data['type'] contained a single quote. How can I make it such that these characters that need to be escaped gets escaped automatically, like when using Active records?
259
For escaping HTML output, in most cases htmlspecialchars() is all you need, but you can use the xss_clean() function any time.
SQL injection is an attack made on database query. In PHP, we are use mysql_real_escape_string() function to prevent this along with other techniques but CodeIgniter provides inbuilt functions and libraries to prevent this.
To submit a query, use the following function: $this->db->query('YOUR QUERY HERE'); The query() function returns a database result object when "read" type queries are run, which you can use to show your results. When "write" type queries are run it simply returns TRUE or FALSE depending on success or failure.
The update function returns a value: $result = $this->db->update('mytable', $data); Check that value for either being TRUE (success) or FALSE (failure). update runs query internally and then returns the return value of query (Ref):
Another way is to use Query Binding which automatically escapes all the values:
$sql = "INSERT IGNORE INTO my_table(lat, lng, date, type) VALUES (?,?,?,?);";
$this->db->query($sql, array($data['lat'], $data['lng'], $data['date'], $data['type']));
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
