TIP: Use Markdown or, <pre> for multi line code blocks / <code> for inline code.
These forums are read-only and for archival purposes only!
Please join our new forums at discourse.kohanaframework.org
Prototype Candidate for PDO driver
  • All,



    in reviewing the PDO driver, I've created a prototype for consideration for KO 3.1.



    My goal is to create a driver that is compatible with existing drivers, takes advantage of the PDO abstraction as much as possible, and includes 'prepare' capability. Here are my results (only lightly tested), offered as grist for the mill to the development team.



    The following list and notes were updated July 18



    Components:





    Notes:



    There's lots to consider here. Some highlights...



    database_pdo

    Database_PDO is intended as a replacement for Kohana_Database_PDO. It adds support for ->prepare(), and adds a key 'config' constructor parameter. This allows passing of PDO attributes, and default result configuration details, including the result class to create in response to queries. It returns (by default) database_result_pdo for select queries, and prepare() insert, update, and delete returns a (new) Operation_PDO object (to provide access to execute() etc...)



    database_pdo_mysql


    Database_PDO_Mysql is an extension of Database_PDO to support specifics to the Mysql PDO driver (a 'subdriver' in the Kohana context). I tried to avoid this, but it turns out that some of this is inevetable (not much). For example lastInsertID seems to be a problem for some drivers, and requires custom attention. The PDO_Mysql subdriver has subdriver specific implementations of list_tables() and list_columns() (borrowed from the standard Mysql driver).



    database_query


    Database_Query is an extension of (and candidate for integration into) Kohana_Database_Query. It rewrites execute(), adds prepare(), and adds a config() builder method.



    database_result_pdo


    Database_Result_PDO is an extension of PDO_Result. It supports database two way cursor control (where supported) and cursor emulation (for compatibility - not recommended), errorInfo(), and errorCode(). It also has a $config constructor parameter for flexibility. Has an execute() method for select queries.



    database_operation


    Analagous to database_result, this is an abstract class for wrapping a PDOStatement that has been executed for update, delete, or insert.



    database_operation_pdo


    Concrete implementation of database_operation



    database configuration

    I've changed the database configuration somewhat for PDO. I've dropped the 'persistent' property, as there is a PDO attribute that deals with it. I've added an 'attributes' sub-array, to hold formal PDO attributes.



    I've also added a 'result' subarray, to hold default configuration values for result classes, including 'attributes' for the pdostatement, 'class' to determine what Kohana class to create in response to queries (Database_Result_<class> - default 'pdo', but could be a subclass if needed: 'pdo_postgresql'), and an 'emulatecursor' flag. Specific implementations could add other values.



    I've likewise added an 'operation' subarray, to hold default configuration values for operation classes.



    test script


    I've created a test script that lightly tests some of the options, this really needs more work



    test table


    I've exported the SQL of my simple test table, FWIW.

    Feedback



    All feedback is of course very welcome, including some indication as to whether this is going in the right direction, and if anyone has the time, testing with other databases would be great. There are lots of technical design issues as well.



    Hope this helps.



    - Henrik

  • For the record, one of the loose ends of the pdo prototype I put forward is that the driver prepare() method returns a PDOStatement object in response to Update, Delete, or Insert.

    In principle it would be better to return an Operation object (analagous to the Result object), and wrap the PDOStatement in that. This would also allow an error check after the fact where the error handling had been set to PDO::ERROR_SILENT (in the configured attributes).

    Thus Database drivers' execute() and prepare() would both return either a Result or an Operation object (never the numeric results which are now returned for operations) which could be further queried for information (such as counts and error information), or re-executed.

    - Henrik
  • I don't know the database module well enough to offer any helpful comment, but if your work can bring support for true prepared statements in Kohana, it would be very beneficial to all of us and I hope it will be considered for inclusion...thank you.

    ...btw is there a reason why the database module isn't solely based on PDO ?
  • In general I think the Kohana 3 database module is pretty good. For example the query builder has been decoupled from the database (connection) code (from KO2), allowing for separate (and potentially concurrent) assembly of query components.

    But the development team has declined to pursue my prototype ideas. See the issue entry in the kohana development subsite for details. That's their prerogative.

    I think the reason for lack of in depth PDO support is probably mostly historical. The PHP PDO abstraction has taken a while to mature, and many of the PDO drivers are still flagged as experimental, whereas the Mysql and Mysqli interfaces are well established. But you'd have to ask the dev team.

    The prototype I've developed seems functional (though I wouldn't put it into production yet!), and over the next weeks and months I'll be implementing it and refining it further. When I have something additional to share I'll post an update here. Or of course anyone is free to build on the work, or any of its parts. It would be particularly interesting to see whether it helps simplify other database connections (such as PostgreSQL).

    Personally I'm betting that in the long and even medium term use of the PDO abstraction is going to simplify my life, so that I can focus more of my time on browser based database application development. I seem to be stuck building a lot of tools. We'll see... (but thank goodness for the Kohana framework!!)

    - Henrik

    ps. here's the revised database config file to deal with the Operation class, the abstract Operation class, and the concrete Operation_PDO class (all prototypes). Not that much to it, but ties up a loose end. I've opted to have the pdo class return Operation only for prepare/execute for now (leaving numerical results for update, delete, and insert for compatibility with current approach for query calls). In principle, however, pdo would return one of two class objects in all cases: Result for Select Queries, and Operation for Insert, Delete or Update queries.
  • Prepared statements as they are emulated in Kohana right now are useful but don't offer the performance benefit that one usually expects from true prepared statements...and as far as I can remember the way parameters replacement is implemented is based on search and replace in a query string...security-wise I'm not sure that is equivalent to true prepared statements...

    Edit : strtr doesn't seem to have the same gotcha as str_replace so my worries about security were probably not justified
  • parameters replacement is implemented is based on search and replace in a query string

    What happens when a query is compiled and parameters have been bound is:

    1. All parameters are escaped using the normal method
    2. strtr is called to replace all parameter keys with the escaped values

    In theory, this has equivalent security to prepared statements, as long as the PHP extension works properly. ;)

  • @shadowhand:

    ... but note that the kohana method by definition avoids low level optimization opportunities provided by PDO prepare() (that may be available depending on driver) - see http://php.net/manual/en/pdo.prepare.php.

    Also note that the PDO documentation strongly recommends using prepare() instead of PDO::quote (used by Kohana $db->escape() I believe). see http://www.php.net/manual/en/pdo.quote.php.

    I don't mean to be argumentative here, I just think that sooner or later people developing industrial strength database applications with kohana are going to want to push this level of system service as low (therefore as fast and hopefully secure) as possible.

    None of this is a showstopper for most things.

    - Henrik
  • Henrik is quite right, and his candidate PDO implementation is very welcome. The current scripted simulation of parameter binding is not remotely comparable to how genuine prepared statements work, in terms of either query optimization or protection from injection attacks.
  • Nothing is stopping you from using PDO without the Kohana layer, or using the query builder to create SQL to pass through to PDO. The various Database classes need to have a similar API, so we can't make things work differently just for PDO.

  • I'm not sure shadowhand from your comment - or from the comment on the issues page - whether you've actually taken the time to read through Henrik's proposal. He's suggesting integration with the existing layer in a perfectly sensible way, it seems to me. Allowing use of prepared statements throughout where these are supported would fill a gap in Kohana, since they're increasingly relevant ITRW for MySQL and SQLite 3 usage - not to mention transactions and the rest.
  • You might be interested in Chris' real-database module. It has support for prepared statements, and more complete pdo support. The api is totally different though, so you can't use it with existing database code.
  • That's a very nice module, thanks for the link Isaiah. Quite an interesting approach.
  • Nice work. I ran into a need for prepared statements awhile ago myself as many have. My solution is admittedly very bare bones. I simply extended the Database_PDO with a prepare function. Then I set the new connection type to use the extension. Just thought I'd toss this out there for those not needing more. Thanks for filling a hole quite a few of us saw as needed, Henrik.

    Extended_Database_PDO:

    final public function prepare($statement, array $driver_options = array())
    {
    // Make sure the database is connected
    $this->_connection or $this->connect();

    return $this->_connection->prepare($statement, $driver_options);
    }


    and here is an example of its usage with input and out params

    /**
    * Attempt to validate the login attempt
    * If the Email Address or IP is currently banned the method will return False
    *
    * @param string $email
    * @param string $ip_address
    * @return boolean
    */
    private function _check_login_attempt($email, $ip_address)
    {
    // Check to see if the User is temporarily locked
    $statement = $this->db->prepare('call check_login_attempt(?,?,@status)');
    $statement->bindParam(1, $email);
    $statement->bindParam(2, $ip_address);
    $statement->execute();

    $cachedResult = $this->db->query(Database::SELECT, 'SELECT @status', TRUE);
    return $cachedResult->get('@status') == 'SUCCESS' ? true : false;
    }

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion