Simplifying database interactions with Doctrine DBALTuesday 25 July by Bob. Since the beginning of this week, we've had a number of reports from Bolt developers who were getting the following errors after running a Composer update:. This error happens because the Doctrine project test eq dbol results updated the minimum requirement of their dbal doctrine update to PHP 7. If you have a deploy process where you run composer update on the Development environment with one version of PHP, and composer install on Production with an earlier version of PHP, this sudden change might catch you off-guard. Regardless of our opinion that this aggressive push for PHP 7.
SQL Query Builder - Database Abstraction Layer (DBAL) - Doctrine
Using a database implies retrieval of data. It is the primary use-case of a database. For this purpose each database vendor exposes a Client API that can be integrated into programming languages. However because of disagreements between the PHP community there are often native extensions for each database vendor that are much more maintained OCI8 for example.
The query method executes the SQL and returns a database statement object. A database statement object can be iterated to retrieve all the rows that matched the query until there are no more rows:. These three arguments and some more technical details hopefully convinced you to investigate prepared statements for accessing your database.
Consider the previous query, now parameterized to fetch only a single article by id. The reason is simple, the actual SQL query is not clearly separated from the input parameters. Prepared statements separate these two concepts by requiring the developer to add placeholders to the SQL query prepare which are then replaced by their actual values in a second step execute. Placeholders in prepared statements are either simple positional question marks?
You cannot mix the positional and the named approach. The approach using question marks is called positional, because the values are bound in order from left to right to any question mark found in the previously prepared SQL query.
That is why you specify the position of the variable to bind into the bindValue method:. Named parameters have the advantage that their labels can be re-used and only need to be bound once:. Support for positional and named prepared statements varies between the different database extensions.
There are essentially two different types of methods available on a statement. Methods for binding parameters and types and methods to retrieve data from a statement. If you are finished with binding parameters you have to call execute on the statement, which will trigger a query to the database. After the query is finished you can access the results of this query using the fetch API of a statement:. See the API section below on details how to use them.
Additionally there are lots of convenience methods for data-retrieval and manipulation on the Connection, which are all described in the API section below. Be aware this type conversion only works with Statement bindValue , Connection executeQuery and Connection executeUpdate. It is not supported to pass a doctrine type name to Statement bindParam , because this would not work with binding by reference.
One rather annoying bit of missing functionality in SQL is the support for lists of parameters. You cannot bind an array of values into a single prepared statement parameter.
Consider the following very common SQL statement:. Since you are using an IN expression you would really like to use it in the following way and I guess everybody has tried to do this once in his life, before realizing it doesn't work:.
Implementing a generic way to handle this kind of query is tedious work. This is why most developers fallback to inserting the parameters directly into the query, which can open SQL injection possibilities if not handled carefully.
Doctrine DBAL implements a very powerful parsing process that will make this kind of prepared statement possible natively in the binding type system. The parsing necessarily comes with a performance overhead, but only if you really use a list of parameters. There are two special binding types that describe a list of integers or strings:. Using one of these constants as a type you can activate the SQLParser inside Doctrine that rewrites the SQL and flattens the specified values into the set of parameters.
Consider our previous example:. It is internally rewritten to look like the following explicit code that could be specified as well:. The DBAL contains several methods for executing queries against your configured database for data retrieval and manipulation. Below we'll introduce these methods and provide some examples for each of them.
Executes a prepared statement with the given SQL and parameters and returns the affected rows count:. See the Types section for more information.
Creates a prepared statement for the given SQL and passes the parameters to the execute method, then returning the statement:. Escaping is a very tricky business to do automatically, therefore there is none by default. The ORM internally escapes all your values, because it has lots of metadata available about the current context. The following methods help you with it:. Table of Contents master 2. Data Retrieval Using a database implies retrieval of data.
Start writing an SQL query and pass it to the query method of your connection: A database statement object can be iterated to retrieve all the rows that matched the query until there are no more rows: This can easily lead to a category of security holes called SQL injection , where a third party can modify the SQL executed and even execute their own queries through clever exploiting of the security hole.
Databases optimize SQL queries before they are executed. Using the query method you will trigger the optimization process over and over again, although it could re-use this information easily using a technique called prepared statements. Dynamic Parameters and Prepared Statements Consider the previous query, now parameterized to fetch only a single article by id.
That is why you specify the position of the variable to bind into the bindValue method: This is a Doctrine 2.