“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.
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:
Behind the Curtain:
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: