2

I've been studying prepared statements and implemented it in my first login. I used a prepared statement for the SQL query and am wondering if this is enough to ward off most SQL injections?

Since the SQL query is parameterized and I've escaped the username I think it should be ok. The only real issue that I know of (if there are more, please let me know) is the fact that I couldn't escape the password input because of the way Bcrypt verifies it. Let me know if there is a better way of doing this.

<!Doctype html>
<html>
<head>
<title>password validation</title></head>
<body>


<fieldset>
<legend>enter password</legend>
<form action =passing.php method = "post">
<table>
<tr>
<td>Email:</td><td><input type="id" name ="id" /><br /></td>
</tr>
<tr>
<td>Password:</td><td><input type = "password" name="password" /><br /></td>
</tr>
</table>
<input type = "submit" name ="submit" value ="insert" />
</form>
</fieldset>
<br />


<?php
// connect to the server

$conn = new mysqli('localhost', 'root', '', 'social');

// check connection
if(mysqli_connect_errno()) {
exit("connection failed" . mysqli_connect_error());
 } else {
 echo "connection established";
 }


if($_POST && isset($_POST['submit'], $_POST['password'], $_POST['id'])) {

$pass = ($_POST["password"]);
$id = mysqli_real_escape_string($conn, $_POST["id"]);
$input = $pass;


$query = mysqli_prepare($conn, "SELECT pass FROM social
 WHERE  email LIKE ?");

mysqli_stmt_bind_param($query,'s', $id );

mysqli_stmt_execute($query);
    mysqli_stmt_bind_result($query, $id);



while(mysqli_stmt_fetch($query)) {

echo "<br />";
echo "SUCCESS at query";

if (password_verify($input, $id)) {
echo "matching pass" . header("Location: inserh.php");
} else{
echo "not a  match";

        }

    }
}

mysqli_close($conn);

// close the connection
  • 1
    Take a moment to read through the editing help in the help center. Formatting on Information Security is different than other sites. The better your post looks, the easier it is for others to read and understand it. – Tobi Nary Apr 7 '16 at 7:42
  • 1
    Please feel free to post your code at codereview. I answered your concrete questions here, but there is quite a bit that can be improved in your code, such as formatting, confusing variable names, confusing variable reuse, etc. Improving these things also means improved security, as good code makes it easier to understand what is going on, and easier to find bugs. – tim Apr 7 '16 at 9:48
5

Should I escape input when using prepared statements?

No.

You really don't need to escape input if you use prepared statements. If you want an additional layer of security, use some kind of input filter (eg get me only integers, get me only valid emails, get me only alphanum, etc).

And you obviously should not escape your password, as it doesn't even go into the query.

Escaping really only means that you escape certain characters. This means that ' becomes \' or \ becomes \\. That way, if they are inside a quoted string, they are interpreted correctly. This can prevent SQL injections if the input is inserted into a quoted string, as the attacker cannot exit that string. It doesn't do anything useful if you just apply it to all input. And it's not needed if you use prepared statements.

LIKE instead of equality

You should not use LIKE if you need an exact match. Your use of LIKE here makes it easier to bruteforce your login, and makes it easy to harvest email addresses.

For example, I can sucessfully log in as foobar@example.com:pass with %@%:pass.

As you use a loop instead of just fetching one result - which is what you should do - an attacker can use %@% as a fixed email and just bruteforce the passwords.

  • Thanks Tim . Thats what i thought about the pass is that it doesn't even go into the query but wasn't sure since it was part of the form. Also i used your query it doesn't grant a successful login even when i use a valid email and the %@%:pass as the pass any other suggestions. – nate35795 Apr 7 '16 at 16:05
  • Also LIKE just the only way i know of to be able to use the "?" in my SQL query to get it to work with prepared statements. I recently had it like this (select pass from social where email ='$id';) but didn't work. i was thinking about adding similar email addresses and see if that would screw up the login. Thanks for all your help. Would like to successfully crack this tho then patch. – nate35795 Apr 7 '16 at 16:06
  • @nate35795 Sorry for the misunderstanding. LOGINNAME:PASSWORD is a common notation for login credentials. So what I mean is use %@% as email address, and the correct password of any user, and you will be logged in. – tim Apr 7 '16 at 16:12
  • @nate35795 And your alternative should work, but is completely wrong, as it would not be using prepared statements. You need to use WHERE email = ?. There should be no variables in your query, only placeholders. – tim Apr 7 '16 at 16:15
  • select pass from social where email = ? does work must of just not known how to set values using prepared statements... Ok let me try it. – nate35795 Apr 7 '16 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.