where to use mysql_real_escape_string()??

[cluce]

after reading this other topic on a secure login I am questioning my security. I never knew about the mysql_real_escape_string() function or used it before. can someone tell me how I can add that function to my login page code??

<?php
//initialize the session
session_start();

//connect to server and select database
$mysqli = mysqli_connect("localhost", "root", "", "test");

//trims and strips tags
$checkuser = trim(strip_tags($_POST['username']));
$checkpassword = trim(strip_tags($_POST['password']));

//create and issue the query
$sql = "SELECT username, f_name, l_name FROM employees WHERE username = '$checkuser' AND password = '$checkpassword' LIMIT 1";
$result = mysqli_query($mysqli, $sql);

//gets number of unsuccessful logins
$sql1 = ("SELECT failed_logins FROM employees WHERE username = '$checkuser' LIMIT 1");
$result1 = mysqli_query($mysqli, $sql1);
$resultarr = mysqli_fetch_assoc($result1);
$attempts = $resultarr["failed_logins"];

//disables user if failed logins >= 3 
if ($attempts >= 3){

//records unsuccessful logins
$sql1 = "UPDATE employees SET failed_logins = failed_logins + 1 WHERE username = '$checkuser' LIMIT 1"; 
    mysqli_query($mysqli,$sql1);

$_SESSION['disabled'] = "<font color='red'>Your account has been disabled.<br>Please contact the MIS department.</font>";
header("Location: employee_resource.php");

//close connection to MySQL
mysqli_close($mysqli);
exit();
} else {

//get the number of rows in the result set; should be 1 if a match
if (mysqli_num_rows($result) == 1) {

//if authorized, get the values of f_name l_name
while ($info = mysqli_fetch_array($result)) {
	$f_name = stripslashes($info['f_name']);
	$l_name = stripslashes($info['l_name']);
}
//set authorization cookie
setcookie("auth", "1", 0, "/", "dom.com", 0);
$_SESSION['usersname'] = $f_name . " " . $l_name;

//get last successful login
$last_login = ("SELECT DATE_FORMAT(last_login,  '%b %e %Y at %r') aS last_login FROM employees WHERE username = '$checkuser' LIMIT 1");
$result = mysqli_query($mysqli, $last_login);
$result_login = mysqli_fetch_assoc($result);
$_SESSION['login'] = $result_login["last_login"];

//record last login
    $sql2 = "UPDATE employees SET last_login=NOW() WHERE username = '$checkuser' LIMIT 1";   
    mysqli_query($mysqli,$sql2);

//clears failed logins
$sql3 = "UPDATE employees SET failed_logins = 0 WHERE username = '$checkuser' LIMIT 1";
mysqli_query($mysqli, $sql3);

//sets session to authenticate
$_SESSION['loggedin_e'] = "yes";
  
//sets session to identify
$_SESSION['identity'] = $checkuser;

//close connection to MySQL
mysqli_close($mysqli);

//sets login timer
$current_time = time(); // get the current time
    $_SESSION['loginTime']=$current_time; // login time
    $_SESSION['lastActivity']=$current_time; // last activity

//directs authorized user
header("Location: resource.php");
exit(); 
} else {

//records unsuccessful logins
$sql4 = "UPDATE employees SET failed_logins = failed_logins + 1 WHERE username = '$checkuser' LIMIT 1"; 
    mysqli_query($mysqli,$sql4);

//stores a session error message
$_SESSION['error'] =  "<font color='red'>Invalid username and/or password combination<br>Please remember that your password is case sensitive.</font>"; 
	  
  	//close connection to MySQL
mysqli_close($mysqli);

//redirect back to login form if not authorized
header("Location: employee_resource.php");
exit;
}
}
?>

 

 

[trq]

You should run all user inputted data through mysql_real_escape_string. Simple.

[per1os]

The big thing is making sure you do not double escape. Here is a function created to check that

 

<?php
function real_escape($string) {
       return  get_magic_quotes_gpc()?mysql_real_escape_string(stripslashes($string)):mysql_real_escape_string($string);
}
?>

 

Usage

 

<?php
//initialize the session
session_start();

// put here for demo purposes.
function real_escape($string) {
       return  get_magic_quotes_gpc()?mysql_real_escape_string(stripslashes($string)):mysql_real_escape_string($string);
}

//connect to server and select database
$mysqli = mysqli_connect("localhost", "root", "", "test");

//trims and strips tags
$checkuser = real_escape(trim(strip_tags($_POST['username'])));   // note here
$checkpassword = real_escape(trim(strip_tags($_POST['password']))); // note here

//create and issue the query
$sql = "SELECT username, f_name, l_name FROM employees WHERE username = '$checkuser' AND password = '$checkpassword' LIMIT 1";
$result = mysqli_query($mysqli, $sql);

//gets number of unsuccessful logins
$sql1 = ("SELECT failed_logins FROM employees WHERE username = '$checkuser' LIMIT 1");
$result1 = mysqli_query($mysqli, $sql1);
$resultarr = mysqli_fetch_assoc($result1);
$attempts = $resultarr["failed_logins"];

//disables user if failed logins >= 3 
if ($attempts >= 3){

//records unsuccessful logins
$sql1 = "UPDATE employees SET failed_logins = failed_logins + 1 WHERE username = '$checkuser' LIMIT 1"; 
    mysqli_query($mysqli,$sql1);

$_SESSION['disabled'] = "<font color='red'>Your account has been disabled.<br>Please contact the MIS department.</font>";
header("Location: employee_resource.php");

//close connection to MySQL
mysqli_close($mysqli);
exit();
} else {

//get the number of rows in the result set; should be 1 if a match
if (mysqli_num_rows($result) == 1) {

//if authorized, get the values of f_name l_name
while ($info = mysqli_fetch_array($result)) {
	$f_name = stripslashes($info['f_name']);
	$l_name = stripslashes($info['l_name']);
}
//set authorization cookie
setcookie("auth", "1", 0, "/", "dom.com", 0);
$_SESSION['usersname'] = $f_name . " " . $l_name;

//get last successful login
$last_login = ("SELECT DATE_FORMAT(last_login,  '%b %e %Y at %r') aS last_login FROM employees WHERE username = '$checkuser' LIMIT 1");
$result = mysqli_query($mysqli, $last_login);
$result_login = mysqli_fetch_assoc($result);
$_SESSION['login'] = $result_login["last_login"];

//record last login
    $sql2 = "UPDATE employees SET last_login=NOW() WHERE username = '$checkuser' LIMIT 1";   
    mysqli_query($mysqli,$sql2);

//clears failed logins
$sql3 = "UPDATE employees SET failed_logins = 0 WHERE username = '$checkuser' LIMIT 1";
mysqli_query($mysqli, $sql3);

//sets session to authenticate
$_SESSION['loggedin_e'] = "yes";
  
//sets session to identify
$_SESSION['identity'] = $checkuser;

//close connection to MySQL
mysqli_close($mysqli);

//sets login timer
$current_time = time(); // get the current time
    $_SESSION['loginTime']=$current_time; // login time
    $_SESSION['lastActivity']=$current_time; // last activity

//directs authorized user
header("Location: resource.php");
exit(); 
} else {

//records unsuccessful logins
$sql4 = "UPDATE employees SET failed_logins = failed_logins + 1 WHERE username = '$checkuser' LIMIT 1"; 
    mysqli_query($mysqli,$sql4);

//stores a session error message
$_SESSION['error'] =  "<font color='red'>Invalid username and/or password combination<br>Please remember that your password is case sensitive.</font>"; 
	  
  	//close connection to MySQL
mysqli_close($mysqli);

//redirect back to login form if not authorized
header("Location: employee_resource.php");
exit;
}
}
?>

 

Basically with the code above it checks if the magic_quotes are on (which escapes post data) if they are it strips the slashes from those and then it will escape with the mysql_real_escape function, if they are not on then it automatically escapes it.

 

Anyhow hope that helps.

[cluce]

really. so I guess this will workthan. I just wasnt sure because some examples I was looking at was more complex in using this function.

 

//trims and strips tags

$checkuser = mysql_real_escape_string(trim(strip_tags($_POST['username'])));

$checkpassword = mysql_real_escape_string(trim(strip_tags($_POST['password'])));

[cluce]

thanks a bunch. I got it now.