Custom Search

SQL Protection of injecting in PHP applications II

Dec 12, 2008 Author: City Hall

How to prevent SQL injection code?

The first rule is: Never trust in the correctness of user data entered! It's easy to say a little more complicated to make.

The easiest way to prevent SQL injections is to use the option of PHP "Magic Quotes" for the presentation of special characters (such as quotes, NULL, reverse slash, etc..) That are part of SQL syntax in the form of escape sequences . This option is provided as a means to prevent SQL injections, but not in all cases is effective and is unlikely to evolve into the next versions of PHP. The next examples do not guarantee 100 percent protection (if at all possible), but have a high degree of protection against SQL injections.

First we need to do is to remove '\' of all input parameters that may have put of Magic Quotes:

<? php
/ / There are magic quotes?
if (get_magic_quotes_gpc ())
(
     / / Yes. Remove added '\'
     $ _REQUEST = Array_map ( 'stripslashes', $ _REQUEST);
     $ _GET = Array_map ( 'stripslashes', $ _GET);
     $ _POST = Array_map ( 'stripslashes', $ _POST);
     $ _COOKIE = Array_map ( 'stripslashes', $ _COOKIE);
)
?>

instead use the capabilities of mysql_real_escape_string () in SQL query:

<? php
$ query = mysql_query ( "SELECT * FROM` users `"
. "WHERE` username `= '"
. mysql_real_escape_string ($ username). ' "
. "AND` password `= '"
. mysql_real_escape_string ($ password). "'");
?>

Let me change the format and method of GET to POST, although this is not a great defense. However you save surprised that the password you think explicit kind in the history of visited sites of some browsers. Also enter password field is normal to type password:

<? php
if (empty ($ _POST [ 'username'])) (
     echo "<form method='POST' action='login.php'>"
     . "Username: <input type='text' name='username' /> <br />"
     . "Password: <input type='password' name='password' /> <br />"
     . "<input type='submit' value='Login' />"
     . "</ form>";
)
$ username = $ _POST [ 'username'];
$ password = $ _POST [ 'password'];
?>

Finally, the script will look like this:

<?php
if (get_magic_quotes_gpc())
    $_REQUEST = array_map('stripslashes', $_REQUEST);
    $_GET = array_map('stripslashes', $_GET);
    $_POST = array_map('stripslashes', $_POST);
    $_COOKIE = array_map('stripslashes', $_COOKIE);
}

if(empty($_POST['username'])) {
    echo "<form method='POST' action='login.php'>"
    ."Username: <input type='text' name='username' /><br />"
    ."Password: <input type='text' name='password' /><br />"
    ."<input type='submit' value='Login' />"
    ."</form>";
}

$username = $_POST['username'];
$password = $_POST['password'];

$link = @mysql_connect($dbhost, $dbuname, $dbpassword)
    or die('Could not connect: ' . mysql_error());

mysql_select_db($mysqldb, $link)
    or die('Could not select database.');

$query = mysql_query("SELECT * FROM `users` "
. "WHERE `username` = '"
. mysql_real_escape_string($username) . "' "
. "AND `password` = '"
. mysql_real_escape_string($password) . "'");

$row = mysql_fetch_assoc($query);

if((mysql_num_rows($query) == 1) && ($password == $row['password'])) {
    echo "Hello {$row['username']}!<br />";
    echo "Your credit card number is: {$row['creditcard']}";
}
?>

Note the last "if ()". We expect only one line. This is fine, but the table with our desire would accept 2 lines with the same names and passwords. Just limit the uniqueness of values in the column "username" and checking for error (1062: "Unique constraint violation") on INSERT.

So login process is reliably protected from SQL injections.