PHP to begin deprecation of ext/mysql -- start moving your development to PDO now

Discussion in 'Scripts, 3rd Party Apps, and Programming' started by essellar, Jul 15, 2011.

Thread Status:
Not open for further replies.
  1. essellar

    essellar Community Advocate Community Support

    Messages:
    3,295
    Likes Received:
    227
    Trophy Points:
    63
    While there won't be any immediate effect, the PHP developers have finally decided to begin the long, slow, soft process of deprecating the mysql extension. Anyone doing development in PHP using MySQL as a database back-end should have stopped using the mysql methods long ago (it has been superceded by two newer methods over the years, first by mysqli, then by PHP Data Objects (PDO)), but it has taken until now for the first warning shot to be fired.

    For those not aware of the dangers, using the PHP mysql extension is one of the quickest and easiest ways of making your site vulnerable to SQL injection attacks. In addition, it forces you to handle all of the details of sanitising (preparing) data for storage in your database, and the follies of string escaping have provided a consistent source of laughs over the years (have you ever seen the ever-growing forest of backslashes as you page through search results?).

    There have been better ways of doing things for quite a while now. Unfortunately, there are also a lot of code snippets and tutorials on the web that still use the mysql extension, and many of those are on sites that claim to be authoritative (I'm looking at you, W3Schools). And now, knowing that ext/mysql is going to go away, there is no longer any excuse for using it in new development. Stay far, far away from any tutorial or library that encourages you to use the mysql extension -- if you are searching for PHP snippets or tutorials, make sure you include PDO in your search query.

    Just one small suggestion when using PDO, though -- do not use the ? (ordered parameters) syntax when preparing statements; use the :variable (named parameters) syntax instead. It'll keep you out of the asylum.
     
    • Like Like x 1
  2. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    I'd give slightly different advice about positional parameters. A positional parameter is fine if a statement has only one parameter and will only ever have one parameter. Multiple positional parameters are also fine if the statement is programmatically generated and executed, such as for an ORM. More than one parameter in a hand-written statement and named is definitely the way to go.
     
  3. callumacrae

    callumacrae not alex mac Community Support

    Messages:
    5,257
    Likes Received:
    97
    Trophy Points:
    48
  4. Skizzerz

    Skizzerz Contributors Staff Member Contributors

    Messages:
    2,929
    Likes Received:
    117
    Trophy Points:
    63
    So do I -- stickied.
     
  5. alfred4w

    alfred4w New Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
  6. miguelkp

    miguelkp Member

    Messages:
    306
    Likes Received:
    7
    Trophy Points:
    18
    Last edited: Mar 28, 2012
  7. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    That one has a few issues (use of SELECT *, extraneous information, information disclosure in error handling code). As is often the case with tutorials online, I've yet to see one that should be used to the exclusion of others. It's usually best to read multiple tutorials, so you don't get stuck in one way of thinking.

    In the end, it's most important to read the PDO manual pages for any information left out of the tutorials.
     
    • Like Like x 1
  8. miguelkp

    miguelkp Member

    Messages:
    306
    Likes Received:
    7
    Trophy Points:
    18
    True: best comparing various tutorials, instead of using only one. And thanks for the links.
    I also use the last one you put (the official php.net documentation). The best, obviously, but first time I read it, I was a bit lost.

    So what I like in the one I put is that it follows similar structure than the old mysql extension tutorials. I mean, it explains kinda 'step-by-step' how to connect, how to work with opened database (queries) and how to close connection; ie, it tells you what functions (or rather, methods) you have to look for in php.net to get the finest information. That way is the one I'm using right now and I'm finding easier to move from old mysql extension to PDO sentences.
     
  9. xdevx10m

    xdevx10m New Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
  10. misson

    misson Community Paragon Community Support

    Messages:
    2,572
    Likes Received:
    72
    Trophy Points:
    48
    mysqli is simpler as a drop-in replacement for the outdated mysql extension, as the interfaces are similar, but PDO is generally considered the easier to use and more expressive extension, requiring fewer method calls to achieve the same affect. The API Overview on MySQL.com says: "PDO has its advantages, such as a clean, simple, portable API". The reason to use mysqli over PDO is that the former exposes advanced MySQL features. PDO is a generic DB interface, so its MySQL driver can't expose many MySQL specific features without causing compatibility issues (differences between RDBMSs in SQL support cause enough problems).

    Things are slightly better for mysqli in PHP 5.4, when support for the Traversable interface was added to mysqli_result. If you must have 5.3 compatibility, however, PDO's support for Traversable is a big win when it comes to abstraction. You can use a PDOStatement in the place of an array or other collection type if all that's needed is to iterate over it (with foreach). You can use PDOStatement::setFetchMode to fetch results as objects for some real magic.
     
  11. mariannaguide56

    mariannaguide56 New Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    0
  12. simon.evanz48

    simon.evanz48 Member

    Messages:
    32
    Likes Received:
    0
    Trophy Points:
    6
    Last edited: Sep 4, 2012
  13. essellar

    essellar Community Advocate Community Support

    Messages:
    3,295
    Likes Received:
    227
    Trophy Points:
    63
    It's important to note that Doctrine2/DBAL uses PDO; it isn't a replacement or an installable PHP language extension. (Mostly, it seems to be about moving transactions/ACID into code to make up for missing/inconsistent database features.)
     
Thread Status:
Not open for further replies.

Share This Page