1

I know a standard query runs an SQL statement that requires all data to be escaped for safety, like preventing SQL injections.

And Prepared statements bind parameters where escaping data in not needed, and is ideal for queries that are being executed multiple times.

But I was wondering in terms of security and safety, what is the difference between these three query examples.

I know the first query ($query) with binding parameters is the safest and most ideal to use, but are the other two query examples ($query2 and $query3) also safe when using the CodeIgniter framework?

And if we just use php, is $query3 safe because the data variable its quoted?

Query 1

$query = "SELECT * FROM users WHERE id = ?";

$bind  = array($id);
$query = $this->db->query($query, $bind);

Query 2

$query2 = "SELECT * FROM users WHERE id = '" . $this->db->escape_str($id) . "'";

Query 3

$query3 = "SELECT * FROM users WHERE id = '" . $id . "' ";
Mason
  • 157
  • 1
  • 2
  • 12
  • 1
    1) same as any other bind you've ever used 2) non-binding proper way of escaping a item from input 3) bad; nothing escaped – Alex Jul 19 '18 at 04:39
  • just use the first one – Kevin Jul 19 '18 at 04:40
  • @Alex Thanks :) so if I was to use the second one, is it perfectly safe when dealing with data from input, or is it not as safe as binding? There are some cases where binding is harder to use, thats all – Mason Jul 19 '18 at 04:42
  • @Mason use `escape()` over `escape_str()` Read - https://stackoverflow.com/a/48575820/4595675 – Abdulla Nilam Jul 19 '18 at 04:46
  • I can't speak as to how safe it is. I can say that the way I do it (which automatically escapes) is using query builder e.g. `$this->db->get_where('users', $id);` – Alex Jul 19 '18 at 04:48
  • also note if you use `escape()` over `escape_str()` it adds `'var'` (single quotes) which isn't great for many things like `SHOW COLUMNS FROM 'projects'` (sql error) – Alex Jul 19 '18 at 04:54
  • Thanks alot @Alex and @Abdulla Nilam. In terms of `escape()` vs `escape_st()`, in the CI docs, it mentions that `$this->db->escape_str() ` escapes the data passed to it, regardless of type, whereas `escape()` determines the data type so that it can escape only string data. So does that mean non string data does not need to be escaped? Thanks – Mason Jul 19 '18 at 05:07
  • 1
    https://forum.codeigniter.com/archive/index.php?thread-18106.html – Alex Jul 19 '18 at 05:13
  • @Alex - Thanks a lot awesome answers! – Mason Jul 19 '18 at 05:18

0 Answers0