Raw SQL with Symfony 3

Symfony 3 and Raw SQL

PHP 7 has arrived and along with it Symfony 3, the latest version of Sensio Labs’ wildly popular framework. With contributors numbering over 2000 in this latest release it is shaping up to be one of the most powerful open source tools available to developers.

Symfony 3 takes full advantage of Doctrine when it comes to persisting and reading information to and from a database. Doctrine is a powerful tool for for object relational mapping and abstraction but sometimes this amazing tool isn’t the right tool for the job.

Consider a project where you are dealing with a database that has not been properly managed and contain multiple years worth of information. While the best practice would involve cleaning up the information contained within the database and employing recommended best practices this is not always a viable option due to project time constraints. In this case it becomes more ideal to use raw SQL rather than Symfony’s recommended best practices. Not to worry, Doctrine is completely decoupled from Symfony making the use of it entirely optional.

Configuring the Database

Configuring the database is simple. For the sake of this article we are going to assume that our connection is for the purpose of development in a single database clone provided by the client.

Symfony 3 configuration is done via YML in app->config. The first thing you will notice is that there are three configuration files: config.yml, config_dev.yml, and config_prod.yml. Symfony has the ability to define different execution configurations within the same code base but we don’t need to worry about that right now. If you have your Symfony application set for development it will use config_dev.yml by default.

The following lines will need to be inserted into your config_dev.yml to set up your database connection using the pdo_mysql driver:

# Doctrine Configuration
doctrine:
    dbal:
        driver:   pdo_mysql
        host:     “host_name”
        port:     "port"
        dbname:   “database_name”
        user:     "username"
        password: "password"
        charset:  UTF8
    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true

If you need to use multiple databases in your application it doesn’t take much more configuration. Let’s say you have a total of three databases that you will need to have access to.

# Doctrine Configuration
doctrine:
    dbal:
      default_connection: default
      connections:
        default:
          driver:   pdo_mysql
          host:     "host_name"
          port:     "port"
          dbname:   "database_name"
          user:     "username"
          password: "password"
          charset:  UTF8
        db2:
          driver:   pdo_mysql
          host:     "host_name"
          port:     "port"
          dbname:   "database_name"
          user:     "username"
          password: "password"
          charset:  UTF8
        db3:
          driver:   pdo_mysql
          host:     "host_name"
          port:     "port"
          dbname:   "database_name"
          user:     "username"
          password: "password"
          charset:  UTF8
    orm:
        auto_generate_proxy_classes: "%kernel.debug%"
        naming_strategy: doctrine.orm.naming_strategy.underscore
        auto_mapping: true

Raw SQL with PDO

With our connection configured we can start the process of actually making use of the information stored in our database. For this example we are going to assume that we are connecting to gather information on a specific user. The first step is to connect to the database that we configured for.

$conn = $this->get(‘doctrine.dbal.db2_connection’);

With our connection in place now we can actually access all of that information available to us.

Since we are connecting to a database that contains information on the users accessing our application it is important to do this as securely as possible. For this reason we are going to use prepared statements with bound variables. Doing this gives us added measures of protection against SQL injection that could wreck havoc on our application security and our database.

For our statement we are going to assume that we gathered this information from post data and set the variables used in the statement with that.

$username = $_POST[‘username’];
 
$sql = "SELECT * FROM users WHERE username = :username LIMIT 1";
$statement = $conn->prepare($sql);
$statement->bindValue("username", $username);
$statement->execute();
$result = $statement->fetchAll();

To understand what is happening here let’s take it line by line.

$sql = "SELECT * FROM users WHERE username = :username LIMIT 1”;

This is our raw SQL. If you are unfamiliar with this you should take a moment to brush up on your SQL syntax before continuing. You will notice that rather than place the variable we set for the username into the raw SQL we used the placeholder :username. This enables us to later bind a variable of our choosing to that placeholder but more on that later.

Let’s talk about our prepare first.

$statement = $conn->prepare($sql);

Using a prepared statement does a couple of things for us. First it increasing the efficiency of our application. Statements that are not prepared required that the database server parses the query when it receives it. Using a prepared statement is useful because it doesn’t put as much load on the database server and is especially useful when you are executing the same query multiple times such as in our example where we are getting information on a specific user.

Prepared statements also protect against injection attacks. (It is important to note that protects does not imply imperviousness.) This is because with a prepared statement escaping dangerous characters is left to the database which already knows which characters it should be escaping rather than trusting yourself to do it and possibly miss something. Preparing a statement ensures that it is sanitized before sending it to the database for execution

$statement->bindValue("username", $username);

This is where that placeholder we defined earlier gets put to work. bindValue() here tells the database that it should use the variable $username anywhere it sees the placeholder :username. It is important to note that you should only use the : with the placeholder in the SQL statement. Using it in bindValue() will create an exception error. Basically if you were to say bindValue(“:username”, $username); the database would expect ::username to be the placeholder in the statement.

$statement->execute();

This is what actually executes our statement and gathers our result data from the database.

Once we have gathered result data from the database it is time to interpret it. Since we want to get an array of all data connected to our user we are going to use fetchAll();

$result = $statement->fetchAll();

Let’s assume that in our data base we have a table called users that looks like this.

id username email password ipaddress
01 our_user [email protected] password 0.0.0.0

Our variable $result would be set to the following array:

array(
0 => array(
          'id' => '01',
          'username' => 'our_user',
          'email' => '[email protected]',
          'password' => 'password',
          'ipaddress' => '0.0.0.0',
          )
)

And with that we have executed a raw SQL statement securely and efficiently. From here you can manipulate the returned result however best suits the needs of your application.