SQL Injection – prevention methods in Yii Framework

Dream. Dare. Do – that is Suyati’s work principle in a nutshell.

May
05
2013
  • Author:
  • Arun Balakrishnan

“SQL injection”, the word is so familiar to the world of Web! What does it mean? It is a type of code injection that uses vulnerability at the database level and allows executing arbitrary SQL allowing malicious users to carry out such actions as deleting data or raising their privileges.

Here we will see few examples of vulnerable code and fix it.

Getting Ready

Be ready with your application by using yiic webapp!
Create and configure a new database and execute the SQL statement below:
sqlinj1
Now Generate a User model using Gii.
Once our model get ready, let’s follow the steps below:

First, we will implement a simple action that checks if the username and password that came from a URL are correct. Create file protected/controllers/SqlController.php and paste the below code:

sqlinj2

Now let’s try to access it using the /sql/simple?username=test&password=test URL. As we are aware of neither the username nor password, the out put will be “Failure”.

Again try another URL: /sql/simple?username=%27+or+%271%27%3D%271%27%3B+–&password=whatever. This time, it lets us in though we still don’t know anything about actual credentials. The decoded part of the username value looks like the following:

‘ or ‘1’=’1′; —

Close the quote, so that the syntax will stay correct. Add OR ‘1’=’1′ that makes the condition always true. Use ; — to end the query and comment the rest.As no escaping was done, the whole query executed was:

sqlinj3

Now let’s check, how we can fix it using a prepared statement as follows:

“Prepared statements is a way to declare parameters in your SQL. Depending on your configuration, the incomplete query will be compiled by the SQL server, then the values will be inserted at the right places”

sqlinj4

Now check /sql/prepared with the same malicious parameters. This time everything went fine and we have the “Failure” message. The same principle applies to Active Record. The only difference is that AR uses other syntax:
sqlinj5

In the preceding code, we used the :username and :password parameters and passed parameter values as a second argument. If we had written the preceding code by just using the first argument, it would be vulnerable:
sqlinj6

If used properly, prepared statements can save you from all types of SQL injections. Still there are some common problems:

  • You can bind only one value to a single parameter, so if you want to query WHERE IN(1, 2, 3, 4), you will have to create and bind four parameters.
  • Prepared statement cannot be used for table names, column names, and other keywords.

When using Active Record, the first problem can be solved by using the criteria addInCondition method as follows:
sqlinj7

The second problem can be solved in multiple ways. First is to rely on Active Record and PDO quoting:
sqlinj8

The second and the most secure way is using the whitelist approach as follows:
sqlinj9

Behind the Curtain:

  • The main goal when preventing the SQL injection is to properly filter the input. In all cases except table names, we have used prepared statements—a feature supported by most relational database servers. It allows us to build statements once and then use them multiple times and provides a safe way to bind parameter values.
  • In Yii, we can use prepared statements for both Active Record and DAO. When using DAO, it can be achieved by using either bindValue or bindParam. The latter is useful when we want to execute multiple queries of the same type while varying parameter values:

sqlinj10
Most Active Record methods accept either criteria or parameters. To be safe, you should use these instead of just passing the raw data in. As for quoting table names, columns, and other keywords, you can either rely on Active Record or use the whitelist approach.

To learn more about SQL injections and working with database through Yii, refer to the following URLs:

http://www.yiiframework.com/doc/api/CDbConnection
http://www.yiiframework.com/doc/api/CDbCommand

Leave a Comment

Your email address will not be published. Required fields are marked *