Method Chaining

[The New Guy]

To take an example from earlier in this thread:

$this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20); 

mysql_query("SELECT title FROM mytable WHERE id = $id LIMIT 10,20");

See if you can guess which one is suspectible to SQL injections?

Both.

[Entelekheia]

This is an old post, but...

I can see the advantages of abstraction here, It'd certainly be a great help to my team and I should stakeholders decide to change RDBMSes - we have a 60+ table database each with a Table Data Gateway object and a payload of methods.

But still how often are you going to change dbs?

I can see this often used as a argument for using database abstraction: "You might change your db and then...". But isn't it most useful in situations, when you don't know in advance what database your application is going to be used with?

If you're developing a blog software or a discussion board, it's useful if it can be installed on systems that might not be using LAMP without having to rewrite every SQL query.

[mihd]

chaining is definitely a bad idea for database abstratction

just use PDO directly or maybe Zend DB which is based on pdo, pdo will be added into the core at some stage anyways

[ahundiak]

Originally Posted by honeymonster View Post
To take an example from earlier in this thread:

Code:

$this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20);

mysql_query("SELECT title FROM mytable WHERE id = $id LIMIT 10,20");

See if you can guess which one is suspectible to SQL injections?

Both.

Could you explain how the one using the select object is vulnerable to sql injection? The where method will take care of properly escaping the $id value either directly or by using prepared statements.

[mihd]

Could you explain how the one using the select object is vulnerable to sql injection? The where method will take care of properly escaping the $id value either directly or by using prepared statements.

save yourself the hassle

http://www.php.net/pdo

[The New Guy]

Could you explain how the one using the select object is vulnerable to sql injection? The where method will take care of properly escaping the $id value either directly or by using prepared statements.

You just assumed that it escaped. Nobody told me.

[honeymonster]

You just assumed that it escaped. Nobody told me.

And you just assumed it wasn't. Thus, your answer was unqualified.

It would be more correct to say that the string interpolation version is definately suspectible to SQL injections, while in the case of the method chaining it depends on how the where method is implemented. It may be suspectible or it may not.

One of the most common reasons to use method chaining is to take the hassle out of dealing with SQL parameters, which is the only safe way to avoid SQL injections.

String interpolation is just plain stupid when dealing with SQL. Even if you take great care in sanitizing the variables, you will have a latent risk. Future code changes may open up avenues for malicious values to hit the string interpolation again. Especially so with PHPs rather strange type coercions.

[ahundiak]

save yourself the hassle

http://www.php.net/pdo

Explain please. PDO accepts as input a sql string along with an array of parameters. PDO does not (as far as I can tell) provide assistant in building the sql string.

[logic_earth]

Honestly it doesn't really matter. If I like using chaining i'll use it.
If I don't I won't use it. So really it does not matter one bit.

And I like chaining so I'm going to use it.

[mihd]

Explain please. PDO accepts as input a sql string along with an array of parameters. PDO does not (as far as I can tell) provide assistant in building the sql string.

look at binding parameters and prepared statements with PDO, they will give you more speed and extra security

[kyberfabrikken]

Explain please. PDO accepts as input a sql string along with an array of parameters. PDO does not (as far as I can tell) provide assistant in building the sql string.

It has bound parameters/prepared statements. You can do:

$stmt = $pdo->prepare("select * from foo where id = :id");
$stmt->execute(
  Array(':id' => 27));
$row = $stmt->fetch(); 

[mihd]

yep there are several ways of binding parameters, heres another

i highly recommend using PDO for database interactions, its well documented and fast because its a compiled extension

/**
 * adds a new user to the system
 * @param array, $user
 * @return int, $insert_id      
 * @throws PDOException          
 */
public function insert( $user ){
    
    $time    =    time();


    try {
        
        //insert into users table
        $STMT = $this->DB->prepare(    
            "INSERT INTO users (
                name, 
                pass, 
                email, 
                time_created, 
                time_updated 
            ) VALUES (
                :name, 
                :pass, 
                :email, 
                :time_created,
                :time_updated 
            )" 
        );
        

        $STMT->bindParam( ":name",             $user['name'],     PDO::PARAM_STR );
        $STMT->bindParam( ":pass",             $user['pass'],     PDO::PARAM_STR );
        $STMT->bindParam( ":email",         $user['email'], PDO::PARAM_STR );
        $STMT->bindParam( ":time_created",     $time,            PDO::PARAM_INT );
        $STMT->bindParam( ":time_updated",     $time,             PDO::PARAM_INT );
        $STMT->execute();
        
        
        //get the insert_id (user id of our new user)
        $insert_id = $this->DB->lastInsertId();
    } 
    catch (PDOException $e ) {
       
        throw new PDOException( $e->getMessage() );
    }
    
    return $insert_id;
} 

[ahundiak]

Thanks but the question is not about binding parameters it's about building the original sql statement. Suppose you need a "WHERE person_id IN (x,y,z)" clause in which x,y,z values are stored in the array $personIds. Of course you don't always have exactly three ids in the clause.

I don't believe pdo supports any way to just directly pass in the $personIds array. Instead you need to determine how many elements are in the array, generate a string of "?,?,?" and insert that into your sql string. With a query builder you would just do something like: $select->whereIn('person_id',$personIds);

[kyberfabrikken]

Thanks but the question is not about binding parameters it's about building the original sql statement ... With a query builder you would just do something like: $select->whereIn('person_id',$personIds);

That's true. PDO doesn't give you a way to dynamically generate queries.

[33degrees]

PDO is a low level abstraction, and there's definitely utility in building a higher level abstraction on top of it. As far as the method chaining approach goes, I'm not sure I would use it, but I can't see any real reason why it should be considered a bad thing. Surely it's really a personal preference, no?

[BerislavLopac]

Instead you need to determine how many elements are in the array, generate a string of "?,?,?" and insert that into your sql string. With a query builder you would just do something like: $select->whereIn('person_id',$personIds);

How about of implode(', ', $personIds)?

But I agree, it would be a practical method, especially, as implode() wouldn't add appropriate single quotes if the elements are strings etc. That's one reason why I prefer ADOdb, as it is written in PHP and is much easier to expand if need be.

[JaskaS]

How would you write this by using method chaining?

SELECT 
    u.id AS user_id ,u.name AS user_name,email AS user_email, 
    p.id AS product_id, p.code AS product_code, 
    pd.definition AS product_definition, pd.name AS product_name
FROM 
    USER u
INNER JOIN 
    user_product up ON (up.user_id = u.id AND up.waiting = 1)
INNER JOIN 
    product p (p.id = up.product_id)
LEFT OUTER JOIN 
    product_details pd ON (pd.product_id = p.id AND locale = :locale)
WHERE
    u.STATUS = 'active'
ORDER BY 
    ASC u.id, DESC p.name

And this isn't even very complex query, no subqueries, unions, groups, database functions and many other features used.

Edit: method chaining version

    $query->select(
        'u.id AS user_id ,u.name AS user_name,email AS user_email,
        p.id AS product_id, p.code AS product_code,
        pd.definition AS product_definition, pd.name AS product_name'
    )
    ->from('user', 'u')
    ->innerJoin('user_product','up')
    ->on('up.user_id = u.id AND up.waiting = 1')
    ->innerJoin('product','p')
    ->on('p.id = up.product_id')
    ->leftOuterJoin('product_details','pd')
    ->on('pd.product_id = p.id AND locale = :locale')
    ->where("u.status = 'active'")
    ->orderBy('ASC u.id, DESC p.name') 

It's just stupid and unreadable + you lose syntax validity checking and highlighting and also you can't copy that query on any sql-client for debugging. I can't find any pros from method chaining approach. SQL is standard, so why would you want to change that? Standards are good thing

[phpimpact]

I'll just add a quote to that

"Dealing with complexity is an inefficient and unnecessary waste of time, attention and mental energy. There is never any justification for things being complex when they could be simple."

[mihd]

ah php needs an equivalent to C#'s LINQ badly > http://en.wikipedia.org/wiki/Language_Integrated_Query

[honeymonster]

ah php needs an equivalent to C#'s LINQ badly > http://en.wikipedia.org/wiki/Language_Integrated_Query

Ah, yes, LINQ uses (a form of) method chaining underneath, but it also leverages lambda expressions, extension methods etc. PHP is not at all positioned to honor this.

[ahundiak]

Hi JaskaS,
How do you smartly handle dynamic queries? Suppose that sometimes you want product details and sometimes you don't?

[phpimpact]

How do you smartly handle dynamic queries? Suppose that sometimes you want product details and sometimes you don't?

Using a fluent interface is not smarter than writing an SQL query, and vice-versa. In the example below, someone can generate those arrays somewhere else, maybe in another class, and make your life miserable.

// Build this query:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p

$select = $db->select()
    ->from(array('p' => 'products'),
        array('product_id', 'product_name'));

// Build the same query, specifying correlation names:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p

$select = $db->select()
    ->from(array('p' => 'products'),
        array('p.product_id', 'p.product_name'));

// Build this query with an alias for one column:
//   SELECT p."product_id" AS prodno, p."product_name"
//   FROM "products" AS p

$select = $db->select()
    ->from(array('p' => 'products'), 

[Jaffizzle]

I see both query builder and straight SQL as tools.
I use the one that makes the most sense for the project.

Heres an example of how I would use the querybuilder.

<?php
class Users {
    private $fields = array('u.id', 'u.username', 'u.password', 'uf.address', 'uf.city', 'uf.state');
        
    private function _getUsersDB() {
        $db = new query;
        $db->select(array('user_table'=>'u'), $fields)
            ->join(array('user_location'=>'uf'), 'u.id = uf.id')
            ->where("u.status = 'active'")
            ->order('u.id', 'DESC')
            ->limit(0,20);
        return $db;
    }
    public function fetchUsers()
    {
        return $this->getUsersDB()
        ->getAssocArray();
    }
    public function fetchUsersById($id){
        return $this->_getUsersDB()
        ->where("u.id='%s'", $id) //prepends AND by default
        ->getAssocArray();
    }

    public function fetchUsersByCity($city)
    {
        return $this->_getUsersDB()
        ->where("uf.city = '%s'", $city)
        ->getAssocArray();
    }
}
?>

[bobbymac]

To take an example from earlier in this thread:

$this->db->select('title')->from('mytable')->where('id', $id)->limit(10, 20); 

mysql_query("SELECT title FROM mytable WHERE id = $id LIMIT 10,20");

See if you can guess which one is suspectible to SQL injections?

Susceptibility to attack is only part of the problem. You also want to ensure your application functions correctly. How do you know if $id is a number, string, date, or a foreign key? You shouldn't just use mysql_real_escape_string on every parameter as though it is some sort of gaffa-tape. You should check that $id is indeed what it should be, and if not, abort the query, rather than just munging it into the database and seeing what happens. Sure, there might be "no injection", but you might also end up with strange/invalid data in your database. Just because of laziness. At least if you construct your SQL queries yourself (either inside a getUsers-type method, or in the main code), you can construct it and do the appropriate data checks. You only need to code the checks once, and you will be rewarded with a program that shouldn't fail ever.