SitePoint Sponsor

User Tag List

Page 4 of 4 FirstFirst 1234
Results 76 to 99 of 99

Thread: Method Chaining

  1. #76
    Put your best practices away. The New Guy's Avatar
    Join Date
    Sep 2002
    Location
    Canada
    Posts
    2,087
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote 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.
    "A nerd who gets contacts
    and a trendy hair cut is still a nerd"

    - Stephen Colbert on Apple Users

  2. #77
    SitePoint Enthusiast
    Join Date
    Aug 2006
    Posts
    10
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is an old post, but...

    Quote Originally Posted by rvdavid View Post
    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.

  3. #78
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    368
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #79
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    706
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    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?
    Quote Originally Posted by The New Guy View Post
    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.

  5. #80
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    368
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ahundiak View Post
    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

  6. #81
    Put your best practices away. The New Guy's Avatar
    Join Date
    Sep 2002
    Location
    Canada
    Posts
    2,087
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by ahundiak View Post
    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.
    "A nerd who gets contacts
    and a trendy hair cut is still a nerd"

    - Stephen Colbert on Apple Users

  7. #82
    SitePoint Wizard
    Join Date
    Feb 2007
    Posts
    1,274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by The New Guy View Post
    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.

  8. #83
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    706
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by mihd View Post
    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.

  9. #84
    . shoooo... silver trophy logic_earth's Avatar
    Join Date
    Oct 2005
    Location
    CA
    Posts
    9,013
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)
    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.
    Logic without the fatal effects.
    All code snippets are licensed under WTFPL.


  10. #85
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    368
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ahundiak View Post
    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

  11. #86
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ahundiak View Post
    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:
    PHP Code:
    $stmt $pdo->prepare("select * from foo where id = :id");
    $stmt->execute(
      Array(
    ':id' => 27));
    $row $stmt->fetch(); 

  12. #87
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    368
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    PHP Code:
    /**
     * 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;


  13. #88
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    706
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    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);

  14. #89
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ahundiak View Post
    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.

  15. #90
    SitePoint Guru 33degrees's Avatar
    Join Date
    May 2005
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    Last edited by 33degrees; Sep 24, 2007 at 07:25. Reason: Spelling...

  16. #91
    SitePoint Guru BerislavLopac's Avatar
    Join Date
    Sep 2004
    Location
    Zagreb, Croatia
    Posts
    830
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ahundiak View Post
    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.

  17. #92
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    Finland
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How would you write this by using method chaining?
    Code SQL:
    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
    PHP Code:
        $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
    Last edited by JaskaS; Sep 27, 2007 at 05:50.

  18. #93
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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."

  19. #94
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    368
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah php needs an equivalent to C#'s LINQ badly > http://en.wikipedia.org/wiki/Language_Integrated_Query

  20. #95
    SitePoint Wizard
    Join Date
    Feb 2007
    Posts
    1,274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mihd View Post
    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.

  21. #96
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    706
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Hi JaskaS,
    How do you smartly handle dynamic queries? Suppose that sometimes you want product details and sometimes you don't?

  22. #97
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    232
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ahundiak View Post
    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.

    PHP Code:
    // 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'), 

  23. #98
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Location
    California
    Posts
    78
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 Code:
    <?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();
        }
    }
    ?>

  24. #99
    SitePoint Zealot
    Join Date
    Mar 2002
    Location
    Perth, Australia
    Posts
    157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote 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?
    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.
    Paul Davey
    webmaster for Whitford Church of Christ


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •