• 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?

#1
Here is an example of the query just as a string that I pass to the query function. This works as I expected.

Code:
$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->result_array()[0];

I would like to use the Query Builder class instead. In the user guide I have seen the select_sum function, but no examples that uses multiple sums together after each other. Can this be done? If not possible to use query builder class how can I make this safe.

I'll now explain what this code does in case it's unclear. It's basically my idea of a very simple spam filter.

It uses the sum function to count how many times this email/name combination has had comments approved and displays the results in a column called "email_author_approved.

It uses the sum function to count how many times this email/name combination has had comments marked as spam and displays results in a column called "email_author_spam".

Then does the same thing separately for the email and the author. If i understand correctly the information I pass to the sum function will result in either true or false which is the same as 1 or 0. So i can get a count. The where is to stop it counting all the rows.
Reply

#2
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();
Support Development  • Practical CodeIgniter 3  • Vulcan - CLI Tools for CI4
Reply

#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

#4
(11-30-2015, 04:18 AM)DreamOfSleeping Wrote: Here is an example of the query just as a string that I pass to the query function. This works as I expected.

Code:
$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->result_array()[0];

I would like to use the Query  Builder class instead.  In the user guide I have seen the select_sum function, but no examples that uses multiple sums together after each other.  Can this be done? If not possible to use query builder class how can I make this safe.

I'll now explain what this code does in case it's unclear. It's basically my idea of a very simple spam filter.

It uses the sum function to count how many times this email/name combination has had comments approved and displays the results in a column called "email_author_approved.

It uses the sum function to count how many times this email/name combination has had comments marked as spam and displays results in a column called "email_author_spam".

Then does the same thing separately for the email and the author. If i understand correctly the information I pass to the sum function will result in either true or false which is the same as 1 or 0. So i can get a count. The where is to stop it counting all the rows.

Similar response with more query buider.

I would make this query like following:


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

$select "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";

$query $this->db->select($selectfalse)
       ->from('comments')
       ->where('author', $author)
       ->or_where('email', $email);
       
       $result 
$query->row(); 
Greetings.
Reply

#5
(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();

Learning something new everyday! Thank you.
BTW, do you have your thoughts written down anywhere about using/not using Query Builder as you've suggested?
Reply

#6
(12-15-2015, 09:09 PM)meow Wrote: Learning something new everyday! Thank you.
BTW, do you have your thoughts written down anywhere about using/not using Query Builder as you've suggested?

I don't really have them written down about that, in particular. The biggest benefits of Query Builder is that it's cross-platform and makes switching databases simple. However, in all of the projects I've been on, I've never run across a time where we needed to switch databases. I'm sure it happens, but I don't think it's as big of a deal as it is sometimes made out to be. It also makes it convenient to build dynamic queries since you can add parts of the query in different methods of the model, which is often pretty convenient.

However, once I start getting into more complex queries with sub-queries, etc, I'll typically use straight SQL. That's in part because I'm working in SequelPro working out the queries so it's easiest to copy/paste it into the model.
Support Development  • Practical CodeIgniter 3  • Vulcan - CLI Tools for CI4
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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