Welcome Guest, Not a member yet? Register   Sign In
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();
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.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB