Login only works for first data in database?

Question

This is my login.php page

<!DOCTYPE html>
<html>
<body>

<?php

$f_usr= $_POST["username"];
$f_pswd= $_POST["password"];

$con=mysql_connect("localhost","root","");

if(! $con)
{
        die('Connection Failed'.mysql_error());
}

mysql_select_db("login",$con);

$result=mysql_query("SELECT * FROM data");

while($row=mysql_fetch_array($result))
{
    if($row["username"]==$f_usr && $row["password"]==$f_pswd)
    {
        echo "<script language=\"JavaScript\">\n";
        echo "alert('Successfully Log In');\n";
        echo "window.location='MainPage.html'";
        echo "</script>";
    }
    else
    {
        echo "<script language=\"JavaScript\">\n";
        echo "alert('Username or Password was incorrect!');\n";
        echo "window.location='login.html'";
        echo "</script>";
    }
}

?>

</body>
</html>

This is my register.php page

<?php
$connect=mysqli_connect('localhost','root','','login');

if(mysqli_connect_errno($connect))
{
        echo 'Failed to connect';
}

?>

<?php

// create a variable
$username=$_POST['username'];
$password=$_POST['password'];

//Execute the query

mysqli_query($connect,"INSERT INTO data(username, password)
                VALUES('$username','$password')");

            if(mysqli_affected_rows($connect) > 0)
            {
                echo "<script language=\"JavaScript\">\n";
                echo "alert('Successfully Register');\n";
                echo "window.location='MainPage.html'";
                echo "</script>";
            } 
            else 
            {
                echo "<script language=\"JavaScript\">\n";
                echo "alert('Failed to Register');\n";
                echo "window.location='Register.html'";
                echo "</script>";
            }

?>

Here's my problem. I using xammp and I have 3 data currently iin my database. Wheni try to log in with my first data in databse it works, but when i try to log in with the 2nd data in databse it show incorrect username and password? Why?

Please note that `mysql_*` is now deprecated as of `PHP7` because of security issues. It is suggested that you switch to `mysqli_*` or `PDO` extensions. — Pedro Lobito, May 11 '16 at 19:52
`"SELECT * FROM data Where username='ddd' AND password='dddd'"` and use PDO to avoid sql injections — Abbasi, May 11 '16 at 19:53
[Little Bobby](http://bobby-tables.com/) says [your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, May 11 '16 at 19:54
Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, May 11 '16 at 19:54
**Never store plain text passwords!** Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). Make sure that you [don't escape passwords](http://stackoverflow.com/q/36628418/1011527) or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, May 11 '16 at 19:54

score 1 · Answer 1 · edited May 23 '17 at 12:16

1

The problem is because of the following lines,

$result=mysql_query("SELECT * FROM data");
while($row=mysql_fetch_array($result)){ ...

You're getting the entire result set with mysql_query(), and you're fetching only the first row from the result set. And that's why when you try to login with first data it works, but when you try to login with second or third data it doesn't work.

So the solution is,

Make your query like this:

$result=mysql_query("SELECT * FROM data WHERE username = '$f_usr' LIMIT 1");

It will return a result set comprising of a single row if it exists. And then fetch the row without any while loop, like this:

if(mysql_num_rows($result)){
    $row=mysql_fetch_array($result);

    // your code
}else{
    // Incorrect username
}

Sidenotes:

Don't use mysql_* functions, they are deprecated as of PHP 5.5 and are removed altogether in PHP 7.0. Use mysqli or pdo instead. And this is why you shouldn't use mysql_* functions.
Your queries are susceptible to SQL injection. Always prepare, bind and execute your queries to prevent any kind of SQL injection. And this is how you can prevent SQL injection in PHP.
Never store password as a plain readable text, always perform salted password hashing on raw password before inserting it into the table.

edited May 23 '17 at 12:16

Community

1
1

answered May 11 '16 at 19:56

Rajdeep Paul

16,887
3
18
37

@JayBlanchard So the column `password` need not to be wrapped in backticks? Would it work? – Rajdeep Paul May 11 '16 at 20:09
1

That is correct - columns named after keywords do not need to have the back ticking. – Jay Blanchard May 11 '16 at 20:12
1

...unless they are used as a function. It's rare but it happens. If so, then it needs to be ticked. – Funk Forty Niner May 11 '16 at 21:06
@Fred-ii- so it means that if a function or a procedure is named as `password` then it should be ticked, and if it's used as table name or column name then it need not to be ticked, am I getting this right? – Rajdeep Paul May 11 '16 at 21:10
pretty much @RajdeepPaul yes. – Funk Forty Niner May 11 '16 at 21:11
@Fred-ii- Thanks for the clarification. Learnt few things today. :-) – Rajdeep Paul May 11 '16 at 21:12

Login only works for first data in database?

1 Answers1