PHP Anti-SQL injection

In a SQL injection attack, the user adds information to a database query by manipulating the form or GET query string. For example, suppose you have a simple login database. Each record in this database has a user name field and a password fields. Build a login form that allows users to log in.

Listing 5. Simple sign-in form

Login

This form accepts the user name and password entered by the user and submits the user input to a file named verify.php. In this file, PHP processes the data from the login form as follows:

Listing 6. Unsafe PHP Form processing code

$okay = 0; $username = $_post[' user ']; $PW = $_post[' pw ']; $sql = "SELECT count (*) as Ctr from users where Username= ' ". $username." ' and password= ' ". $PW. "' Limit 1 '; $result = mysql_query ($sql); while ($data = Mysql_fetch_object ($result)) { if ($data->ctr = = 1) { They ' re okay to enter the application! $okay = 1; } } if ($okay) { $_session[' Loginokay ') = true; Header ("index.php"); }else{ Header ("login.php"); } ?>

This piece of code looks fine, doesn't it? This code is used by hundreds (or even thousands) of php/mysql sites around the world. Where is it wrong? OK, remember "cannot trust user input." There is no escaping any information from the user, so the application is vulnerable to attack. Specifically, there may be any type of SQL injection attack.

For example, if the user enters Foo as the user name and enters ' or ' 1 ' = ' 1 as the password, the following string is actually passed to PHP, and then the query is passed to MySQL:

$sql = "SELECT count (*) as Ctr from users where username= ' foo ' and password= ' or ' 1 ' = ' 1 ' limit 1";

This query always returns the count value of 1, so PHP will allow access. By injecting some malicious SQL at the end of the password string, the hacker can dress up as a legitimate user.

The solution to this problem is to use PHP's built-in mysql_real_escape_string () function as a wrapper for any user input. This function escapes characters in a string, making it impossible for strings to pass special characters such as apostrophes and letting MySQL operate on special characters. Listing 7 shows the code with escape processing.

Listing 7. Secure PHP Form processing code

$okay = 0; $username = $_post[' user ']; $PW = $_post[' pw ']; $sql = "SELECT count (*) as Ctr from users where Username= ' ". Mysql_real_escape_string ($username)." ' and password= ' ". Mysql_real_escape_string ($PW). "' Limit 1"; $result = mysql_query ($sql); while ($data = Mysql_fetch_object ($result)) { if ($data->ctr = = 1) { They ' re okay to enter the application! $okay = 1; } } if ($okay) { $_session[' Loginokay ') = true; Header ("index.php"); }else{ Header ("login.php"); } ?>

By using mysql_real_escape_string () as a wrapper for user input, you can avoid any malicious SQL injection in user input. If a user attempts to pass a malformed password through SQL injection, the following query is passed to the database:

Select COUNT (*) as Ctr from users where \username= ' foo ' and password= ' \ ' or \ ' 1\ ' =\ ' 1 ' limit 1 "

Nothing in the database matches such a password. Just taking a simple step is blocking a big hole in the Web application. The experience here is that the user input to the SQL query should always be escaped.

For your safety, please only open URLs with reliable sources

From: http://hi.baidu.com/luzheng22/blog/item/af49aca48ea018f19052eeea.html

