security.rst 6.56 KB
Newer Older
1 2 3
Security
========

4 5 6 7 8
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.
9

10 11 12 13
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.
14

15 16 17 18 19 20 21
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
--------------------------------------------------

Phil Davis's avatar
Phil Davis committed
22
A database library naturally touches the class of SQL injection security
23 24 25 26
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.
Steve Müller's avatar
Steve Müller committed
27
There are however some exceptions.
28 29 30

The following APIs are designed to be **SAFE** from SQL injections:

31 32 33 34 35
- 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.
36 37 38 39 40 41 42 43 44
- ``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
45

46 47
To escape user input in those scenarios use the ``Connection#quote()`` method.

48 49 50
User input in your queries
--------------------------

Phil Davis's avatar
Phil Davis committed
51 52
A database application necessarily requires user-input to be passed to your queries.
There are wrong and right ways to do this and it is very important to be very strict about this:
53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

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

    <?php
    // Very wrong!
    $sql = "SELECT * FROM users WHERE name = '" . $_GET['username']. "'";

An attacker could inject any value into the GET variable "username" to modify the query to his needs.

Phil Davis's avatar
Phil Davis committed
68
Although DQL is a wrapper around SQL that can prevent some security implications, the previous
69 70 71
example is also a threat to DQL queries.

.. code-block:: php
72 73 74 75 76

    <?php
    // DQL is not safe against arbitrary user-input as well:
    $dql = "SELECT u FROM User u WHERE u.username = '" . $_GET['username'] . "'";

Ujjwal Ojha's avatar
Ujjwal Ojha committed
77
In this scenario an attacker could still pass a username set to ``' OR 1 = 1`` and create a valid DQL query.
78 79 80 81 82 83 84 85
Although DQL will make use of quoting functions when literals are used in a DQL statement, allowing
the attacker to modify the DQL statement with valid literals cannot be detected by the DQL parser, it
is your responsibility.

Right: Prepared Statements
~~~~~~~~~~~~~~~~~~~~~~~~~~

You should always use prepared statements to execute your queries. Prepared statements is a two-step
Phil Davis's avatar
Phil Davis committed
86
procedure, separating the SQL query from the parameters. They are supported (and encouraged) for both
87 88 89
DBAL SQL queries and for ORM DQL queries.

Instead of using string concatenation to insert user-input into your SQL/DQL statements you just specify
Phil Davis's avatar
Phil Davis committed
90
placeholders and then explain to the database driver which variable should be bound to
91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
which placeholder. Each database vendor supports different placeholder styles:

-  All PDO Drivers support positional (using question marks) and named placeholders (:param1, :foo, :bar).
-  OCI8 only supports named parameters, but Doctrine DBAL has a thin layer around OCI8 and
   also allows positional placeholders.
-  Doctrine ORM DQL allows both named and positional parameters. The positional parameters however are not
   just question marks, but suffixed with a number (?1, ?2, ?3, ...).

Following are examples of using prepared statements with SQL and DQL:

.. code-block:: php

    <?php
    // SQL Prepared Statements: Positional
    $sql = "SELECT * FROM users WHERE username = ?";
    $stmt = $connection->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
129
are using just the DBAL there are also helper methods which simplify the usage quite a lot:
130 131 132 133 134 135 136 137

.. code-block:: php

    <?php
    // bind parameters and execute query at once.
    $sql = "SELECT * FROM users WHERE username = ?";
    $stmt = $connection->executeQuery($sql, array($_GET['username']));

138
There is also ``executeStatement`` which does not return a statement but the number of affected rows.
139

140
Besides binding parameters you can also pass the type of the variable. This allows Doctrine or the underlying
141 142 143 144 145 146 147 148 149 150 151 152 153
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

    <?php
    // Parameter quoting
154
    $sql = "SELECT * FROM users WHERE name = " . $connection->quote($_GET['username']);
155

Phil Davis's avatar
Phil Davis committed
156
This method is only available for SQL, not for DQL. For DQL you are always encouraged to use prepared
157
statements not only for security, but also for caching reasons.