Preventing SQL injections

SQL injection 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.

In this recipe, we will see examples of vulnerable code and fix them.

Getting ready

1. Create a fresh application by using yiic webapp.

2. Create and configure a new database.

3. Execute the following SQL:

   CREATE TABLE `user` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `username` varchar(100) NOT NULL,
     `password` varchar(32) NOT NULL,
     PRIMARY KEY (`id`)
   );
   INSERT INTO `user`(`id`,`username`,`password`) VALUES ( '1','Alex','202cb962ac59075b964b07152d234b70');
   INSERT INTO `user`(`id`,`username`,`password`) VALUES ( '2','Qiang','202cb962ac59075b964b07152d234b70');

4. Generate a User model using Gii.

How to do it...

1. First, we will implement a simple action that checks if the username and password that came from a URL are correct. Create protected/controllers...