Security
========
Allowing users of your website to communicate with a database can possibly have
security implications that you should be aware of. Databases allow very
powerful commands that not every user of your website should be able to
execute. Additionally the data in your database probably contains information
that should not be visible to everyone with access to the website.
The most dangerous security problem with regard to databases is the possibility
of SQL injections. An SQL injection security hole allows an attacker to
execute new or modify existing SQL statements to access information that he is
not allowed to access.
Neither Doctrine DBAL nor ORM can prevent such attacks if you are careless as a
developer. This section explains to you the problems of SQL injection and how
to prevent them.
SQL Injection: Safe and Unsafe APIs for User Input
--------------------------------------------------
A database library naturally falls touches the class of SQL injection security
vulnerabilities. You should read the following information carefully to
understand how Doctrine can and cannot help you to prevent SQL injection.
In general you should assume that APIs in Doctrine are not safe for user input.
There are however some exceptions.
The following APIs are designed to be **SAFE** from SQL injections:
- For ``Doctrine\DBAL\Connection#insert($table, $values, $types)``,
``Doctrine\DBAL\Connection#update($table, $values, $where, $types)`` and
``Doctrine\DBAL\Connection#delete($table, $where, $types)`` only the array
values of ``$values`` and ``$where``. The table name and keys of ``$values``
and ``$where`` are NOT escaped.
- ``Doctrine\DBAL\Query\QueryBuilder#setFirstResult($offset)``
- ``Doctrine\DBAL\Query\QueryBuilder#setMaxResults($limit)``
- ``Doctrine\DBAL\Platforms\AbstractPlatform#modifyLimitQuery($sql, $limit, $offset)`` for the ``$limit`` and ``$offset`` parameters.
Consider **ALL** other APIs to be not safe for user-input:
- Query methods on the Connection
- The QueryBuilder API
- The Platforms and SchemaManager APIs to generate and execute DML/DDL SQL statements
To escape user input in those scenarios use the ``Connection#quote()`` method.
User input in your queries
--------------------------
A database application necessarily requires user-input to passed to your queries.
There are wrong and right ways to do this and is very important to be very strict about this:
Wrong: String Concatenation
~~~~~~~~~~~~~~~~~~~~~~~~~~~
You should never ever build your queries dynamically and concatenate user-input into your
SQL or DQL query. For Example:
.. code-block:: php
prepare($sql);
$stmt->bindValue(1, $_GET['username']);
$stmt->execute();
// SQL Prepared Statements: Named
$sql = "SELECT * FROM users WHERE username = :user";
$stmt = $connection->prepare($sql);
$stmt->bindValue("user", $_GET['username']);
$stmt->execute();
// DQL Prepared Statements: Positional
$dql = "SELECT u FROM User u WHERE u.username = ?1";
$query = $em->createQuery($dql);
$query->setParameter(1, $_GET['username']);
$data = $query->getResult();
// DQL Prepared Statements: Named
$dql = "SELECT u FROM User u WHERE u.username = :name";
$query = $em->createQuery($dql);
$query->setParameter("name", $_GET['username']);
$data = $query->getResult();
You can see this is a bit more tedious to write, but this is the only way to write secure queries. If you
are using just the DBAL there are also helper methods which simplify the usage quite alot:
.. code-block:: php
executeQuery($sql, array($_GET['username']));
There is also ``executeUpdate`` which does not return a statement but the number of affected rows.
Besides binding parameters you can also pass the type of the variable. This allows Doctrine or the underyling
vendor to not only escape but also cast the value to the correct type. See the docs on querying and DQL in the
respective chapters for more information.
Right: Quoting/Escaping values
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Although previously we said string concatenation is wrong, there is a way to do it correctly using
the ``Connection#quote`` method:
.. code-block:: php
quote($_GET['username'], \PDO::PARAM_STR);
This method is only available for SQL, not for DQL. For DQL it is always encouraged to use prepared
statements not only for security, but also for caching reasons.
Non-ASCII compatible Charsets in MySQL
--------------------------------------
Up until PHP 5.3.6 PDO has a security problem when using non ascii compatible charsets. Even if specifying
the charset using "SET NAMES", emulated prepared statements and ``PDO#quote`` could not reliably escape
values, opening up to potential SQL injections. If you are running PHP 5.3.6 you can solve this issue
by passing the driver option "charset" to Doctrine PDO MySQL driver. Using SET NAMES does not suffice!
.. code-block::
'pdo_mysql',
'charset' => 'UTF8',
));