• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Is it possible to make this query using Query Builder, if not how can I make it safe?

#3
(11-30-2015, 08:26 AM)kilishan Wrote: First off - for many projects, there's no need to use Query Builder, and you'll get a (very) slight performance increase from not using it. So, I won't dig into the possibilities of using the query with Query Builder here. Instead, lets look at how to make it safe. This is easy to do using Query Bindings. Basically, replace all instances of variables in your query with a question mark. Then, pass those values as an array to the query() method:

Code:
$query_string = "SELECT SUM(author = ? AND email = ? AND approved = 1) AS email_author_approved, " .
       "SUM(author = ? AND email = ? AND spam = 1) AS email_author_spam, " .
       "SUM(author = ? AND spam = 1) AS author_spam, " .
       "SUM(email = ? AND spam = 1) AS email_spam " .
       "FROM comments " .
       "WHERE author = ? OR email = ?";
       
       $query = $this->db->query($query_string, [
          $author,
          $email,
          $author,
          $email,
          $author,
          $email,
          $author,
          $email
       ]);
       $result = $query->row_array();

An even better option, in this case since you re-use $author and $email repeatedly, is to manually escape the values and then inserting them into the query is fine like you had it. You don't need to manually add the single quotes around the variables in the SQL, though, since the escaping will do that for you.

Code:
$author = $this->db->escape($author);
$email = $this->db->escape($email);

$query_string = "SELECT SUM(author = {$author} AND email = {$email} AND approved = 1) AS email_author_approved, " .
       "SUM(author = {$author} AND email = {$email} AND spam = 1) AS email_author_spam, " .
       "SUM(author = {$author} AND spam = 1) AS author_spam, " .
       "SUM(email = {$email} AND spam = 1) AS email_spam " .
       "FROM comments " .
       "WHERE author = {$author} OR email = {$email}";
       
       $query = $this->db->query($query_string);
       $result = $query->row_array();

This is awesome! Extremely helpful. Thank you so much! I can't wait to try it out tomorrow.
Reply


Messages In This Thread
RE: Is it possible to make this query using Query Builder, if not how can I make it safe? - by DreamOfSleeping - 11-30-2015, 12:50 PM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.