PHP login with database check

AmiNeo

CodeMonkey
Joined
Jul 28, 2010
Posts
7,436
Country
UK
Region
Kendal, Cumbria
Hi again guys, seem to be asking for help a lot these days :LOL:

I've figured out everything I need for my web coursework this year now and the site is as complete as its getting given my client now no longer needs it and have given up on her business. I just need to get one final things in it before I can hand it in next week.

I have a username and password form in place in the website. I need to verify the username and password against an existing entry in a database (using PHP and SQL) and if its a successful login, allow a div's text to be editable on the page, or output an error message box (which I could easily do in JS) if there is a login error.

I've set up an SQL database in xampp containing the username and password and the website is sitting in the web directory, but I can't find any tutorials that explain the basics of the verification process without going into extra code for registrations and security which aren't necessary for this (as its not going live and there will only ever be one login).

Could someone point me to a tutorial that doesn't over-complicate things with registration and OTT security measures as they're not a requirement and I would rather spend time on that separately once I have the basics understood and the login working.

We haven't and likely wont have had any PHP lectures at all this year and we haven't learned any SQL at all this year sadly yet either, someone in charge of the course decided it would be a great idea to have us do it with databases AFTER the web module finishes. :nuts:

If this were just a php script I could probably get my head around it as I do understand the basics but with SQL needed too, I need it simplifying a bit more.

Thanks as always! (y)
 
I will look later for a tutorial for you.

Easiest easy to understand how to do this is first to play around using a text file holding the login details, rather than a database. This is faster to play around with and quickly shows the ideas of submitting the login details from the form, checking them and verifying the returned results.

With a database you would normally hash and salt the password to securely store it, but as you say, for your purposes you can just store them as plain text in the database.

All you will basically need to do is set the form up so that when it is submitted it queries the database to see if the username exists. You would set the username as the index field in the table. Then if it verifies it exists to then compare the passwords and return a yes/no boolean variable reply as the result, which you can then use to test with an if statement to output either a redirection to log them in, or an error message followed by reloading the same login page.
 
Thanks Harrison!

Anywhere I can find more info on salts and how they're used?

My php right now is pretty basic...

Code:
<?php
function login(){
    $submit = $_POST['submit'];
    $username = $_POST['username'];
    $password = $_POST['password'];

    if($submit){
        $check = mysql_num_rows(mysql_query("SELECT * FROM users WHERE username='$username' AND password='$password'"));
            if($check != '0'){
            echo "<center><br>You're logged in $username";
            session_register("username");
            $username = $_SESSION['username'];
            exit();
        }
        else{
            echo "<br><center>No such username and password combination";
            exit();
        }
    }
}
?>

(HTML CONTENT...)
As you can see, I've created a function 'login()' which should run a basic check script. I've got this working with my login JS form...

Code:
<div id="popupbox"> 
     <form name="login" action="<?php$login()?>" method="post">
         <center>
             Username:
         </center>
         <center>
             <input name="username" size="14" />
         </center>
         <center> 
             Password:
         </center>
         <center>
             <input name="password" type="password" size="14"/
         </center>
         <center>
             <input type="submit" name="submit" value="login" />
         </center>
    </form>
    <br/>
    <a href="javascript:login('hide');">close</a>
</div>
I've also created a basic database with PHPMyAdmin in Xampp... (see attached)
9k=

I've also attached an image of what the site looks like with the hidden login called for completion :D

How do I link the script to said database? I can't seem to figure out what's missing :deadhorse

Note: I pulled the SQL parts from a tutorial so not entirely certain what I'm doing with it. As it is it doesn't appear to be able to locatte the database as i get an error on submission saying database cannot be accessed or found.
 

Attachments

  • db.jpg
    db.jpg
    89.9 KB · Views: 1
  • CMAsite.jpg
    CMAsite.jpg
    96.4 KB · Views: 0
Last edited:
You need to make a mySQL connect before doing queries. For that you need the database URL, database username and database password.

Here's a simple connect sample from php.net:
Code:
<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>
 
Warning- lots of useful junk beneath - go to the bottom for links to learning PHP

as an addition to proteks database connect:
you'll need to make a connection to the database itself, not only to the server.
PHP:
 mysql_select_db('my_database', $link);
the mysql_XXX commands are being deprecated, you should use mysqli_XXX
instead or object oriented $link->query(bla);

The mysql_xx will be removed from php v5.5.0


PHP:
$link = mysqli_connect('my_server / localhost', 'username', 'password', 'my_database');
php.net has some really great examples for their functions:
http://www.php.net/manual/en/mysqli.construct.php


Warning: the following can be overwhelming depending on your php skills.

you might as well take it to clean any user inputs you have

this should be used for input where you use the input to lookup in databases
mysql_real_escape_string()

otherwise you can type in username formfield something like this:

admin' OR id = '1' --
which makes the select statement looke like this

SELECT * FROM users where username = 'admin' OR id = '1' --

the -- is a SQL comment, everything that comes after is ignored.

which evaluates your code to true.

using the mysql_real_escape_string($username)
the input would then be escaped making it look like this:

admin\' OR id = \'1\' --

you also might want to use trim() which removes any whitespace before and after the input word.
PHP:
$data = trim(" smurf "); // added whitespace before and after word
echo $data; // will output "smurf"
This will protect your input against SQL injection, and accidently whitespaces
PHP:
$username = mysql_real_escape_string(trim($username));
There are some other ones like htmlentities() and strip_tags() which are quite useful.
Read up on them on php.net

I find it the easiest to make a new function that does all the cleaning, see the example below

PHP:
function clean_mysql($input)
{
    return mysql_real_escape_string(trim($input));
}
and use it when you retrieve the input

PHP:
<?php
$username = clean_mysql($_GET['username']);
$password = clean_mysql($_POST['password']);

// rest of code goes here
?>
Some hints to check other kinds of input too

if you know input will be an integer. make a function that checks only for that the rest will be discarded (return fals; in a function)

compare if the input is in an array.
PHP:
$input = $_POST['something'];
$input = clean_fixedlist($input, array('smurf', 'car', ''))
the function clean_fixed() would use in_array(), in_array() you can find in php.net's great database.

This will check input against, you guessed it; smurf, car or '' (empty)
and pass it on to the variable, else the function clean_fixed() should return false.

there are many ways of doing this of course


Paid video tutorial
http://www.lynda.com --> php tuts: http://www.lynda.com/PHP-training-tutorials/282-0.html

Free online interactive teaching <-- this is great
http://www.codecademy.com
 
Last edited:
Hey guys, think i've got it now, its working in xampp anyway :D

I need to upload this to the unis private web servers which they use internally. It's file structure is the same as any other web server.

Does anyone know if it matters specifically where I put the database? Will I need to alter my script to show its file path relative to the php file? I'm unsure as it doesn't need this in xampp :unsure:

Thanks for all the help and links! (y)
 
If your university's servers have PHPMyAdmin installed you can use it to import your database and it should go into the correct place.
 
Hey guys, think i've got it now, its working in xampp anyway :D

I need to upload this to the unis private web servers which they use internally. It's file structure is the same as any other web server.

Does anyone know if it matters specifically where I put the database? Will I need to alter my script to show its file path relative to the php file? I'm unsure as it doesn't need this in xampp :unsure:

Thanks for all the help and links! (y)

Do you mind me asking what course you're doing? The reason being is that from all of the different topics you've started it looks as though you're doing things completely out of order and and it seems they haven't taught the fundamentals of how this stuff works before getting you do a project pulling all the different components together.

For example, if you're using a mysql database to store your user credentials for your login form then the concept of 'file paths' to the database is irrelevant. You talk over a network socket to the database server, not directly via any filesystem mechanism - very often then database server is not even on the same machine as your web server.

Anyhow, to answer your final question, you'll need to speak to your university IT dept to get them to create a database on their mysql server and add a user for you. At that point you can then create your tables and populate your data to get it working as you have on your dev environment.
I seriously doubt you'll have free access to phpMyAdmin and access to create new databases as you desire.

[disclaimer: I'm a unix infrastructure manager at a UK university]
 
Hi megatron, I've been setting it up on Xampp virtual server prior to uploading it so I know everything is working. I have a personal web server which I will be uploading it to for them to see it live and the files will be handed in on physical media or uploaded in a zip file for marking.

The server side / database part is simply a check for an existing entry on submission, and nothing more so shouldn't need to be too complex, though it will be worth about 8% of my overall mark for this module. I'm sure I will pass based on what I already have but of course I do want as high a mark as possible from it.

You are correct however that nothing is being done correctly at our place. It is actually a foundation degree done in coalition between The university of Chester and Wirral Metropolitan College and everything that's technical isn't being lectured on aside from the networking which is being done pretty well. We all (the ones of us that are actually interested, which is maybe 3 of us) pretty much only have the books we've bought and the web to learn from. Our web lecturer has avoided giving lectures all year and made every lesson into a workshop up to now. The one lesson he did try to lecture on JavaScript, I had to tell him what some things did as he is 'learning alongside us' and if I'm completely honest I don't believe he knows any PHP at all. He seems to know databases and can apparently 'teach us SQL in an hour' but has yet to make good on his word and has been off 2 weeks running so far. Our web is also due to be handed in next week... :whistle:

Because of this most people have barely learned a thing, I'm doing my best with it though because I really want to learn this stuff, and passing this year gets me the fundation degree and onto the third year of a proper university's (Chester) computer science degree course for a bachelor's. We've tried complaining but we just get excuses. The college has apparently tried to find people that can lecture us but this guy is the best they've come up with. They've done a reasonably good job with the C# tutor this year to be fair but it took them 3 months into the year to find her. Personally I feel I could be doing a better job myself, as I'd at least bother to learn something new and relevant to teach each week, if that was my job and all I had to study.

I'm very disappointed overall and this is being reflected in the surveys Chester keep giving us. But at the same time I'm one of few on the course that actually care and most people have stopped turning up. :picard

There isn't much I can do to learn more PHP in the 2 days we have left before the hand in, but I can learn enough to get this working. I have most of my server side scripting covered thanks to what I've taught myself, so will at least pass.


The course on a whole is leaving me feeling frustrated and exhausted, but I'll be damned if that's stopping me from understanding what I need to get where I want to be.
 
Back
Top Bottom