#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189

    PDO: PDO noob, getting started


    I'm a little slow to the party... I didn't realize until last night mysql_* was being depreciated... So now I am changing my current project over to PDO.

    I want to make sure I understand as many of the pitfalls out of the gate as possible.

    I've been reading for quite some time about SET NAMES, and have found lots of information that says how to fix it IF you need them, but haven't found anything that tells me HOW TO KNOW if I need them. LOL

    Instead of trying to understand WHY in too much detail, just let me ask for now, if I'm okay.

    Collation: latin1_swedish_ci (default)
    (Maybe it should be utf8_general_ci ???)
    PHP Code:
    $pdo_options = array(
        
    PDO::ATTR_EMULATE_PREPARES => false,
        
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
        
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    );

    $pdo = new PDO("mysql:host=localhost;dbname=MYDB",
        
    'DBUSER',
        
    'DBPASS',
        
    $pdo_options
    ); 
    The next question, I just want to make sure my assumptions are correct.

    If I have control of a variable (non-user input) it's okay to stick it directly into a query without quoting.. Correct?
    PHP Code:
    $salt=bin2hex(openssl_random_pseudo_bytes(16));
    $token=sha1($_POST['userpass'].$salt);

    $pdo->query("INSERT INTO usertable
        SET token='
    {$token}',
            salt='
    {$salt}',
            email= "
    .$pdo->quote($_POST['email'])); 
    I'm sure I'm forgetting a question I have, and will have more later.. But that's it for the moment...
    Thomas Tremain
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,349
    Rep Power
    594
    Right from the start you need to include error checking. For example:
    PHP Code:
    try {
       
    $pdo = new PDO("mysql:host=localhost;dbname=MYDB",
        
    'DBUSER',
        
    'DBPASS',
        
    $pdo_options
       
    );  
    catch (
    PDOException $e) {
       die(
    "Connection error: ".$e->getMessage());

    It is poor programming practice to put literal strings in your query. It is much better to build your query string first, then you can echo it for debugging purposes, to be sure it contains what you expect. You also need to use prepared statements to prevent injection attacks.
    PHP Code:
    $query="INSERT INTO usertable
        SET token='"
    .$token."',
            salt='"
    .$salt."',
            email= "
    .$pdo->quote($_POST['email']);
    echo 
    "$query<br />";
    $stmt=$pdo->prepare($query);
    try {
       
    $stmt->execute();
    }
    catch (
    PDOException $e) {
       die(
    "Query failed: ".$e->getMessage());

    Note how I changed the use of quotes. I've had poor success mixing them like that.

    Comments on this post

    • Jacques1 disagrees : That's *not* how to handle exceptions.
    Last edited by gw1500se; March 7th, 2013 at 02:29 PM.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    first of all: congratulations on switching to PDO. This is a very good decision.

    Using SET NAMES is generally the wrong way of specififying the connection encoding. This applies to every database extension, not just PDO. The problem is that PHP doesn't know when you change the encoding with SET NAMES, so it will still think you're using the original encoding. This can break any PHP function that's dependend on the correct encoding, especially the escaping functions.

    So don't use SET NAMES unless you really not what you're doing -- but then you probably wouldn't ask.

    The correct way is to specify the encoding in the DSN string:
    Code:
    mysql:host=localhost;dbname=YOURDB;charset=utf8
    Unfortunately, this isn't possible before PHP 5.3.6. If you have an outdated PHP version, you (or your webhoster) has to edit the MySQL configuration and set the default encoding to UTF-8 -- or you can switch to the MySQLi extension, which can change the encoding since PHP 5.0.5.

    Apart from that, your options are correct.

    As to the second question:

    Do not escape your values by hand. That's just absurd given the fact that the new extensions were specifically invented to support prepared statements (and other advanced features).

    Use prepared statements as explained here.

    Generally speaking, every value should be escaped, even if you define it yourself. There's a small risk that you accidentally insert critical characters (maybe in the future due to code changes), and there's absolutely no reason to take that risk. Escape everything, even if it's under your control.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by gw1500se
    Right from the start you need to include error checking. For example:
    No, do not do that.

    This isn't error checking, this is nonsense (I'm not blaming you, gw1500se, I know it's common practice and wasn't "invented" by you).

    The purpose of exceptions is to allow specific reactions: You can try to fix the problem, you can stop the script and log the error message, you can completely ignore the error etc.

    If you replace an exception with a dumb die(), you completely defeat its purpose. It becomes totally useless. Instead of reacting specifically, you just dump the error message on the screen of anybody who happens to visit the site at that moment -- which is actually a big security risk.

    And it doesn't even make sense: Stopping the script and displaying the error message is exactly what exceptions do by default (on a badly configured server). All you do is cut off important info like the stack trace and instead display some vague message.

    Only catch exceptions if you actually have a concrete plan to handle the problem -- like trying to connect again or using a different server. Otherwise, just let it kill your script and have PHP log the full error message (via the php.ini).

    Comments on this post

    • gw1500se disagrees : Most of time an exception wihtout a handler gives you no feedback. You just get a false or null returned. Try/Catch is the best way to determine what the failure is, IMO.
    Last edited by Jacques1; March 7th, 2013 at 03:13 PM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189
    Originally Posted by Jacques1
    Use prepared statements as explained here.
    I was under the impression that prepare and execute takes 3 times as long to process...

    But if I'm going to "prepare" I may as well use named placeholders at execute time. (to save quoting individual values)
    Thomas Tremain
  10. #6
  11. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    I was under the impression that prepare and execute takes 3 times as long to process...
    The amount of time required to prepare and execute a query on the PHP side is so insignificant that it will have no performance effect on your application even if it does take 3 times longer than some other method.
    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
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189
    Originally Posted by E-Oreo
    The amount of time required to prepare and execute a query on the PHP side is so insignificant that it will have no performance effect on your application even if it does take 3 times longer than some other method.
    I have a hard time swallowing that.. An application is not a single query, it is a series of queries, run by thousands of users at the same time.

    3 times longer, is still 3 times longer. If most of your server resources are going to mySQL usage, wouldn't it be nice to trim that down by 2/3?

    Sounds like the microsoft business model... "They can just buy faster computers"
    Thomas Tremain
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Originally Posted by ttremain
    I have a hard time swallowing that.. An application is not a single query, it is a series of queries, run by thousands of users at the same time.
    OK, please show us your concrete benchmark results comparing prepared statements with the equivalent raw queries given the load you expect on your server. Did you even do any benchmarks? Because if you didn't, this whole discussion is pointless. You have to actually measure the performance and compare the figures before you can conclude that something is inefficient. A vague gut feeling isn't enough.

    Did you even experience any concrete performance issues? Or is this more of a fear that it could be slow?

    Anyway, none of the benchmarks I've seen would even come close to "3 times slower". Check this one, for example:

    http://webdevrefinery.com/forums/top...do-benchmarks/
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189
    Originally Posted by Jacques1
    OK, please show us your concrete benchmark results comparing prepared statements with the equivalent raw queries given the load you expect on your server. Did you even do any benchmarks? Because if you didn't, this whole discussion is pointless. You have to actually measure the performance and compare the figures before you can conclude that something is inefficient. A vague gut feeling isn't enough.

    Did you even experience any concrete performance issues? Or is this more of a fear that it could be slow?
    If I had concrete benchmarks, I would not have said "I was under the impression..." and would not be here asking the questions...

    I never said it IS three times slower, but I did say; if it were, how could that be insignificant.

    How could I experience performance issues with this? I also said I've been using PDO for only about 36 hours now... Again, hence the questions.
    Thomas Tremain
  18. #10
  19. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Well, if it was three times slower and if you had thousands of users per second and a hopelessly underpowered server, yeah, in that case it could be a problem.

    But I've never heard of anything like that. Most database performance issues come from missing or wrong indices or badly written queries.

    And I think it makes sense to only worry about a problem if it has actually occured.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2005
    Location
    Vancouver, WA, USA
    Posts
    397
    Rep Power
    189
    Originally Posted by Jacques1
    Well, if it was three times slower and if you had thousands of users per second and a hopelessly underpowered server, yeah, in that case it could be a problem.

    But I've never heard of anything like that. Most database performance issues come from missing or wrong indices or badly written queries.

    And I think it makes sense to only worry about a problem if it has actually occured.
    Good to know. And yes I do have thousands of users per second in one of my apps.

    Being proactive does not mean waiting until there is a problem, then trying to fix it.

    So I'm just trying to know as much as possible about PDO, before I implement several thousand lines of code, and find out I have to do it all over again.
    Thomas Tremain
  22. #12
  23. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    3 times longer, is still 3 times longer. If most of your server resources are going to mySQL usage, wouldn't it be nice to trim that down by 2/3?
    3 times a very small percentage is usually still a very small percentage.

    The rest of your application and your actual query are going to take the same amount of time to execute regardless of which database API you use. It might take your application 0.0001 seconds to prepare/execute a query on the PHP side, but the execution time for an HTTP request is usually around to 0.05 seconds, and the full load time, including network transfer, is usually around to 0.5 seconds.

    Even if prepare/execute is 3 times slower (I'm not saying it is), you would only gain 0.00006 seconds per query. This is why the difference is insignificant.

    Additionally if you're ever in a situation where you need to run a query more than once, then prepare/execute is actually faster.

    If your app is actually serving thousands of requests per second then you must have a pretty massive server infrastructure already.
    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