Saturday, 14 July 2012

Secure Login Code using PHP and MySQL / Preventing SQL Injection using PHP

10 comments
Many Web pages accept parameters from web users and generate SQL queries to the database. SQL Injection is a trick to inject SQL script/command as an input through the web front end.

Your application may be susceptible to SQL Injection attacks when you incorporate invalidated user input into the database queries. Particularly susceptible is a code that constructs dynamic SQL statements with unfiltered user input.



Consider the following example code:
Sql DataAdapter myCommand = new SqlDataAdapter(
"Select * from Users
Where UserName = ' "+txtuid.Text+" ", conn);

Attackers can inject SQL by terminating the intended SQL statement with the single quote character  followed by a semicolon character to begin a new command and then executing the command to their choice. Consider the following character string entered into the .txtuid field.
' OR 1=1
This results in the following statement to be submitted to the database for execution:
SELECT * FROM Users WHERE UserName = ' ' OR 1 = 1;

Because 1=1 is always true, the attacker retrieves very row of data from the user table.

Now, to prevent such an attack, a secure login technique is required. Here, in this article, we discuss the coding of a secure login script using PHP and MySQL.


Step I: Create a database and a table 'members' in it:

CREATE TABLE `members` (
  `username` varchar(20),
  `password` varchar(128)
)



Step II: Create a Login Form:

<form action="process_login.php" method="post">
Username: <input type="text" name="username" /><br />
Password: <input type="password" name="password" /><br />
<input type="submit" value="Login" />
</form>


Connect to MySQL Server:

$host = 'localhost'; // Host name Normally 'LocalHost'
$user = 'root'; // MySQL login username
$pass = ''; // MySQL login password
$database = 'test'; // Database name
$table = 'members'; // Members name
 
mysql_connect($host, $user, $pass);
mysql_select_db($database);


Step III: Now, you need to provide mechanism to avoid SQL Injection. For this, escape special characters like ", ', \

We can escape special characters (prepend backslash) using mysql_real_escape_string or addslashes functions. In most cases PHP will this do automatically for you. But PHP will do so only if the magic_quotes_gpc setting is set to On in the php.ini file.
If the setting is off, we use mysql_real_escape_string function to escape special characters. If you are using PHP version less that 4.3.0, you can use the addslashes function instead.

name = mysql_real_escape_string($_POST['username']);
$password = md5($_POST['password']);
 
$result = mysql_query("SELECT * FROM $table WHERE username = '$username' AND password = '$password'
");


Here, we use the MD5(Message Digest 5) Algorithm, that generates the message digest for the password. So, while writing the script for registration page, care must be taken that the md5 of the password entered by the user must be stored in the database, instead of the actual text password.

Validating the login:

if(mysql_num_rows($result))
{
  // Login
  session_start();
  $_SESSION['username'] = htmlspecialchars($username); 
}
else
{
  // Invalid username/password
  echo '<p><strong>Error:</strong> Invalid username or password.</p>';
}
 
// Redirect
header('Location: http://www.example.com/loggedin.php');
exit;


You are done!! This code will help prevent the SQL injection problem. However, it must be noted that no script is 100% secure. So, it is advisable to provide multilevel security process, which make the login more secure.


Kindly Bookmark and Share it:

10 comments:

  1. Great post indeed and thanks for all the information, it was very helpful i really like that you are providing information on PHP and MYSQL with basic JAVASCRIPT,being enrolled in http://www.wiziq.com/course/5871-php-mysql-with-basic-javascript-integrated-course i was looking for such information online to assist me on php and mysql and your information helped me a lot. Thanks.

    ReplyDelete
  2. Thanks for your comment...glad you found my blog useful!!

    ReplyDelete
  3. Thanks for your such help.Ill use it in my login module.

    ReplyDelete
  4. Glad you found the post useful:-) Keep visiting for more such coding stuff!!

    ReplyDelete
  5. Really like that you are providing such information on PHP MYSQl with JAVASCRIPT ,being enrolled at http://www.wiziq.com/course/5871-php-mysql-with-basic-javascript-integrated-course i really thank you for providing such information it was helpful.

    ReplyDelete
  6. Thanks Ajinkya! Apparently it's not just escaping strings that prevents sql injection, but I think even more effective is using prepared statements as discussed here:

    http://www.programmerinterview.com/index.php/database-sql/sql-injection-prevention/

    ReplyDelete
  7. nice post!found it helpful.Want to know about 2 way user authentication.Sir can you help????

    ReplyDelete
    Replies
    1. Hi...by 2 way authentication, here is what I assume you are asking for:
      The User first enters his credentials partly, as in just the username...In response, he receives some unique reply from the server, for example, an image that he may have previously set. When the User confirms, that the reply from the server is on expected lines, he then enters the remaining credentials, password in this case, which are then authenticated by the server.

      If this is what you desire, here is what you can do:
      During registration, you can ask the user to select a unique image, or some unique Text, which can help prove the authenticity of the site.
      You can then save this unique thing into the database.
      Now, when the user wants to login, he will, in the 1st step enter just the Username.
      You can then query your database, and retrieve the unique field corresponding to that username. Now, all the user has to do is tick a checkbox alongside the unique field you display, and enter his password.
      You can then, using a server side scripting language, check if the checkbox is ticked, & then compare the passwords.
      By this way, both the Server & the User are assured of the authenticity of each other.

      Delete
  8. Thanks for sharing, I will bookmark and be back again





    PHP Course in Chennai

    ReplyDelete
  9. Good Post!!!
    Visitors might also be interested in Encryption algorithms.
    Here's the link to get different encryption algos
    http://adityamandhare.blogspot.in/2013/08/system-security-c-code-for-playfair.html
    http://adityamandhare.blogspot.in/2013/07/system-security-c-code-for-vigenere.html
    http://adityamandhare.blogspot.in/2013/07/system-security-java-code-for-hill.html

    ReplyDelete

Subscribe to Get Latest Tutorial via email