having trouble with PHP and SQL

Question

I am trying to display an error message in php ( $error="Username or Password is invalid"; ) when the username or password is not in the database. But without even running the code(clicking login in index.php the error message is displaying. Thanks :)

<?php

    session_start(); // Starting Session
    $error=''; // Variable To Store Error Message
    if (isset($_POST['submit'])) {

    }

    if (isset($_POST['submit'])) {



    // Define $username and $password
     $name=$_POST['username'];
     $pass=$_POST['password'];

    $servername = "localhost";
    $username = "root";
    $password = "root";
    $dbname = "company";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection


    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $sql = "SELECT id, username, password FROM login";
    $result = $conn->query($sql);


    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            //echo "id: " . $row["id"]. " - Name: " . $row["username"]. " " . $row["password"]. "<br>";
            if($name==$row["username"] && $pass== $row["password"]){
                header("location: profile.php"); // Redirecting To Other Page
            }else{
                $error="Username or Password is invalid";
            }

        }

    }




         else {
        echo "Server is down";
    }
    $conn->close();
    }

    ?>

My index.php

<?php
include('../php/login.php'); // Includes Login Script

if(isset($_SESSION['login_user'])){
header("location: ../php/profile.php");
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Login Form in PHP with Session</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body>
<div id="main">
<h1>PHP Login Session Example</h1>
<div id="login">
<h2>Login Form</h2>
<form action="" method="post">
<label>UserName :</label>
<input id="name" name="username" placeholder="username" type="text">
<label>Password :</label>
<input id="password" name="password" placeholder="**********" type="password">
<input name="submit" type="submit" value=" Login ">
<span><?php echo $error; ?></span>
</form>
</div>
</div>
</body>
</html>

So you see the error even if you cleanly open the page, not renewing it? BTW this is very unsafe, you should use prepared statements for your sql. — Firewizz, Feb 22 '16 at 22:51
I tried turning off the server and turning it back on it worked thanks. — , Feb 22 '16 at 23:00

score 0 · Accepted Answer · edited May 23 '17 at 11:46

I updated your security a little. Make sure you always validate user input. The code below is secure against SQL injections. No way of injection possible! Also HEX attacks are not possible.

<?php
if (!isset($_SESSION)) { session_start(); }

$db_host = "localhost";
$db_user = "root";
$db_pass = "root";
$db_name = "company";

$conn = new mysqli($db_host, $db_user, $db_pass, $db_name);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}   

$errmessage = $error = '';

function checkUsername($data) {
    if (preg_match('/[^A-Za-z0-9.]{8,50}/', $data)) { // A-Z, a-z, 0-9, . (dot), min-length: 8, max-length: 50
        $data = false;
    } 
    return $data;
}

function checkPassword($data) {
    if(!preg_match('/^(?=.*\d)(?=.*[A-Za-z])[0-9A-Za-z!@#$%]{8,50}$/', $data)) { // A-Z, a-z, 0-9, !, @, #, $, %, min-length: 8, max-length: 50
        $data = false;
    }
    return $data;
}

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

    $username = $_POST['username'];
    $password = $_POST['password'];

    if (checkUsername($username) === false) {
        $error = "Username is not valid!";
        exit();
    }
    if (checkPassword($password) === false) {
        $error = "Password is not valid!";
        exit();
    }

    $secure_name = bin2hex(htmlspecialchars($username));
    //$secure_pass = hashpassword($securepass); // Hash your passwords!
    $secure_pass = bin2hex(htmlspecialchars($password));

    $sql = "SELECT * FROM login WHERE username = UNHEX('$username') AND password = UNHEX('$password')";
    $result = $conn->query($sql);

    if ($result->num_rows == 1) {
        session_regenerate_id();
        $_SESSION['login_user'] = true;
        header("location: profile.php");
    } else {
        $error = "Username and/or Password is invalid";
    }
    $conn->close();

} else {
    echo "Error";
}
?>

Source for HEX against injection: How can I prevent SQL injection in PHP? (Zaffy's answer)

Extra information:

Do not check only if the session login exist, but check also its value!

if(isset($_SESSION['login_user'])){
    header("location: ../php/profile.php");
}

Must be

if (isset($_SESSION['login_user']) && $_SESSION['login_user'] === true){
    header("location: ../php/profile.php");
    exit();
} else {
    // Loginscript
}

score 0 · Answer 2 · answered Feb 23 '16 at 01:20

0

The PHP script seems to take ALL username/password pairs from DB

$sql = "SELECT id, username, password FROM login";

Later in while loop first pair not matching with user input would trigger an error message assignment

answered Feb 23 '16 at 01:20

kshpolvind

91
4

having trouble with PHP and SQL

2 Answers2