Connection problems using PHP and MySQL

advertisements

I'm attempting to write a login script in PHP and MySQL with username and password fields. I also have a separate php page which checks the username and password against the database.

The problem is, after inputing a username and a password, the data is not being stored into the database. I'm not sure if it's a problem with the structure of the table, or with the way that the data is being inserted into the table, or something else.

This is the login page, where the user logs in with a username and password. There is also a button that redirects to New.php to create a new account.

<html>
<body>
    <!--starts a form that redirects to Validate.php when submitted with entered values-->
    <form action="Validate.php">
        Enter your username and password below then click login.
        <br>
        Username:
        <input type="text" name="username" id="username">
        <br>
        Password:
        <input type="password" name="password" id="password">
        <br>
        <input type="submit" value="Login">
    </form>

    <!--if there is a message set, it displays the message-->
    <?php
        if (isset($_GET['msg']))
            echo "<p>".$_GET['msg']."</p>";
    ?>

    <!--starts a form that redirects to New.php when submitted-->
    <form action="New.php">
        To create a new account
        <input type="submit" value="Click Here">
    </form>
</body>
</html>

This is the php page which checks the username and password against the database.

<?php
function testPassword($fpwd, $fdbsalt, $fdbhash)
 {
   return hash_hmac("sha256", $fpwd, $fdbsalt) == $fdbhash;
 }

$a = $_GET; 

$user = $a['username'];
$pwd = $a['password'];

include('Connect.php');

$found = mysqli_num_rows(mysqli_query($con,"SELECT * FROM students WHERE username =    $user"));

if($found < 1)
  { header('Location: Login.php?msg=Username%20incorrect!'); }

else
{
  $dbsalt = mysqli_query($con,"SELECT salt FROM students WHERE username = $user");
  $dbhash = mysqli_query($con,"SELECT hash FROM students WHERE username = $user");

  if(testPassword($pwd, $dbsalt, $dbhash))
  {
     session_start();
     $b = $_SESSION;
     $b['user'] = $user;

     if($user === "00000000")
        { header('Location: Students.php'); }

     else
        { header('Location: Display.php'); }
  }

  else
     { header('Location: Login.php?msg=Password%20incorrect!'); }
}

The table that's been created has the following structure. There are a lot more fields other than username and password because after a successful login, the user would be sent to a separate form to fill out.

$sql="CREATE TABLE students(rows int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY(rows),
username VARCHAR(30),
firstname char(30),
lastname char(30),
sex char(6),
studentnumber INT(8),
email VARCHAR(80),
degree CHAR(50),
major CHAR(50),
minor CHAR(50),
experience INT(11),
skills TINYTEXT,
fblacklist1 CHAR(30),
fblacklist2 CHAR(30),
fblacklist3 CHAR(30),
lblacklist1 CHAR(30),
lblacklist2 CHAR(30),
lblacklist3 CHAR(30),
salt VARCHAR(20),
hash VARCHAR(20),
busydays VARCHAR(255),
project INT(2)";

UPDATE: I've implemented @Alfie's solution, but, no luck yet. Now I'm thinking maybe there is a problem with how I'm connecting to the database? Thanks in advance:

<?php
$host = "localhost";
$user = "";
$password = "";
$database = "";
$tbl = "Students";

/*
 connects to database
*/
$conn = mysql_connect("$host", "$user", "$password")
or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";

//select a database to work with
mysql_select_db("$database")or die("Could not select welcome");

   /*
   if the connection fails an error message is displayed
   */
if (mysqli_connect_error($con))
{
    echo "Connection failed". mysqli_connect_error();
}

mysql_select_db("$database")or die("cannot select DB");

$user = $_POST['user'];
$salt = $_POST['salt'];
$pass = $_POST['hash'];

 $sql="INSERT INTO $tbl (username, salt, hash) VALUES ('$user', '$salt', '$hash')");
 $result =mysql_query($sql);

if ($result)
 {
   echo "successfuly inserted into database!"
     }

 else
  {
   echo "ERROR";
      }

mysql_close($con);
}
?>


You are not using prepared statements (you should), and so your MySQL query's varchar fields need escaping with quotes, for example:

$dbsalt = mysqli_query($con,"SELECT salt FROM students WHERE username = '$user'");
$dbhash = mysqli_query($con,"SELECT hash FROM students WHERE username = '$user'");

Notice the quotes around $user.

Additionally, you are trying to access the data from the query directly from the result given by mysqli_query (which is actually an an object containing the data), instead, you should use like this:

$dbresult = mysqli_query($con,"SELECT salt, hash FROM students WHERE username = '$user'");

$row = $dbresult->fetch_row();

if(testPassword($pwd, $row['salt'], $row['hash']))
....

Edit: after looking more closely, there are also a few other 'optimisations' you might like to consider; there is no need to assign the $_SESSION and $_GET arrays to separate variables before using them (and this may also be a reason why your data isn't getting 'stored', if you were referring to the session data?), and you only need 1 database query for your script:

<?php
function testPassword($fpwd, $fdbsalt, $fdbhash)
 {
   return hash_hmac("sha256", $fpwd, $fdbsalt) == $fdbhash;
 }

$user = $_GET['username'];
$pwd = $_GET['password'];

include('Connect.php');

$dbresult = mysqli_query($con,"SELECT salt, hash FROM students WHERE username = '$user'");

$found = mysqli_num_rows($dbresult);

if($found < 1)
  { header('Location: Login.php?msg=Username%20incorrect!'); }

else
{
  $row = $dbresult->fetch_row();

  if(testPassword($pwd, $row['salt'], $row['hash']))
  {
     session_start();
     $_SESSION['user'] = $user;

     if($user === "00000000")
        { header('Location: Students.php'); }

     else
        { header('Location: Display.php'); }
  }

  else
     { header('Location: Login.php?msg=Password%20incorrect!'); }
}