0

It's my first question in Stackoverflow so sorry if something is not quite right.

I started learning back-end development a few days ago.
Currently, I am working on an application with login capabilities.

From what I have learned so far, I should take a username and password input, then check if there is a match in a database and if there is I will log that person in.

However, in order to protect my web app from potential SQL injections, I need to use prepared statements or at least escape strings when building queries.

If I get user input, then I use a for loop to get username and password of each user and compare them to user input that is trying to log in.
That would seem to solve SQL injection danger since nothing is sent to the server, only compared to data inside the server.

Is this approach a lot slower or does it open other vulnerabilities?

Which method should be used to be better and safer?

This is the PHP code I am using:

$r = "SELECT * FROM users";
$result = mysqli_query($con, $r);
$rows = mysqli_num_rows($result);
for ($i = 1; $i <= $rows; $i++){
    $checkQ = "SELECT * FROM users WHERE id = $i";
    $r = mysqli_query($con, $checkQ);
    $result = mysqli_fetch_assoc($r);

    if ((isset($_POST["username"])) && (isset($_POST["password"]))) {
        if (
            ($result["username"] == $_POST["username"])
            && ($result["password"]==$_POST["password"])
        ) {
            $_SESSION["username"] = $_POST["username"];
            mysqli_close($con);
            header("Location: home.php");
        }
    }
}
JustCarty
  • 3,839
  • 5
  • 31
  • 51
NewbieCoder
  • 3
  • 2
  • 2
    Well for a start it looks like you have a Plain Text Password stored on your database! PHP provides [`password_hash()`](http://php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://php.net/manual/en/function.password-verify.php) please use them. And here are some [good ideas about passwords](https://www.owasp.org/index.php/Password_Storage_Cheat_Sheet) If you are using a PHP version prior to 5.5 [there is a compatibility pack available here](https://github.com/ircmaxell/password_compat) – RiggsFolly Jan 24 '19 at 10:13
  • Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's – RiggsFolly Jan 24 '19 at 10:14
  • ___Is this appraoch a lot slower___ YES A LOT! Imagine if you are lucky enough to get 1000 users, or even 100,000 users – RiggsFolly Jan 24 '19 at 10:14
  • 1
    Also, this assumes that the IDs are consecutive and there are no gaps. If there are any rows deleted then your IDs may be out of sync with the number of rows. – JustCarty Jan 24 '19 at 10:17
  • ^^^ So always better to use a WHILE loop when processing data out of a result set – RiggsFolly Jan 24 '19 at 10:18
  • And of course if you use `SELECT *` you already have the `id` in the result set. Although `SELECT *` is not a good ways to write a query unless you actually want every column from a table, which is unusual. And in this case all you want is `id,username,password` and password should have been hashed when you store it on a database – RiggsFolly Jan 24 '19 at 10:20
  • Thanks to everyone. I'm aware that i need to hash passwords, just didn't want an extra layer to deal with while im learning server-web comunication on my local server. I got an answer i was looking for. Thumbs up for everyone :) – NewbieCoder Jan 24 '19 at 10:25
  • 2
    Since you're learning, you should learn the right way. Use parametrized queries to avoid SQL injection, not ad hoc schemes like this. https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Barmar Jan 24 '19 at 10:28
  • 2
    I also suggest you learn PDO instead of mysqli, it's a much nicer API. – Barmar Jan 24 '19 at 10:28
  • Does this even remotly look correct? `include "assets/php/connect.php"; $cName = $_POST["username"]; $cPass = $_POST["password"]; $sql = "SELECT id,username,password FROM users WHERE username = ?"; $stmt = $con->prepare($sql); $stmt->execute([$_POST['username']]); $logs = $stmt->fetchAll(); foreach($logs as $log){ if(($cName == $log->username)&&($cPass == $log->password)){ header("Location: home.php"); }else{ echo "
    Error
    "; } }`     – NewbieCoder Jan 24 '19 at 17:22
  • Sorry for the messy code above, not quite sure how to make it display correctly yet :/ – NewbieCoder Jan 24 '19 at 17:42

0 Answers0