Creating MySQL Database and Table

1. Login to phpMyAdmin (http://localhost/phpmyadmin/) in your browser and create a new database named androiddeft (if not present already).

2. Run the below SQL to create the table named member:

member.sql

--------------

--

-- Database: `androiddeft`

--

-- --------------------------------------------------------

--

-- Table structure for table `member`

--

CREATE TABLE `member` (

  `user_id` int(11) NOT NULL,

  `username` varchar(50) NOT NULL,

  `full_name` varchar(50) NOT NULL,

  `password_hash` varchar(256) NOT NULL,

  `salt` varchar(256) NOT NULL,

  `created_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--

-- Indexes for dumped tables

--

--

-- Indexes for table `member`

--

ALTER TABLE `member`

  ADD PRIMARY KEY (`user_id`),

  ADD UNIQUE KEY `username` (`username`);

--

-- AUTO_INCREMENT for dumped tables

--

--

-- AUTO_INCREMENT for table `member`

--

ALTER TABLE `member`

  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;

-- --------------------------------------------------------

Scripting Backend PHP

1. Open www folder of WAMP server and create a new folder named member. This folder will house all the PHP files used in this tutorial.

2. Now create a sub-folder named db inside member folder.

3. Create a PHP file called db_connect.php and add the following code. This will help in establishing the connection to the MySQL database. You may update database details accordingly.

db_connect.php

--------------------

<?php

define('DB_USER', "root"); // db user

define('DB_PASSWORD', ""); // db password (mention your db password here)

define('DB_DATABASE', "androiddeft"); // database name

define('DB_SERVER', "localhost"); // db server

$con = mysqli_connect(DB_SERVER,DB_USER,DB_PASSWORD,DB_DATABASE);

// Check connection

if(mysqli_connect_errno())

{

 echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

?>

4. Create a file named functions.php inside the member folder. This contains functions for checking whether the user exists not, functions for generating password salt and hashes. Here I have made use of random salt generator so that a unique salt and hash is generated for each user even if 2 people share the same password. This adds an additional level of security and prevents hackers from making use of reverse lookup tables.

functions.php

----------------

<?php

$random_salt_length = 32;

/**

* Queries the database and checks whether the user already exists

* 

* @param $username

* 

* @return

*/

function userExists($username){

 $query = "SELECT username FROM member WHERE username = ?";

 global $con;

 if($stmt = $con->prepare($query)){

  $stmt->bind_param("s",$username);

  $stmt->execute();

  $stmt->store_result();

  $stmt->fetch();

  if($stmt->num_rows == 1){

   $stmt->close();

   return true;

  }

  $stmt->close();

 }

 return false;

}

/**

* Creates a unique Salt for hashing the password

* 

* @return

*/

function getSalt(){

 global $random_salt_length;

 return bin2hex(openssl_random_pseudo_bytes($random_salt_length));

}

/**

* Creates password hash using the Salt and the password

* 

* @param $password

* @param $salt

* 

* @return

*/

function concatPasswordWithSalt($password,$salt){

 global $random_salt_length;

 if($random_salt_length % 2 == 0){

  $mid = $random_salt_length / 2;

 }

 else{

  $mid = ($random_salt_length - 1) / 2;

 }

 return

 substr($salt,0,$mid - 1).$password.substr($salt,$mid,$random_salt_length - 1);

}

?>

5. Create another PHP file called register.php. This takes username, password and full name as parameters checks whether the username is already taken. If not, it registers the user and prints a success response. If any errors occur, then it sets it in the response.

register.php

--------------

<?php

$response = array();

include 'db/db_connect.php';

include 'functions.php';

//Get the input request parameters

$inputJSON = file_get_contents('php://input');

$input = json_decode($inputJSON, TRUE); //convert JSON into array

//Check for Mandatory parameters

if(isset($input['username']) && isset($input['password']) && isset($input['full_name'])){

 $username = $input['username'];

 $password = $input['password'];

 $fullName = $input['full_name'];

 //Check if user already exist

 if(!userExists($username)){

  //Get a unique Salt

  $salt         = getSalt();

  

  //Generate a unique password Hash

  $passwordHash = password_hash(concatPasswordWithSalt($password,$salt),PASSWORD_DEFAULT);

  

  //Query to register new user

  $insertQuery  = "INSERT INTO member(username, full_name, password_hash, salt) VALUES (?,?,?,?)";

  if($stmt = $con->prepare($insertQuery)){

   $stmt->bind_param("ssss",$username,$fullName,$passwordHash,$salt);

   $stmt->execute();

   $response["status"] = 0;

   $response["message"] = "User created";

   $stmt->close();

  }

 }

 else{

  $response["status"] = 1;

  $response["message"] = "User exists";

 }

}

else{

 $response["status"] = 2;

 $response["message"] = "Missing mandatory parameters";

}

echo json_encode($response);

?>

6. Finally create a file for login, namely login.php. Here we compare the stored hash with the hash generated from the password entered by the user using the stored salt. If password matches then we set a success response and a failure response otherwise.

login.php

-------------

<?php

$response = array();

include 'db/db_connect.php';

include 'functions.php';

//Get the input request parameters

$inputJSON = file_get_contents('php://input');

$input = json_decode($inputJSON, TRUE); //convert JSON into array

//Check for Mandatory parameters

if(isset($input['username']) && isset($input['password'])){

 $username = $input['username'];

 $password = $input['password'];

 $query    = "SELECT full_name,password_hash, salt FROM member WHERE username = ?";

 if($stmt = $con->prepare($query)){

  $stmt->bind_param("s",$username);

  $stmt->execute();

  $stmt->bind_result($fullName,$passwordHashDB,$salt);

  if($stmt->fetch()){

   //Validate the password

   if(password_verify(concatPasswordWithSalt($password,$salt),$passwordHashDB)){

    $response["status"] = 0;

    $response["message"] = "Login successful";

    $response["full_name"] = $fullName;

   }

   else{

    $response["status"] = 1;

    $response["message"] = "Invalid username and password combination";

   }

  }

  else{

   $response["status"] = 1;

   $response["message"] = "Invalid username and password combination";

  }

  

  $stmt->close();

 }

}

else{

 $response["status"] = 2;

 $response["message"] = "Missing mandatory parameters";

}

//Display the JSON response

echo json_encode($response);

?>