#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171

    About PDO, bind value, execute and retriving data from the database


    Objective: Connect to database using PDO and retrieve results based on the arguments. This is what it looks like.

    My questions & wanderings:

    1. I want to keep it really simple and only say:
      $sql = "select, updat or delet";
      $args = ('List of args');
      $execute :: DB ($sql,$args);
      And a nice way to show the results that takes me to 2 things:
      1 - How can I get the run_query function to return the results back?
      2 - How can I print these results? As you can see in the right column it is array in array.
    2. I don't see why not using __construct the way I have used it. I remember everytime I use construct I get told to piss off lol
      Is there anything against this approach?
    3. Is the query secure from sql injection? If not, wft should I do?



    Please do not post replies that include
    • The answer. I dont want a database connection script. I want to understand what is going on in my code.
    • pear()>CGI(./lib/puty.wsdl) exec(Putty)./
    PHP Code:
    define(HOST'lochalhost');
    define(DATABASE'test');
    define(USER'test');
    define(PASS'');
    class 
    DB{
        public 
    $conn;
        public 
    $sql;
        public 
    $results;
        public function 
    __construct($sql$args)
            {
            try {
                    
    $this->conn = new PDO('mysql:'.HOST.'=;dbname='.DATABASE.''USERPASS);
                    
    $this->run_query($sql$args);            
            }
            catch (
    PDOException $e)
                {
                    print 
    "Error!: " $e->getMessage() . "<br/>";
                    die();
                }
            
            }
        private function 
    run_query($sql$args)
            {
                
    $sth $this->conn->prepare($sql);
                
    $sth->bindParam(':id'$args['id']);
                foreach (
    $args as $val =>$row)
                    {
                        
    $sth->bindParam(":".$val$row);
                    }
                
    $sth->execute();
                
    $result $sth->fetchAll(PDO::FETCH_ASSOC);
                
    print_r($result);
            }
    }
    $sql "SELECT * FROM hotels WHERE id >:id AND status=:status";
    $args = array('id'=>0'status'=>'active');
    $object = new DB($sql$args); 
    Thank you
    Last edited by zxcvbnm; October 11th, 2012 at 03:17 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,571
    Rep Power
    595
    1) I don't think you want to create a new connection every time you want to run a query. Create a connection once, then make the run_query public and call it.

    2) You should validate the data before using prepare but that is sufficient to minimize injection risk.

    3) Here is how I do my class which is generic for all my applications and I don't use a constructor.
    PHP Code:
    class DB {
       private static 
    $instance=null;
       private static 
    $mysqli=null;

       public static function 
    getConnection($mysqlhost,$mysqluser,$mysqlpassword,$mysqldatabase) {
           if (!
    self::$instance) {
             
    self::$instance=new DB();
             try {
                
    self::$mysqli=(new PDO("mysql:host=$mysqlhost;dbname=$mysqldatabase",$mysqluser,$mysqlpassword);
             }
             catch(
    PDOException $e) {
                die(
    "Connection error: ".$e->getMessage());
             }
          }
          return(
    self::$mysqli);
       }
    }
    // I then create a new class specific to my application
    class myApp {
       private 
    $mysqlresource=null;
       
    //other variables including SQL user and password, etc

       
    public function __construct() {
          
    $this->mysqlresource=DB::getConnection($this.hostname,$this.user,$this.password,$this.db);
       }
    // I put my query, update, insert, delete and other methods here

    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,687
    Rep Power
    171
    Originally Posted by gw1500se
    1) I don't think you want to create a new connection every time you want to run a query. Create a connection once, then make the run_query public and call it.

    2) You should validate the data before using prepare but that is sufficient to minimize injection risk.
    Thank you for your tips. I applied some changes. What do you think? Any ways I could make it better?
    PHP Code:
    define(HOST'lochalhost');
    define(DATABASE'test');
    define(USER'test');
    define(PASS'');
    class 
    Database{
        
        private 
    $conn;
        private static 
    $instance_connection;
        public function 
    connect()
                {
                    if (!
    self::$instance_connection)
                        {
                            
    self::$instance_connection = new Database();    
                            try
                                {
                                        
    $this->conn = new PDO('mysql:'.HOST.'=;dbname='.DATABASE.''USERPASS);
                                }
                            catch (
    PDOException $e)
                                {
                                    print 
    "Error!: " $e->getMessage() . "<br/>";
                                    die();
                                }
                        }
                    return 
    self::$instance_connection;
                
                }
        
        public function 
    run_query($sql$args='')
            {
                
    $sth $this->conn->prepare($sql);
                if(
    $args!='')
                    {
                        foreach (
    $args as $val =>$row)
                            {
                                
    $sth->bindParam(":".$val$row);
                            }
                    }
                
    $sth->execute();
                
    $result $sth->fetchAll(PDO::FETCH_ASSOC);
                return(
    $result);
                }
    }    
    $sql "SELECT * FROM hotels WHERE id >:id AND status=:status";
    $args = array('id'=>0'status'=>'active');
    $object = new Database();
    $object->connect();
    $data $object->run_query($sql$args); 
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,571
    Rep Power
    595
    Looks good to me.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  8. #5
  9. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    PHP Code:
    <?php

    define
    ('HOST''lochalhost'); // Pass the name of the constant as a string, it hasn't been defined yet
    define('DATABASE''test');   // Pass the name of the constant as a string, it hasn't been defined yet
    define('USER''test');       // Pass the name of the constant as a string, it hasn't been defined yet
    define('PASS''');           // Pass the name of the constant as a string, it hasn't been defined yet

    class Database{
        
        
    // This was sort of a frightening mixture of static and non-static functionality; it's not good to mix static and non-static functionality in one method.
        
        // This is your static code:
        
    private static $instance_connection;
        
        public static function 
    instance()
            {
                if (!
    self::$instance_connection)
                    {
                        
    // Passing the connection parameters into the constructor is a lot more flexible and re-usable than having them hard-coded in the class.  By putting them into the static instance method rather than your constructor, the 99% of your code that uses HOST/DATABASE/USER/PASS doesn't have to deal with specifying them every time, but the 1% of your code that doesn't can still create its own Database object by bypassing ::instance()
                        
    self::$instance_connection = new Database(HOSTDATABASEUSERPASS);
                    }
                return 
    self::$instance_connection;
            }
        
        
    // This is your non-static code:
        
    private $conn;
        
        
    // There is no reason this constructor should throw an error
        
    public function __construct($host$database$user$pass)
            {  
                try
                    {
                        
    $this->conn = new PDO('mysql:'.$host.'=;dbname='.$database.''$user$pass);
                    }
                catch (
    PDOException $e)
                    {
                        print 
    "Error!: " $e->getMessage() . "<br/>";
                        die();
                    }
            }
        
        public function 
    run_query($sql$args='')
            {
                
    $sth $this->conn->prepare($sql);
                if(
    $args!='')
                    {
                        foreach (
    $args as $val =>$row)
                            {
                                
    $sth->bindParam(":".$val$row);
                            }
                    }
                
    $sth->execute();
                
    $result $sth->fetchAll(PDO::FETCH_ASSOC);
                return(
    $result);
            }
    }

    $object Database::instance();
    $sql "SELECT * FROM hotels WHERE id >:id AND status=:status";
    $args = array('id'=>0'status'=>'active');
    $data $object->run_query($sql$args);
    Yes this does protect against SQL injection.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around

IMN logo majestic logo threadwatch logo seochat tools logo