Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » PDO Integration

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

Page: 1
Go to End
Author Topic: 1790 Views
  • xeraa
    Avatar
    Google Summer of Code Hacker
    58 Posts

    PDO Integration Link to this post

    While working on the PDO integration, I came up with the following questions (I guess more will follow soon )

    PHPUnit:
    As I write PDODatabase.php totally anew, I guess it would be a good start to use PHPUnit there (with full code-coverage), possibly adding it elsewhere later if it's working well.
    For the implementation, there will be an additional class / file PDODatabaseTest which will contain all tests, but PDODatabase will be the same with and without the unit testing (so no design-by-contract approach).
    Is that ok or does anyone want it to be done differently (for example using design-by-contract)?

    CHECK / REPAIR table:
    Although I did some searching, I could not find the equivalent of MySQL's REPAIR and CHECK in PostgreSQL and MS SQL. So I guess for these databases checkAndRepairTable / runTableCheckCommand will just trigger an error and do nothing or are there better suggestions?

  • Markus
    Avatar
    Google Summer of Code Hacker
    152 Posts

    Re: PDO Integration Link to this post

    Hi Philipp,

    I think it's good if PDODatabaseTest is completely separated from the PDODatabase class since the test code doesn't need to be shipped with the standard distribution.

    Regarding the CHECK/REPAIR TABLE commands I think they are rather proprietary and it's the same question as with some other SQL statements. How do you abstract e.g. the IFNULL, CONCAT, LIMIT, or TOP statements or the date formats?

    That's the real advantage of using a library like ADOdb (which can also be used with the PDO driver). PDO doesn't offer any of those abstractions, it's just a consistent interface for accessing databases.

    Another question is if you will add support for prepared statements?
    This would result in huge security benefits and (luck for you ) it is the only feature that PDO emulates for drivers that don't support them (PDO).

  • xeraa
    Avatar
    Google Summer of Code Hacker
    58 Posts

    Re: PDO Integration Link to this post

    Thanks for the quick answer

    That's exactly what I thought with the testing, so if that's ok with everyone I'll do it like that.

    Right, PDO is just an interface (we didn't want to introduce another layer of abstraction, dependency of other software,...).
    With different SQL statements it really depends on a case by case basis. Most things can be done or worked around (for example LIMIT is used by MySQL and PostgreSQL, while MS SQL uses TOP [but it doesn't understand OFFSET], MySQL and PostgreSQL use SELECT VERSION() and MS SQL does it with SELECT @@VERSION,...). So generally I try to make it work, but with CHECK / REPAIR I just couldn't find anything appropriate. If that doesn't exist (wanted to ask if anyone knew anything first ;-) ), I will simply have to leave it empty for MS SQL and PostgreSQL, if there is nothing else...

    Prepared statements will be used / I'm currently writing some. And I'm trying to get them right to avoid SQL injections...
    $dbConn->prepare("CREATE DATABASE " . $database) *ggg*

  • Markus
    Avatar
    Google Summer of Code Hacker
    152 Posts

    Re: PDO Integration Link to this post

    Great! If you support prepared statements they will automatically avoid SQL injections..

    $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
    $stmt->bindParam(1, $name);
    $stmt->bindParam(2, $value);

    // or

    $stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':value', $value);

    no need to escape $name or $value..

    Maybe the easiest thing would be to add a parameter to Database::query() so that you can pass an array with values (indexed or associative array).
    To keep the whole stuff backwards compatible you could assign a default value of null to that new parameter..

  • xeraa
    Avatar
    Google Summer of Code Hacker
    58 Posts

    Re: PDO Integration Link to this post

    <quote>If you support prepared statements they will automatically avoid SQL injections..</quote>
    If done correctly - I only say $dbConn->prepare("CREATE DATABASE " . $database)

    I'm currently using $stmt->bindParam(':name', $name); (don't really like the numbering).

  • Matt
    Avatar
    Core Development Team
    84 Posts

    Re: PDO Integration Link to this post

    FYI, Sam has worked on some test runners using PHPUnit - I'm not sure if he's informed you separately.

    Take a look at http://doc.silverstripe.com/doku.php?id=automated-testing for more info - but your basic idea is exactly right - except you'd extend SapphireTest instead of the PHPUnit class (SapphireTest will give you a number of handy utility functions to deal specifically with doing testing in SilverStripe).

    HTH.

    1790 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.