How to prevent SQL injection PHP and MySQL?

[crf1121359]

I have been reading about SQL injection and I want to secure my code.

I am not asking anyone to write me a code, but I just want to learn it in simple terms. The best way for me to learn is to edit my code so I can compare them.

For example, how secure is this code and if not, how can I make a secure?

 

<?php
if (isset ($_POST['email'])) {
//Connect to the database through our include
include_once "config/connect.php";
$email = stripslashes($_POST['email']);
$email = strip_tags($email);
$email = mysqli_real_escape_string($db_conx, $email);
$password = preg_replace("[^A-Za-z0-9]", "", $_POST['password']); // filter everything but numbers and letters
$password = md5($password);
// Make query and then register all database data that -
// cannot be changed by member into SESSION variables.
// Data that you want member to be able to change -
// should never be set into a SESSION variable.
$sql = "SELECT * FROM members WHERE email='$email' AND password='$password'";
$query = mysqli_query($db_conx, $sql);
$login_check = mysqli_num_rows($query);
if($login_check > 0){
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
// Get member ID into a session variable
$id = $row["id"];
session_register('id');
$_SESSION['id'] = $id;
// Get member username into a session variable
$username = $row["username"];
$email = $row["email"];
$password = $row["password"];
$firstname = $row["firstname"];
$lastname = $row["lastname"];
session_register('username');
session_register('firstname');
session_register('lastname');
// Update last_log_date field for this member now
$sql = "UPDATE members SET lastlogin=now() WHERE id='$id'";
$query = mysqli_query($db_conx, $sql);
// Print success message here if all went well then exit the script
header("location: members/index.php?id=$id");
exit();
} // close while
} else {
// Print login failure message to the user and link them back to your login page
header("location: login.php");
exit();
}
}
?>

 

 

Thanks in advance

[Ch0cu3r]

You can protect your self from SQL injection by sanitizing the input to make it safe to be handled in a query,

 

PHP has functions to help you with this such as mysqli_real_escape_string(), which are using. But mysqli has another feature to help protect SQL injection and that is prepared queries. With prepared queries the input you use in the query is never treated as SQL code, it is only treated as the value and so it helps to reduce the risk of SQL injection even further.

 

This is wrong

$password = preg_replace("[^A-Za-z0-9]", "", $_POST['password']); // filter everything but numbers and letters
$password = md5($password);

You should not care what characters a user uses for their password, once you have hashed the password the returned hash will always be made up of alphanumeric characters. Stripping characters from passwords makes them even weaker, which makes an attackers job even easier to crack with rainbow tables.

 

But the main argument is md5 should not be used for hashing passwords any more. You should use PHP's password_hash function for handling passwords (or use ircmaxwells password compatibility library if you are not using PHP5.5). 

 

Next the use of session_*_register() type functions is deprecated and should not be used at all. When setting session value you  use the $_SESSION superglobal array like any other array. Eg when adding value it'll be

$_SESSION['key_name'] = $var_name; // adding value to session

When checking if a session value exists you use isset

if(isset($_SESSION['key_name']))
{
    // session token exists
}

And where ever you use sessions ensure you have already started the session before using $_SESSION.