How can I improve/secure my login script and how to check for any possible injection?
PS. the script must run on multiple platforms, so I need empty arrays for cases such as the Android ones.
UPDATES
- mysql structure
- php switch cases instead of UNION
user_table:
CREATE TABLE `user_table` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`user_type` varchar(255) NOT NULL,
`user_email` varchar(255) NOT NULL,
`user_pass ` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=latin1
| Field |
Type |
Null |
Key |
Default |
Extra |
| user_id |
int(11) |
NO |
PRI |
NULL |
auto_increment |
| user_type |
varchar(255) |
NO |
|
NULL |
|
| user_email |
varchar(255) |
NO |
|
NULL |
|
| user_pass |
varchar(255) |
NO |
|
NULL |
|
student_table:
CREATE TABLE `student_table` (
`user_id` int(11) NOT NULL,
`user_type` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
| Field |
Type |
Null |
Key |
Default |
Extra |
| user_id |
int(11) |
NO |
PRI |
NULL |
|
| user_type |
varchar(255) |
NO |
|
NULL |
|
teacher_table:
CREATE TABLE `teacher_table` (
`user_id` int(11) NOT NULL,
`Class` varchar(255) NOT NULL,
`DEPARTMENT` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
| Field |
Type |
Null |
Key |
Default |
Extra |
| user_id |
int(11) |
NO |
PRI |
NULL |
|
| Class |
varchar(255) |
NO |
|
NULL |
|
| DEPARTMENT |
varchar(255) |
NO |
|
NULL |
|
management_table:
CREATE TABLE `management_table` (
`user_id` int(11) NOT NULL,
`Class` varchar(255) NOT NULL,
`DEPARTMENT` varchar(255) NOT NULL,
`HEAD` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
| Field |
Type |
Null |
Key |
Default |
Extra |
| user_id |
int(11) |
NO |
PRI |
NULL |
|
| Class |
varchar(255) |
NO |
|
NULL |
|
| DEPARTMENT |
varchar(255) |
NO |
|
NULL |
|
| HEAD |
varchar(255) |
NO |
|
NULL |
|
user_status table:
CREATE TABLE `user_status` (
`user_id` int(11) NOT NULL,
`Login_id` int(11) NOT NULL,
`user_token` varchar(255) NOT NULL,
`user_status` varchar(255) NOT NULL,
`time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
| Field |
Type |
Null |
Key |
Default |
Extra |
| user_id |
int(11) |
NO |
|
NULL |
|
| Login_id |
int(11) |
NO |
PRI |
NULL |
auto_increment |
| user_token |
varchar(255) |
NO |
|
NULL |
|
| user_status |
varchar(255) |
NO |
|
NULL |
|
| time_stamp |
timestamp |
NO |
|
CURRENT_TIMESTAMP |
on update CURRENT_TIMESTAMP |
login script:
<?php
//filter email var before connecting to database
function validateEmail($email) {
if(filter_var($email, FILTER_VALIDATE_EMAIL)) {
//echo "email is valid";
}
else {
//echo "Email not valid";
exit;
}
}
//connect to database
function db_connect($db_name, $db_username, $db_password)
{
$conn = new PDO($db_name, $db_username, $db_password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
return $conn;
}
// check login credentials
function userLogin($email, $password,$PDO)
{
$stmt = $PDO->prepare("
SELECT user_table.user_id ,user_table.user_pass
FROM user_table
WHERE user_table.user_email = :EMAIL");
$stmt->bindParam(':EMAIL', $email);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$hash = $row['user_pass'];
$returnApp = array( 'LOGIN' => 'Wrong_password_email');
if (!empty($row) && password_verify($password, $hash))
{
$user_id = $row['user_id'];
return $user_id;
}
else{
return $returnApp;
}
}
//guidv4
function guidv4($data = null) {
$data = $data ?? random_bytes(16);
assert(strlen($data) == 16);
$data[6] = chr(ord($data[6]) & 0x0f | 0x40);
$data[8] = chr(ord($data[8]) & 0x3f | 0x80);
return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4));
}
//create token
function createtoken($user_id,$user_online,$user_token,$PDO){
$sql_insert = "INSERT INTO user_status
(user_id, user_token,user_status)
VALUES
(:ID,:TOKEN,:ONLINE );";
$stmt = $PDO->prepare($sql_insert);
$stmt->bindParam(':ID', $user_id);
$stmt->bindParam(':ONLINE', $user_online);
$stmt->bindParam(':TOKEN', $user_token);
if ($stmt->execute()){
}else{
}
}
//getting user type
function usertype($user_id,$PDO){
$sql_select ="SELECT user_table.user_type AS user
FROM user_table
WHERE user_table.user_id = :USER_ID";
$stmt = $PDO->prepare($sql_select);
$stmt->bindParam(':USER_ID', $user_id);
if ($stmt->execute()) {
$row = $stmt->fetch(PDO::FETCH_ASSOC);
return $row;
}else{
}
}
//specifyuserquery
function specifyuser($user_type){
foreach ($user_type as $key) {
switch ($key) {
case 'STUDENT':
$query = "
SELECT
student_table.user_type AS type,
user_status.login_id AS id,
user_status.user_token AS Token
FROM student_table
LEFT JOIN user_status ON user_status.user_id = student_table.user_id
WHERE student_table.user_id = :USERID ";
return $query;
case 'TEACHER':
$query = "
SELECT
teacher_table.Class AS CL,
teacher_table.DEPARTMENT AS DEP,
user_status.login_id AS ID,
user_status.user_token AS TOKEN
FROM teacher_table
LEFT JOIN user_status ON user_status.user_id = teacher_table.user_id
WHERE teacher_table.user_id = :USERID ";
return $query;
case 'MANAGEMENT':
$query = "
SELECT
management_table.CLASS AS CL,
management_table.DEPARTMENT AS DEP,
management_table.HEAD AS HEAD,
user_status.login_id AS ID,
user_status.user_token AS TOKEN
FROM management_table
LEFT JOIN user_status ON user_status .user_id = management_table.user_id
WHERE management_table.user_id = :USERID";
return $query;
}
}
}
//getdata
function getdata($query,$user_id,$PDO){
$stmt = $PDO->prepare($query);
$stmt->bindParam(':USERID', $user_id);
if ($stmt->execute()) {
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$data = array( 'LOGIN' => 'Log_In_Success');
$final = array_merge($data, $row);
return $final;
}else{
}
}
$email = $_POST['email'];
//validate email
validateEmail($email);
// connect to data base
try
{
$PDO=db_connect("mysql:host=localhost;dbname=DATABASE", "root", "");
//echo "connection success";
}
catch (PDOException $e) {
//echo "Database error! " . $e->getMessage();
}
$password =$_POST['pass'];
//getting either user_id or email/pass dont match database
$result = userLogin($email,$password,$PDO);
$user_online = 'ONLINE';
$user_token = guidv4();
//if checks if email/pass is correct or error
if (ctype_digit($result)) {
//create token
$token = createtoken($result,$user_online,$user_token,$PDO);
//get type
$user_type = usertype($result,$PDO);
//specifyuser
$query =specifyuser($user_type);
//getdata
$data = getdata($query,$result,$PDO);
// merge data with type
$final = array_merge($data,$user_type);
echo json_encode($final);
}else{
echo json_encode($result);
exit;
}
?>
