Issues with simple php/mysql login script

Question

I am trying to learn PHP and created a simple PHP/MySQL form that does successfully register users but is having 2 issues.

I can't get the page to redirect successfully to index.php after the user hits "Register"
I thought if a user is registered they would see the message "welcome (name)" when they come back to the register.php page because I am trying to use sessions. But once I log in, when I go back to register.php, the page acts as if I am not registered and just displays the form again.

What am I missing for these two questions?

<?php 
session_start();
require_once 'connection.php';  

if (isset($_SESSION['username']))  
{  
echo "welcome $author";  
}  
?>


<html>



<?php include('includes/header.php'); ?>


<!-- The HTML registration form -->
<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
Username: <input type="text" name="username" /><br>
Author/Pen Name: <input type="text" name="author" /><br>
Password: <input type="password" name="password" /><br>
Email: <input type="type" name="email" /><br />
Author Bio (optional): <br><textarea name="bio" cols="20" rows="5"></textarea><br>

<input type="submit" name="submit" value="Register" />
<a href="login.php">I already have an account...</a>
</form>

<?php
if (isset($_POST['submit'])) {
## connect mysql server
$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
# check connection
if ($mysqli->connect_errno) {
echo "<p>MySQL error no {$mysqli->connect_errno} : {$mysqli->connect_error}</p>";
exit();
}
## query database
# prepare data for insertion
$username   = $_POST['username'];
$contact    = $_POST['author'];
$password   = $_POST['password'];
$email  = $_POST['email'];
$bio        = $_POST['bio'];

# check if username and email exist else insert
// u = username, e = emai, ue = both username and email already exists
$exists = "";
$result = $mysqli->query("SELECT username from user WHERE username = '{$username}' LIMIT   1");
if ($result->num_rows == 1) {
$exists .= "u";
}   
$result = $mysqli->query("SELECT email from users WHERE email = '{$email}' LIMIT 1");
if ($result->num_rows == 1) {
$exists .= "e";
}

if ($exists == "u") echo "<p><b>Error:</b> Username already exists!</p>";
else if ($exists == "e") echo "<p><b>Error:</b> Email already exists!</p>";
else if ($exists == "ue") echo "<p><b>Error:</b> Username and Email already exists!
</p>";
else {
# insert data into mysql database
$sql = "INSERT  INTO `user` (`username`, `author`, `password`, `email`, `bio`) 
VALUES ('{$username}', '{$contact}', '{$password}', '{$email}', '{$bio}')";

if ($mysqli->query($sql)) {
header("Location: site.com/index.php");
} else {
echo "<p>MySQL error no {$mysqli->errno} : {$mysqli->error}</p>";
exit();
}
}
}
?>

Use [error reporting](http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php) and follow the messages/what you find on them. Most likely you can solve this all by yourself — kero, Jul 04 '14 at 00:19
I also suggest you read this thoroughly to avoid SQL injection vulnerabilities ~ http://php.net/manual/mysqli.quickstart.prepared-statements.php — Phil, Jul 04 '14 at 00:21
This is a bit of a mess. You have a welcome message before your opening `` tag, unsanitised use of `PHP_SELF`, multiple queries where you could use one, unchecked array indices, SQL injection vulnerabilities and a *"headers already sent"* error just waiting to happen. — Phil, Jul 04 '14 at 00:33
More; you never assign a value to `$_SESSION['username']` so the welcome message will never appear. Also `$author` is never defined — Phil, Jul 04 '14 at 00:56
i guess the `$_SESSION['username']` is defined by login.php, i hope ^^ — Bobot, Jul 04 '14 at 01:01

Giacomo1968 · Accepted Answer · 2014-07-04T00:37:53.493

Your header directive is like this:

header("Location: example.com/index.php");

But you need to set a full URL like this:

header("Location: http://example.com/index.php");

That said, you cannot have HTML content be output before headers are set. You’ll just end up with a “headers already sent” error. I refactored your code to place all of the PHP at the top of the page. Remember, PHP get’s processed & HTML get’s displayed. So all of your PHP logic is best placed at the top of the page.

I also reformatted your code for clarity. This is not a minor thing. The harder it is to read your code, the hard it is to debug for you & it’s even harder to debug for others.

<?php

session_start();

require_once 'connection.php';  

if (isset($_SESSION['username'])) {  
  echo "welcome $author";  
}  

if (isset($_POST['submit'])) {
  ## connect mysql server
  $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
  # check connection
  if ($mysqli->connect_errno) {
    echo "<p>MySQL error no {$mysqli->connect_errno} : {$mysqli->connect_error}</p>";
    exit();
  }
  ## query database
  # prepare data for insertion

  $username = $_POST['username'];
  $contact = $_POST['author'];
  $password = $_POST['password'];
  $email = $_POST['email'];
  $bio = $_POST['bio'];

  # check if username and email exist else insert
  // u = username, e = emai, ue = both username and email already exists
  $exists = "";
  $result = $mysqli->query("SELECT username from user WHERE username = '{$username}' LIMIT   1");
  if ($result->num_rows == 1) {
    $exists .= "u";
  }   

  $result = $mysqli->query("SELECT email from users WHERE email = '{$email}' LIMIT 1");
  if ($result->num_rows == 1) {
    $exists .= "e";
  }

  if ($exists == "u") {
    echo "<p><b>Error:</b> Username already exists!</p>";
  }
  else if ($exists == "e") {
    echo "<p><b>Error:</b> Email already exists!</p>";
  }
  else if ($exists == "ue") {
    echo "<p><b>Error:</b> Username and Email already exists!</p>";
  }
  else {
    # insert data into mysql database
    $sql = "INSERT  INTO `user` (`username`, `author`, `password`, `email`, `bio`)"
         . " VALUES ('{$username}', '{$contact}', '{$password}', '{$email}', '{$bio}')"
         ;

    if ($mysqli->query($sql)) {
      header("Location: http://example.com/index.php");
    }
    else {
      echo "<p>MySQL error no {$mysqli->errno} : {$mysqli->error}</p>";
      exit();
    }
  }
}
?>  

<html>

<?php include('includes/header.php'); ?>

<!-- The HTML registration form -->

<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
Username: <input type="text" name="username" /><br>
Author/Pen Name: <input type="text" name="author" /><br>
Password: <input type="password" name="password" /><br>
Email: <input type="type" name="email" /><br />
Author Bio (optional): <br><textarea name="bio" cols="20" rows="5"></textarea><br>
<input type="submit" name="submit" value="Register" />
<a href="login.php">I already have an account...</a>
</form>

I know the current spec says to use an absolute URI but in practice, this is not required. Most (if not all) clients support the draft spec allowing relative URIs. So simply `/index.php` should suffice and is easier to implement — Phil, Jul 04 '14 at 00:26
Also, if OP is attempting to send a `header()` after all that HTML output, they're going to have a bad time — Phil, Jul 04 '14 at 00:29
@Phil Noted. Just refactored the code as best as possible to place the PHP code at the top of the file before any HTML gets set. — Giacomo1968, Jul 04 '14 at 00:38
Great. Makes sense and part 1 works. But if I click on register again after the successful registration it just shows the form again with no indication the user was logged in. But maybe that is fine--as long as the user login variables stay active across all pages (with session start code I assume). — user5000, Jul 04 '14 at 01:14
@user3077135 “But if I click on register again after the successful registration it just shows the form again with no indication the user was logged in.” That is because that is your code logic. If you want them to see something else or be bounced elsewhere, change your `if (isset($_SESSION['username'])) {` to also have a `header('Location: …;)` directive. The structure works the way it works because that is how it is coded. Code it to work the way you want it to work. — Giacomo1968, Jul 04 '14 at 01:18
Very cool. Thanks. I'll do that. And now I suppose I have to learn about all those other things Phil mentioned. What change made it sanitized? is that what protects against sql injection? — user5000, Jul 04 '14 at 01:21
@user3077135 None of my changes sanitized anything. Strictly formatting. Just now use what works & improve it by reading up on sanitizing data and applying that. — Giacomo1968, Jul 04 '14 at 01:24
thanks again. One thing I noticed though is that the headers and footers will not display now even though the directories/paths are correct? Is that because we put the php processing code first? — user5000, Jul 04 '14 at 14:26
@user3077135 Nope. The PHP code in this is completely separate from the rest of it. `` — Giacomo1968, Jul 04 '14 at 14:48
Hmm. That's the exact code I have and it displayed header (and footer with footer include) until the script edits. — user5000, Jul 04 '14 at 15:12
@user3077135 Sorry, but the thing with this, “Excuse me, one more thing…” question is it’s something we cannot help you with. I would test what you have in that snipped & even the header with simple `echo 'Hello world.';` placed wherever something is not showing up where it should. But the code as presented should work. The debugging process is in your hands now. — Giacomo1968, Jul 04 '14 at 15:18

score 1 · Answer 2 · edited Jul 29 '22 at 23:19

For you first question : Giacomo1968 answered here (it's a link)

For your second question : you use : $autor, when it's not defined, use :

if( isset($_SESSION['username']) ){
    echo 'welcome '.htmlentities($_SESSION['username']);  
}

Ah yes, and just put : exit(); after this test if you don't want to display the form ... (the best way is still to put your processing code on the top of the script, and the display one into the html ...)

See this :

<?php 
session_start();

require_once 'connection.php';

$show_form = true;

if (isset($_POST['submit'])) {
    // connect mysql server
    $mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);
    // check connection
    if ($mysqli->connect_errno) {
        die('<p>MySQL error no '.$mysqli->connect_errno.' : '.$mysqli->connect_error}.'</p>');
    }
    // query database
    // prepare data for insertion
    $username   = (isset($_POST['username'])) ? mysqli_real_escape_string($_POST['username']) : '';
    $contact    = (isset($_POST['author'])) ? mysqli_real_escape_string($_POST['author']) : '';;
    $password   = (isset($_POST['password'])) ? mysqli_real_escape_string($_POST['password']) : '';
    $email      = (isset($_POST['email'])) ? mysqli_real_escape_string($_POST['email']) : '';
    $bio        = (isset($_POST['bio'])) ? mysqli_real_escape_string($_POST['bio']) : '';

    // check if username and email exist else insert
    // u = username, e = emai, ue = both username and email already exists
    $exists = "";

    $result = $mysqli->query('SELECT username from user WHERE username = \''.$username.'\'');
    if ($result->num_rows == 1) {
        $exists .= 'u';
    }
    $result = $mysqli->query('SELECT email from users WHERE email = \''.$email.'\'');
    if ($result->num_rows == 1) {
        $exists .= 'e';
    }
    
    if ($exists == 'u') $msg = '<p><b>Error:</b> Username already exists!</p>';
    else if ($exists == 'e') $msg = '<p><b>Error:</b> Email already exists!</p>';
    else if ($exists == 'ue') $msg = '<p><b>Error:</b> Username and Email already exists!</p>';
    else {
        // insert data into mysql database
        $sql = 'INSERT  INTO `user` (`username`, `author`, `password`, `email`, `bio`) 
        VALUES (\''.$username.'\', \''.$contact.'\', \''.$password.'\', \''.$email.'\', \''.$bio.'\')';

        if ($mysqli->query($sql)) {
            header('Location: index.php');
        } else {
            die('<p>MySQL error no '.$mysqli->errno.' : '.$mysqli->error.'</p>');
        }
    }
}

if( isset($_SESSION['username']) ){ 
    $msg = 'Welcome '.htmlentities($_SESSION['username']);
    $show_form = false;
}


?>


<html>

<?php

include('includes/header.php');

if( isset($msg) && !empty($msg) ){
    echo $msg;
}

if( $show_form ){

?>

<!-- The HTML registration form -->
<form action="" method="post">
Username: <input type="text" name="username" /><br>
Author/Pen Name: <input type="text" name="author" /><br>
Password: <input type="password" name="password" /><br>
Email: <input type="type" name="email" /><br />
Author Bio (optional): <br><textarea name="bio" cols="20" rows="5"></textarea><br>

<input type="submit" name="submit" value="Register" />
<a href="login.php">I already have an account...</a>
</form>

<?php
}
?>

EDIT : ah damnit didn't seen that Giacomo1968 edited his post ... soz its a kind of doublon now :/ Still hope it will help ^^

Much better logic flow. Now, if only somebody would address [all the other issues mentioned](http://stackoverflow.com/questions/24565061/issues-with-simple-php-mysql-login-script#comment38049197_24565061)... ;) — Phil, Jul 04 '14 at 00:49
There's also the missing vital step of assigning a value to `$_SESSION['username']` — Phil, Jul 04 '14 at 00:56
Wow....so many helpful responses! Yes, I'm sure there are all sorts of issues with sanitizing and sql injection, but like I say, I'm just trying to learn how to make the basics for now. I am going to make sense of everyone's great responses now and see what happens... — user5000, Jul 04 '14 at 01:03
ok, now script is sanitized, but i didn't found the way to use only 1 query to make difference between username allready taken, email allready taken, and both allready taken ^^ btw, i deleted the PHP_SELF and let action empty, all navigators after ie6 use the current page as action if i remember good ... basic sql injection and array key check done ... is there some more upgrades to do ? ^^ — Bobot, Jul 04 '14 at 01:06
@user3077135 you are right, try the basics :) but do not forget that error_display is not set to off on all servers :p so test if your var exists with [isset()](http://www.php.net/manual/fr/function.isset.php) function, and don't forget that processing MUST be in top of the script, display after, or you will get many errors like here with : headers allready sent ... (you cannot see if you don't have `error_reporting = E_ALL` and `display_error = On` in your php.ini ;) — Bobot, Jul 04 '14 at 01:09
BTW IM STUPID : you can use [error_reporting(E_ALL)](http://www.php.net/manual/fr/function.error-reporting.php) and [ini_set('display_errors', '1')](http://fr2.php.net/manual/fr/function.ini-set.php) in the top of your script ! — Bobot, Jul 04 '14 at 01:12

PrathapB · Answer 3 · 2014-07-04T01:10:11.957

You did not add value to the SESSION variable. Since the SESSION variable is not set the if condition

if (isset($_SESSION['username']))  
{  
echo "welcome $_SESSION['author']";  
exit(); //This will prevent displaying the form
}

is evaluated to false. And hence you din't receive the welcome message. And you if you need to display the author name add a session variable for author too as below

if ($mysqli->query($sql)) {
$_SESSION['username'] = $username;
$_SESSION['author'] = $contact;
header("Location: index.php");
}

And the redirection part works well for me... Use just 'index.php' instead of 'mysite.com/index.php' if both your register.php and index.php lie in same folder.

Issues with simple php/mysql login script

3 Answers3