www.webdeveloper.com
Results 1 to 7 of 7

Thread: PDO usage

  1. #1
    Join Date
    Nov 2010
    Posts
    56

    PDO usage

    Hey all,

    I have been told that I should be using PDO for my mysql stuff. I have been doing some reading and have started implementing it but I want to clarify a few things before I go ahead and use it wrong!

    Do I need to run the try/catch for all queries, or only the connection?
    PHP Code:
    try {
        
    $dbh = new PDO('mysql:host=localhost;dbname=test'$user$pass);
    } catch (
    PDOException $e) {
        print 
    "Error!: " $e->getMessage() . "<br/>";
        die();

    What is the easiest way for showing the mysql error - I have tried $query->errorInfo() but while I am getting an error that is sufficient, I don't think this code is doing it..

    To run a basic query I have tried the following
    PHP Code:
    $query $DBH->query($sql) or die($query->errorInfo()); 
    But I don't think it is safe against sql injection.. Is this method ok if the sql code does not have user input?
    When there is user input, what is the simplist way for me to run it? I have looked at examples and they all seem to have a 4 or 5 step process to run a simple mysql command. Apparently it is really bad to have the variables in the sql - instead they need to be passed in via unnamed or named place holders?
    PHP Code:
    /* Execute a prepared statement by passing an array of values */
    $sth $dbh->prepare('SELECT name, colour, calories
        FROM fruit
        WHERE calories < ? AND colour = ?'
    );
    $sth->execute(array(150'red'));
    $red $sth->fetchAll(); 
    This seems terribly annoying when I am used to running:
    PHP Code:
    mysql_query('SELECT name, colour, calories FROM fruit WHERE calories < $var1 AND colour = $var2'
    I understand that this way is insecure but I am hopeful there is a simpler way for me to use PDO.
    I'm obviously very new at this so any help/explaination would be much appreciated!

  2. #2
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    724
    Some things that will shorten the process for you.

    1 - When you have a script that is going to make a db query, make the connection at the top and let it exist for the duration of the script. PHP will close it automatically once the script goes away so don't even worry about it.
    2 - Writing your query statement inside of function call (as you have done in the past) is easy -yes. It also works. But - many people find it easier to create that query in a var so that it may be displayed during development when there are problems. Think about that.

    3 - It's not the use of PDO or mysqli or MySQL that controls whether you have potentially bad practices going on related to injection. It is how you write your query! Since MySQL is going away and since it doesn't support prepared queries, obviously it poses the greatest risk. So can PDO and mysqlI if you don't take advantage of prepared queries. So - the use of a prepared query removes the risk of injection if properly used.

    With all of this understood, a query process is pretty straight forward. Write it, prep it, then execute it with an array.

    As for the try/catch question. You really don't want to be displaying error information to the user once your system goes into production. IIRC, when you enable exceptions in your connection logic and you have php display errors on, you will get an exception whenever your calls fail. And yes you need to do some kind of error checking for all kinds of external calls (connect,select, execute, fopen,fwrite, etc.). Read up on good practices on making the pdo connection to see what options you s/b using. My current connection logic is:

    PHP Code:
      $db_options = array(PDO::ATTR_EMULATE_PREPARES => false,
        
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC); 
    This is a var that is used in the actual constructor call that I have taken from a sticky post I found either here or on another forum.

    Finally - while it may seem that you have more steps involved with pdo, I think you are saving effort now. Think about the proper argument preparation you had to do with a MySQL query. Now you don't have to escape every argument since a prepare statement takes care of that for you. Making the connection once at the top of you script is a practice that removes that step from all of your queries in that script. (I use a std. include module for the connection and simply make a call to a function in it that makes the connection and selects the db all at the same time.)

    Hopefully I have not mis-stated anything here - and if I have I'm sure someone will step right up and correct me.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  3. #3
    Join Date
    Nov 2010
    Posts
    56
    Thanks for the reply!
    I have indeed put the connect script into a function that I can call! One more thing - Do I only need to use the array method for user variables? So can I do something like:
    PHP Code:
    /* Execute a prepared statement by using one set value and one variable value */ 
    $sth $dbh->prepare('SELECT name, colour, calories 
        FROM fruit 
        WHERE calories < 5 AND colour = ?'
    ); 
    $sth->execute(array('red')); 
    $red $sth->fetchAll(); 

    /*Or a statement with no user variables - displaying data*/
    $sth $dbh->query('SELECT name, colour, calories 
        FROM fruit 
        WHERE calories < 5 AND colour = red'
    ); 
    $result $sth->fetchAll(); 
    I hope this question makes sense..

    thanks again

  4. #4
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    724
    Yes it makes sense and you are correct. Although you have an error. The value of 'red' needs to be in quotes, as do all string values in a query statement.
    JG
    PS - If you're posting here you should be using:

    error_reporting(E_ALL | E_NOTICE);
    ini_set('display_errors', '1');


    at the top of ALL php code while you develop it!

  5. #5
    Join Date
    May 2014
    Posts
    905
    Quote Originally Posted by ginerjm View Post
    Yes it makes sense and you are correct. Although you have an error. The value of 'red' needs to be in quotes, as do all string values in a query statement.
    Uhm no they don't -- though it's good practice.

    See, the only real reason you need to put extra quotes inside a query is if the word used is the same as a reserved word in the SQL language. like if you had:

    id = quijada

    you're fine.

    id = select

    and you're boned.

    id= `select`

    and you're fine again.

    Since 'red' is not a reserved word in mySQL, it's fine.

    ... and yes, PDO::query and PDO::exec exist for when you do NOT have user variables. PDO:repare + PDOStatement::execute is for when you do... and don't mix them up.

    Generally speaking you shouldn't need more error handling than the connection string and the error logs. It'll bomb with an invalid result set or operation anyways, meaning bugs you should have handled/fixed LONG before you deploy the code. It's why you don't see a lot of error handling in people's PDO based code; once you've written your query strings properly, what's there to go wrong other than something catastrophic like a table missing?

    Oh, and some advice, avoid using PDOStatement::fetchAll -- SERIOUSLY. Making a complete extra copy of your data-set is just begging for a fat-ass slow memory footprint. While/fetch -- if you need the result elsewhere or later, store/pass the PDOStatement!
    Java is to JavaScript as Ham is to Hamburger.

  6. #6
    Join Date
    Nov 2010
    Posts
    56
    Thanks guys - makes sense!

  7. #7
    Join Date
    May 2014
    Posts
    75
    You can simplify it down quite a bit... I don't remember almost any of the PDO functions anymore, I built a few different functions:
    PHP Code:
    if ($host $this->sqlToObject('SELECT * FROM hosts WHERE host = :host;', ['host' => strtolower($_GET['host'])])) {
       
    // do something with $host

    If I want to get an array of objects sqlToObjects (yes, I know... tricky naming system).
    I can do the same with sqlToArray and sqlToArrays.

    The code's a little complex, and perhaps a tiny bit wasteful at times (did I [and if so how well] optimize it?), but it's worth the simplicity.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles