Login Script against two tables

[DJMaze]

Yeah sorry i didn't give an explanation and actualy was a bit rough on you at 6 AM in the morning after i fixed another website.

Anyway i will provide some background information here which should help you on your way.

PHP Code:

$result=mysql_query("SELECT * FROM tutor
  WHERE username='" . $username . "' and password='" . $password . "'"); 


$username and $password are not checked for right quoting.
This means if i type a password like: 0' UNION SELECT 'something
The actualy SQL query looks like:

SELECT * FROM tutor WHERE username='foo' and password='0' UNION SELECT 'something'

As you can see anyone can manipulate the SQL queries.
To get around this use

PHP Code:

$username = mysql_real_escape_string($username); 


I do notice now that you fixed $_REQUEST["username"] into $_POST["username"]

$_REQUEST can be either a $_POST (form post method), a $_GET (index.php?getkey=value or form get methof) or a $_COOKIE
It's the same bad thing as register_globals.

"register_globals" is an php.ini setting that copies all "request" variables (get, post cookie) to the global scope of your php documents.
For example i ask a page "index.php?username=foo&password=bob" then inside your document the following is works:

PHP Code:

<?php
echo "Your username is $username\n";
echo "Your password is $password\n";

this will outpur

Your username is foo
Your password is bob

In the above code you use

PHP Code:

if($_POST["username"])
{
  $username=$_POST["username"];
  $password=$_POST["password"];  
}
elseif($_SESSION["username"])
{
  $username=$_SESSION["username"];
  $password=$_SESSION["password"];
} 


But there's no checking on the register_globals so if i set the "username" and "password" in a different way then you want it to i can still get in.
So the correct coding would be more like:

PHP Code:

if($_POST["username"])
{
  $username=$_POST["username"];
  $password=$_POST["password"];
}
elseif($_SESSION["username"])
{
  $username=$_SESSION["username"];
  $password=$_SESSION["password"];
}
else
{
  $username='';
  $password='';
} 


this way you are 100% certain they are not trying to get around your requests

[sde]

now that is some great information. thanks DJ!

[BUFFY]

Wow, ok. Thanks DJ, thats really useful. I can understand what your saying.

I can see what you mean by entering in the variables through the URL and setting them that way, it would be quiet simple.

I added the following code before the SELECT query:

PHP Code:

 $username = mysql_real_escape_string($username); 


I also added $admin = 1; in tutor, and $admin = 2; in student, which I think should help me in the future for displaying restricted data if $admin == 1; for a tutor.

Now ive basically copied and changed a number of registration examples, some are too advanced for what I need, but this one was quite simple, but when I press the Register button the form just clears the input boxes...

On my register.php page i see it as accepting the input, sending it to itself and doing the sumbit actions. But Im obviously not doing something as it just reloads the page, this time with the input fields blank, no confirmation message, no updated database. I know the password place is wrong as I haven't linked up the include file to validate the registration fields, just wanting to get it updating the database first.

On this page is the following form:

register.php

PHP Code:

<?PHP
if($submit)
{

//Firsty connect to database
include 'connect.inc.php';

$sql = "INSERT INTO student
           (title, firstname, lastname, username, password, address, suburb, city, phonenumber, dob) 
           VALUES ('$title', '$firstname', '$lastname', '$username', '$password', '$address', '$suburb', '$city', '$phonenumber', '$dob')";
$result = mysql_query($sql);
echo "Thank you! Information entered.\n";
}
else
{ 

?>
<form action="register.php" method="post">
<table width="95%" border="1">
 <tr>
  <td align="left" valign="top">
<table>
 <tr> 
  <td colspan="4"><img src="spacer.gif">Registration Details</td>
 </tr>
 <tr> 
  <td><img src="spacer.gif"></td>
  <td><p>Title:<br>
 (Mr, Miss, Mrs, Dr)&nbsp;</p></td>
   <td colspan="2">
            <select name=title>
            <option >Mr.</option>
            <option >Mrs.</option>
            <option >Ms.</option>
            <option >Dr.</option>
            </select> 
   </td>
  </tr>
  <tr> 
   <td><img src="spacer.gif"></td>
   <td>First Name:&nbsp;</td>
   <td colspan="2"><input type="text" name="firstname"/> 
  </td>
  </tr>
  <tr> 
   <td><img src="spacer.gif"></td>
   <td>Last Name &nbsp;</td>
   <td colspan="2"><input type="text" name="lastname"/> 
   </td>
  </tr>
   <tr> 
  <td><img src="spacer.gif"></td>
  <tdclass="textblue">Email Address:&nbsp;</td>
  <tdclass="text2"><input name="username" type="text" id="username"/> </td>
   <td width="50%"><span class="textred">*vaild email address is required for login username</span></td>
  </tr>
  <tr> 
   <td><img src="spacer.gif"></td>
   <td>Password:&nbsp;</td>
   <td><input name="password1" type="password" id="password1"/></td>
   <td><span class="textred">*up to 20 characters long</span></td>
  </tr>
  <tr> 
   <td><img src="spacer.gif"></td>
   <td>Confirm Password:&nbsp;</td>
   <td colspan="2"><input name="password2" type="password" id="password2"/></td>
  </tr>
  <tr> 
   <td><img src="spacer.gif"></td>
   <td>Address:&nbsp;</td>
   <td colspan="2"><input type="text" name="address"/></td>
  </tr>
  <tr> 
   <td><img src="spacer.gif"></td>
   <td>Suburb:&nbsp;</td>
   <td colspan="2"><input type="text" name="suburb"/> 
   </td>
  </tr>
  <tr> 
   <td><img src="spacer.gif"></td>
   <td>City:&nbsp;</td>
   <td colspan="2"><input type="text" name="city"/> </td>
  </tr>
  <tr> 
   <td><img src="spacer.gif"></td>
   <td>Phone Number: &nbsp;</td>
   <td colspan="2"><input type="text" name="phonenumber"/> 
  </td>
  </tr>
  <tr> 
   <td><img src="spacer.gif"></td>
   <td>Date of Birth:<br>(YEAR-MM-DD format) &nbsp;</td>
   <td colspan="2"><input type="date" name="dob"/> </td>
  </tr><tr align="left" valign="middle">
   <td height="40" colspan="4"><div align="left"> &nbsp; 
   <input name="submit" type="submit"  value="Register"/>
   <input type=reset  name=reset  value="Clear">
 <br>
 </div></td>
  </tr>
 </table>
 <div align="left"></div></td>
 </tr>
</table>
  </form>
<?PHP
}
exit;
?>

[redhead]

an easy fix will be

PHP Code:

echo "Thank you! Information entered.\n"; 
exit; 


and remove the entire else clause.
In order to make the else clause work, you would have to echo or print() the form section of the page, which would make it a valid element in your else clause.

[BUFFY]

Tried the quick fix and removing the else statement. Didn't change much.

I then put just the form on register.php and in the form action directed it to reg.inc.php. When the register button was pressed the page loaded reg.inc.php with "Thank you! Information entered" but the database had not been updated (added to from reg details).

reg.inc.php

PHP Code:

<?PHP
include 'connect.inc.php';

//for new PHP
$title=$_GET[title];
$firstname=$_GET[firstname];
$lastname=$_GET[lastname];
$username=$_GET[username];
$password=$_GET[password];
$suburb=$_GET[suburb];
$city=$_GET[city];
$phonenumber=$_GET[phonenumber];
$dob=$_GET[dob];

$sql = "INSERT INTO customer SET
           (title, firstname, lastname, username, password, address, suburb, city, phonenumber, dob) 
           VALUES ('$title', '$firstname', '$lastname', '$username', '$password', '$address', '$suburb', '$city', '$phonenumber', '$dob')";
$result = mysql_query($sql);
echo "Thank you! Information entered.\n";
?>

Used GET because its getting the data from the previous form... POST does the same thing and also doesn't add the info to the database.

[DJMaze]

PHP Code:

<?PHP
error_reporting(E_ALL); // added to check for bugs
include 'connect.inc.php';

//for new PHP
$title=$_GET[title];
$firstname=$_GET[firstname];
$lastname=$_GET[lastname];
$username=$_GET[username];
$password=$_GET[password];
$suburb=$_GET[suburb];
$city=$_GET[city];
$phonenumber=$_GET[phonenumber];
$dob=$_GET[dob];

$sql = "INSERT INTO customer SET
           (title, firstname, lastname, username, password, address, suburb, city, phonenumber, dob)
           VALUES ('$title', '$firstname', '$lastname', '$username', '$password', '$address', '$suburb', '$city', '$phonenumber', '$dob')";
$result = mysql_query($sql);
// error check added
// NOTE: uses === (triple =) which checks if the result is boolean AND false because 0==false is also true
if ($result === false) {
  die(mysql_error());
}
echo "Thank you! Information entered.\n";
?>

I don't know the variable that is set inside connect.inc.php but if it's something like:

PHP Code:

$db_connection = mysql_connect() 


then use

PHP Code:

$result = mysql_query($sql, $db_connection);
if ($result === false) {
  die(mysql_error($db_connection));
} 


[BUFFY]

Thanks for the error checking code DJ

I updated the reg.inc.php but I am still not updating the database.

Also updated my connect.inc.php:

PHP Code:

<?PHP
//Include file

//Connect to the database
$db=mysql_connect("localhost", "xxx", "xxx") 
or die("Could not connect to localhost."); 

//Commented this out as its in the wrong place and effecting the database.
//Dj's security check code
//$result = mysql_query($sql, $db_connection);
//if ($result === false) {
//  die(mysql_error($db_connection));
//} 

//Select database
if (!@mysql_select_db("techdatabase",$db)) 
    {
    exit('<p>Unable to select database.</p>');
        } else {
        //Connection successful
    }
?>

Going to go back to the reg.inc.php, now that I put in the error checking code this appeared "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(title, firstname, lastname, username, password, address, suburb, city, phonenum' at line 1"
So I'll get on to it and keep looking around for any INSERT code Ive missed. Thanks.

Thanks redhead aswell for your help.

[DJMaze]

hmm i think you misunderstood me.
the "error" doesn't belong in connect.inc.php

PHP Code:

<?PHP
//Include file

//Connect to the database
$db=mysql_connect("localhost", "xxx", "xxx")
or die("Could not connect to localhost.");

//Select database
if (!@mysql_select_db("techdatabase",$db))
{
    exit('<p>Unable to select database.</p>'); // or use die(mysql_error($db)) ;)
} else {
    //Connection successful
}

Then inside your reg.inc.php use

PHP Code:

$sql = "INSERT INTO customer SET
           (title, firstname, lastname, username, password, address, suburb, city, phonenumber, dob)
           VALUES ('$title', '$firstname', '$lastname', '$username', '$password', '$address', '$suburb', '$city', '$phonenumber', '$dob')";
$result = mysql_query($sql);
// error check added
// NOTE: uses === (triple =) which checks if the result is boolean AND false because 0==false is also true
if ($result === false) {
  die(mysql_error($db)); // show BUFFY the error why the query fails ;)
} 


[BUFFY]

Yes I didn't quiet understand where to stick it, haven't seen it in any examples before.... I just updated the previous post then saw your next post.

Ok so its just in the reg.inc.php now and yes it is showing buffy errors. Cheers.

[BUFFY]

Ok, changed the INSERT code to:

PHP Code:

$sql = "INSERT INTO customer SET
title='$title',
firstname='$firstname',
lastname='$lastname',
username='$username',
password='$password',
suburb='$suburb',
city='$city',
phonenumber='$phonenumber',
dob='$dob'";

$result = mysql_query($sql); 


Which resulting in getting the message "Thank you! Information entered."
So checked my database and its added a new row with autonumber id 2 (second record in database) but the rest of the feilds are blank.

[DJMaze]

if <form method="POST"> then use:

PHP Code:

$title=mysql_real_escape_string($_POST['title']);
$firstname=mysql_real_escape_string($_POST['firstname']);
$lastname=mysql_real_escape_string($_POST['lastname']);
$username=mysql_real_escape_string($_POST['username']);
$password=mysql_real_escape_string($_POST['password']);
$suburb=mysql_real_escape_string($_POST['suburb']);
$city=mysql_real_escape_string($_POST['city']);
$phonenumber=mysql_real_escape_string($_POST['phonenumber']);
$dob=mysql_real_escape_string($_POST['dob']); 


Else use $_GET

Oh a view posts back i added the following line to your reg.inc.php

PHP Code:

error_reporting(E_ALL); 


It should show have shown you a bunch of "notices" about your code.

[BUFFY]

Thanks again DJ, GET worked fine with it in the form but i ended up have it all on the one page so changed them all to POST after all

I got another question.
I have logged in a tutor or student, and when I go back to the login page it shows the login form even though i have a if statement saying if loggedin==true then dont...
I don't think I am setting the variables to session, as when I type $fullname (which should is set in the login) i can't show it once i leave the include and go to a page which should display the variable.

Im thinking I have to have something like SESSION['$username'] ? and was just wondering if i can have this add my normal $ stuff eg I have this already:

PHP Code:

if($_POST["username"])
{
  $username=$_POST["username"];
  $password=$_POST["password"];
}
elseif($_SESSION["username"])
{
  $username=$_SESSION["username"];
  $password=$_SESSION["password"];
}
else
{
  //Set username and password defaults for security.
  //Stops hackers setting these vairables through the URL 
  $username='';
  $password='';
} 


How do i make sure these are going to be set throughout the pages?

[sde]

anything you set with $_SESSION[] should be available. you just need to use the full variable name including $_SESSION[] to access them.

you can add whatever you want to the session array, .. just use the same method you are using to set the username and password:

PHP Code:

$_SESSION['fullname'] = $fullname 


i don't have time to look over all the last posts, so i'm just warning you that my reply this morning may be out of context.

[DJMaze]

another way is to use $_SESSION for containers like:

PHP Code:

if (!session_is_registered('tutor')) {
    session_register('tutor');
    $_SESSION['tutor']['nickname'] = 'johndoe'; // login name
    $_SESSION['tutor']['password'] = 'fgrhjt4095dgfg'; // encrypted
    $_SESSION['tutor']['realname'] = 'john doe';
    $_SESSION['tutor']['age'] = 15;
}