3

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
Improve this question
2
  • 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, 2016 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, 2016 at 9:48

1 Answer 1

Reset to default
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.

Improve this answer
10
  • 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, 2016 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, 2016 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, 2016 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, 2016 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, 2016 at 16:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .