“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:
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:
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:
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”
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:
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:
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:
The second problem can be solved in multiple ways. First is to rely on Active Record and PDO quoting:
The second and the most secure way is using the whitelist approach as follows:
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:
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