[SOLVED] password protection in phpmyadmin

dazzclub · March 25, 2008

Hi guys,

I am creating a smiple login for, so i can learn php and mysql really.

A quick question.

I have created a table called users, in it a field called password. This password is not protected . I chose "varchar" instead of "password" or "md5" using the drop down menu. How can i go back and change it to password or md5 instead of varchar.

I cant seem to find the dropdown menu again to change it, so i assuming it requires some sql??

can anyone help me out on this??

Kind regards

Dazzclub

ansarka · March 25, 2008

let the database field be varchar itself

you can write a simple update query

selecting each fields in DB and updating the password field with md5 encryption .

for md5 conversion you can use php function md5

$pwdencry=md5($oldpassword)

dazzclub · March 25, 2008

o rite, that simple. cheers.

May i run another question by you please?

I have a form that simply asks for your username and a password. Once the user has entered their correct password they will be taken to a page that displays their content. At the moment i have just put their name to show only.

I am using their name as a way of identifying them and also what content to retrieve from the database aswell.

Here is the code my form

-----------------------

<form method="post" action="login.php">
<ul class="form">
<li>Please enter your name: <input type="text" name="username" id="username"></li>
<li>Please enter reference password: <input type="text" name="password" id="password"></li>
<li><input type="submit"  name="submit" value="submit"   title="Click here to login" ></li>
<ul>	
</form>

---------------------

Here is the code for login.php

----------------------------------

<?php
require_once("includes/connection.php");


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

////to protect from sql injection
// To protect MySQL injection (more detail about MySQL injection)
$username = stripslashes($username);
$password = stripslashes($password);

//just incase they have forgotton something
$errors .= (empty($username)) ? "<br />You have forgotton to include your username." : "";
$errors .= (empty($password)) ? "<br />Password incorrect." : "";

$sql="SELECT * FROM members WHERE username='$username' and password='$password'";
$result=mysqli_query($connection, $sql);

// Mysql_num_row is counting table row
$count=mysqli_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
session_register("username");
session_register("password"); 
header("location:login_success.php");
}
else {
echo "Wrong Username or Password";
}

ob_end_flush();
?>

--------------------------------------

and here is the code for login_success.php

-------------------------------------------

<?php
require_once("includes/connection.php");

if ( (isset($_GET['name'])) && (is_numeric($_GET['name'])) ) { //correctly accessed 
$name=$_GET['name']; 
} else { 
$errors[] = 'You have accessed this page incorrectly.'; 
} 

$details = "SELECT * FROM contact WHERE name = $name ";
	If ($r = mysqli_query($connection, $details)) { 
	//sending the query to the mySQL server
	While ($row = mysqli_fetch_array($r)) { 
	//inputs the data into the table


	$name = $row['name'];
	}
}	
?>
<html>
<head>
<title>Order form</title>
<link rel="stylesheet" type="text/css" href="styles/style.css" />	
<head>
<body>
<div id="container">
<div id="holder">
<div id="header">header goes here</div>
<div id="main">
<form method="post" action="order.php">
<ul class="form">
<li>Hello <?php echo $name; ?> </li>
</ul>	
</form>
</div>
<div id="footer"></div>
<div>
</div>
</div>		
</body>
</html>

---------------------

kind regards

Dazzclub

Is my post too confusing??

wildteen88 · March 25, 2008

Modified your code abit:

<?php

require_once 'includes/connection.php';

// check that the form is submitted
if(isset($_POST['submit']))
{
    // validate username
    if(isset($_POST['username']) && !empty($_POST['username']))
    {
        // use the built in mysql real escape string function to protect agains SQL Injection
        $username = mysqli_real_escape_string($connection, $_POST['username']);
    }
    else
    {
        // username does not validate, define an error
        $errors[] = 'You have forgotton to include your username.';
    }

    // we apply the same for the password field.
    if(isset($_POST['password']) && !empty($_POST['password']))
    {
        $username = mysqli_real_escape_string($connection, $_POST['password']);
    }
    else
    {
        $errors[] = 'Password not provided';
    }

    // chekc that no errors have been set, if so display them
    if(isset($errors) && is_array($errors))
    {
        echo 'Errors: <ul><li>' . implode('</li><li>', $errors) . '</li></ul>';
    }
    // no errors are set so we'll continue
    else
    {
        // run query
        $sql    = "SELECT * FROM members WHERE username='$username' and password='$password'";
        $result = mysqli_query($connection, $sql);

        // check that the query return only ONE result
        if(mysqli_num_rows($result) == 1)
        {
            $_SESSION['is_logged_in'] = true;

            // get result set from the query and assign it to the 'user' session.
            $row = mysqli_fetch_assoc($result);
            $_SESSION['user'] = $row;

            // redirect to the login_success.php
            header('Location: login_success.php');
            exit;
        }

        // query failed, display error
        echo "Wrong Username or Password";
    }
}
// for was not submitted, display error
else
{
    echo 'Please use the login form for logging in';
}
?>

<?php
session_start();

// check that the  user has logged in
if(isset($_SESSION['is_logged_in']) && $_SESSION['is_logged_in'] !== true || !isset($_SESSION['is_logged_in']))
{
    die('You must be logged in to view this page!');
}

?>
<html>
<head>
<title>Order form</title>
<link rel="stylesheet" type="text/css" href="styles/style.css" />
<head>
<body>
<div id="container">
<div id="holder">
<div id="header">header goes here</div>
<div id="main">
You are logged in!

Session data:
<?php echo '<pre>' . print_r($_SESSION['user'], true) . '</pre>'; ?>

</div>
<div id="footer"></div>
<div>
</div>
</div>
</body>
</html>

I have modified it so all user details get saved to a session variable called 'user'. This will save you from have to run an sql query to retrieve the user details from the database for every page.

ansarka · March 25, 2008

if you are storing the md5 password in db you have to convert the user entered password to md5 before checking i in sql

a small change in above code

$password=md5($password);
$sql    = "SELECT * FROM members WHERE username='$username' and password='$password'";
$result = mysqli_query($connection, $sql);

lordfrikk · March 25, 2008

Just a side-note concerning MySQL PASSWORD() function from MySQL manual:

The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider MD5() or SHA1() instead. Also see RFC 2195, section 2 (Challenge-Response Authentication Mechanism (CRAM)), for more information about handling passwords and authentication securely in your applications.

http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html#function_password

dazzclub · March 25, 2008

@wildteen88

The query keeps failing, it displays "Wrong Username or Password".

Can you spot anything in the edited code you gave me. (Thanks for doing this, its really nice of you, i really appreciate it dude)

ansarka · March 25, 2008

$password=md5($password);

ADD ABOVE CODE ABOVE

$sql = "SELECT * FROM members WHERE username='$username' and password='$password'";

$result = mysqli_query($connection, $sql);

dazzclub · March 25, 2008

Hi,

the login.php below is displaying this error "Wrong Username or Password "once i submit a username and password. I have double checked the database and both usernam and password are correct

here is the code for the login.php script. I cant seem to point out what or where the problem is

-------------------------------

<?php

require_once 'includes/connection.php';

// check that the form is submitted

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

{

// validate username

if(isset($_POST['username']) && !empty($_POST['username']))

{

// use the built in mysql real escape string function to protect agains SQL Injection

$username = mysqli_real_escape_string($connection, $_POST['username']);

}

else

{

// username does not validate, define an error

$errors[] = 'You have forgotton to include your username.';

}

// we apply the same for the password field.

if(isset($_POST['password']) && !empty($_POST['password']))

{

$username = mysqli_real_escape_string($connection, $_POST['password']);

}

else

{

$errors[] = 'Password not provided';

}

// chekc that no errors have been set, if so display them

if(isset($errors) && is_array($errors))

{

echo 'Errors: <ul><li>' . implode('</li><li>', $errors) . '</li></ul>';

}

// no errors are set so we'll continue

else

{

// run query

$password=md5($password);

$sql = "SELECT * FROM members WHERE username= '$username' AND password= '$password' ";

$result = mysqli_query($connection, $sql);

// check that the query return only ONE result

if(mysqli_num_rows($result) == 1)

{

$_SESSION['is_logged_in'] = true;

// get result set from the query and assign it to the 'user' session.

$row = mysqli_fetch_assoc($result);

$_SESSION['user'] = $row;

// redirect to the login_success.php

header('Location: login_success.php');

exit;

}

// query failed, display error

echo "Wrong Username or Password";

}

// for was not submitted, display error

else

{

echo 'Please use the login form for logging in';

}

?>

----------------

Can any of you guys see where i am going wrong??

kind regards

Dazzclub

BlueSkyIS · March 25, 2008

is the stored password md5()'d? probably not, because that would be bad. if the stored password isn't md5()'d, this won't match

$password=md5($password);
$sql    = "SELECT * FROM members WHERE username= '$username' AND password= '$password' ";

dazz_club · March 25, 2008

Hi BlueSkyIS

Is that causing the login.php script "not to work" because md5() doesnt match??

Here goes.........

wildteen88 · March 25, 2008

Hiya guys, sorry about that I had a bug in my code, see this line:

$username = mysqli_real_escape_string($connection, $_POST['password']);

It should have been:

$password = md5($_POST['password']);

Also you'll need to change the top two lines of login.php to this:

<?php
session_start(); // session_start() must be called on all pages which uses sessions.
require_once 'includes/connection.php';

Corrected code:

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

// check that the form is submitted
if(isset($_POST['submit']))
{
    // validate username
    if(isset($_POST['username']) && !empty($_POST['username']))
    {
        // use the built in mysql real escape string function to protect agains SQL Injection
        $username = mysqli_real_escape_string($connection, $_POST['username']);
    }
    else
    {
        // username does not validate, define an error
        $errors[] = 'You have forgotton to include your username.';
    }

    // we apply the same for the password field.
    if(isset($_POST['password']) && !empty($_POST['password']))
    {
        $password = md5($_POST['password']);
    }
    else
    {
        $errors[] = 'Password not provided';
    }

    // chekc that no errors have been set, if so display them
    if(isset($errors) && is_array($errors))
    {
        echo 'Errors: <ul><li>' . implode('</li><li>', $errors) . '</li></ul>';
    }
    // no errors are set so we'll continue
    else
    {
      $sql    = "SELECT * FROM memebers WHERE username= '$username' AND password= '$password' ";
      $result = mysqli_query($connection, $sql);

        // check that the query return only ONE result
        if(mysqli_num_rows($result) == 1)
        {
            $_SESSION['is_logged_in'] = true;

            // get result set from the query and assign it to the 'user' session.
            $row = mysqli_fetch_assoc($result);
            $_SESSION['user'] = $row;

            // redirect to the login_success.php
            header('Location: login_success.php');
            exit;
        }

        // query failed, display error
        echo "Wrong Username or Password";
    }
}
// for was not submitted, display error
else
{
    echo 'Please use the login form for logging in';
}
?>

Another thing I forgot to mention dazz_club any page which requires login, you'll need to add the following few lines of code at the top of every page:

<?php
session_start();

// check that the  user has logged in
if(isset($_SESSION['is_logged_in']) && $_SESSION['is_logged_in'] !== true || !isset($_SESSION['is_logged_in']))
{
    die('You must be logged in to view this page!');
}

?>

To allow the user to logout out you'll need to use this:

<?php
session_start();

// check that the  user has logged in
if(isset($_SESSION['is_logged_in']) && $_SESSION['is_logged_in'] !== true || !isset($_SESSION['is_logged_in']))
{
    die('You are already logged out');
}

unset($_SESSION);
session_destroy();

?>
<h1>Logged out!</h1>

dazz_club · March 25, 2008

Hey thanks for getting back to me wildteen88. Cheers!!!!

wildteen88 · March 25, 2008

No problem. Glad I managed to help.

dazzclub · March 26, 2008

Hi wildteen88,

I have used the edited login.php script you provided but when i enter a username and password it displays;

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in E:\wamp\www\bulletins\login.php on line 42

Wrong Username or Password

Have I done something wrong??

Cheers

Darren

wildteen88 · March 26, 2008

Sorry I had a typo in the query:

$sql    = "SELECT * FROM `members` WHERE `username`= '$username' AND `password`= '$password'";
$result = mysqli_query($connection, $sql) or die('Query Error:<br />Query: <tt>'.$sq;.'</tt><br />Error: ' . mysqli_error($connection));

dazzclub · March 26, 2008

Hi wildteen88 ,

I think i am doing something wrong. As it still doesnt seem to work. I think i will buzz of and stop annoying you with requests and try to make some progress of my own (fingers crossed).

Here is how the login.php script stands

------------------------------------

<?php

session_start();

require_once 'includes/connection.php';

// check that the form is submitted

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

{

// validate username

if(isset($_POST['username']) && !empty($_POST['username']))

{

// use the built in mysql real escape string function to protect agains SQL Injection

$username = mysqli_real_escape_string($connection, $_POST['username']);

}

else

{

// username does not validate, define an error

$errors[] = 'You have forgotton to include your username.';

}

// we apply the same for the password field.

if(isset($_POST['password']) && !empty($_POST['password']))

{

$password = md5($_POST['password']);

}

else

{

$errors[] = 'Password not provided';

}

// chekc that no errors have been set, if so display them

if(isset($errors) && is_array($errors))

{

echo 'Errors: <ul><li>' . implode('</li><li>', $errors) . '</li></ul>';

}

// no errors are set so we'll continue

else

{

$sql = "SELECT * FROM `members` WHERE `username`= '$username' AND `password`= '$password'";

$result = mysqli_query($connection, $sql) or die('Query Error:<br />Query: <tt>'.$sq; .'</tt><br />Error: ' . mysqli_error($connection));

// check that the query return only ONE result

if(mysqli_num_rows($result)==1)

{

$_SESSION['is_logged_in'] = true;

// get result set from the query and assign it to the 'user' session.

$row = mysqli_fetch_assoc($result);

$_SESSION['user'] = $row;

// redirect to the login_success.php

header('Location: login_success.php');

exit;

}

// query failed, display error

echo "Wrong Username or Password";

}

// for was not submitted, display error

else

{

echo 'Please use the login form for logging in';

}

?>

-----------------------------

when i try to use it, i displays Parse error: syntax error, unexpected ';' in E:\wamp\www\bulletins\login.php on line 39 which is

$result = mysqli_query($connection, $sql) or die('Query Error:<br />Query: <tt>'.$sq; .'</tt><br />Error: ' .

I have tried removing it and it displays this Wrong Username or Password. I had alos noticed $sq, is that a typo error. I changed this to $sql but still no luck.

Cheers for all your help dude.

wildteen88 · March 26, 2008

Oh Bulls! Yeah $sql; should be $sql

It should work now fingers crossed. I have tested the code this time.

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

// check that the form is submitted
if(isset($_POST['submit']))
{
    // validate username
    if(isset($_POST['username']) && !empty($_POST['username']))
    {
        // use the built in mysql real escape string function to protect agains SQL Injection
        $username = mysqli_real_escape_string($connection, $_POST['username']);
    }
    else
    {
        // username does not validate, define an error
        $errors[] = 'You have forgotton to include your username.';
    }

    // we apply the same for the password field.
    if(isset($_POST['password']) && !empty($_POST['password']))
    {
        $password = md5($_POST['password']);
    }
    else
    {
        $errors[] = 'Password not provided';
    }

    // chekc that no errors have been set, if so display them
    if(isset($errors) && is_array($errors))
    {
        echo 'Errors: <ul><li>' . implode('</li><li>', $errors) . '</li></ul>';
    }
    // no errors are set so we'll continue
    else
    {
        $sql    = "SELECT * FROM `members` WHERE `username`= '$username' AND `password`= '$password'";
        $result = mysqli_query($connection, $sql) or die('Query Error:<br />Query: <tt>'.$sql.'</tt><br />Error: ' . mysqli_error($connection));

        // check that the query return only ONE result
        if(mysqli_num_rows($result) == 1)
        {
            $_SESSION['is_logged_in'] = true;

            // get result set from the query and assign it to the 'user' session.
            $row = mysqli_fetch_assoc($result);
            $_SESSION['user'] = $row;

            // redirect to the login_success.php
            header('Location: login_success.php');
            exit;
        }

        // query failed, display error
        echo "Wrong Username or Password";
    }
}
// for was not submitted, display error
else
{
    echo 'Please use the login form for logging in';
}
?>

dazz_club · March 27, 2008

Hi wildteen88,

I think we are getting somewhere and i am coming to the conclusion that the problem maybe my table that i am querying,

$sql= "SELECT * FROM members WHERE username= $username AND password = $password"; As when i entered the username and password then pressed submit it displayed Query Error:

Query: SELECT * FROM members WHERE username= darren AND password = 1ff6a143a805fa679534bd92eed02ed1

Error: Unknown column 'darren' in 'where clause'

The members table looks like this (if this helps though)

-- Table structure for table `members`
-- 

CREATE TABLE `members` (
  `id` int(4) NOT NULL auto_increment,
  `username` varchar(65) character set utf8 collate utf8_unicode_ci NOT NULL,
  `password` varchar(65) character set utf8 collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `members`
-- 

INSERT INTO `members` (`id`, `username`, `password`) VALUES 
(1, 'darren', 'azzopardi'),
(2, 'gracie', 'alice');

kind regards

Dazzclub

I will grab a coffe and look at this problem with fresh eyes.

wildteen88 · March 27, 2008

Wheres the quotes gone? The query code should be:

$sql= "SELECT * FROM members WHERE username='$username' AND password='$password'";

Did you modify the code in some way? All string values need to be wrapped within quotes in a query. Otherwise MySQL will think you're referencing a column, which is why you're retrieving the error.

dazz_club · March 27, 2008

Sorry wildteen88 for removing the quotes. I strongly believe it must be the table, as when i enter the username (darren) and password (azzopardi) then press submit, it informs me i am using the "Wrong Username or Password".

hmmmm.

Thanks for all you help on this dude

wildteen88 · March 27, 2008

I see you attached your database schema. It is because you don't store the md5 hash for the passwords in the password field for your members table. You only store it as raw text.

The script is encrypting the password when the form is submitted and is comparing an unencryupted password (in the table) to an encrypted password (from the script).

You'll need to change the passwords in your database to a md5 hash in order for the script to work. For a simple fix, run this script only once!

<p><font color=red>RUN THIS SCRIPT ONCE! RUNNING THIS SCRIPT MORE THAN ONCE WILL RE-ENCRYPT THE MD5 HASHES!</font></p>
<?php
require_once 'includes/connection.php';

$sql = 'SELECT * FROM members';
$result = mysqli_query($connection, $sql) or die(mysqli_error($connection));

while($row = mysqli_fetch_assoc($result))
{
    $sql = "UPDATE members SET `username`='".$row['username']."', `password`='".md5($row['password'])."' WHERE id=".$row['id'];

    echo '<pre>' . $sql . '</pre>';
    echo 'MD5 Hashed password for "' . $row['username'] . '"<br />';

    mysqli_query($connection, $sql) or die(mysqli_error($connection));
}

echo 'Affected rows: ' . mysqli_affected_rows($connection);

?>
<p><font color=red>RUN THIS SCRIPT ONCE! RUNNING THIS SCRIPT MORE THAN ONCE WILL RE-ENCRYPT THE MD5 HASHES!</font></p>

dazz_club · March 27, 2008

I thought it might have been this so i removed md5 from here

$password = md5($_POST['password']);

so it looks like this

$password = ($_POST['password']);

double checked the username and password in the members section and completed the form and it didnt work but at this time i may have removed the quotes from the string i.e '$username'.

I will double check.

Thanks dude, i really appreciate your help on this.

dazz_club · March 27, 2008

woahh i think we have gotton somewhere.

I double checked the script, removed the md5 and when i tried the form it went to login_success.php and displayed

Session data: 
Array
(
    [id] => 1
    [username] => darren
    [password] => darren
)

so thats good right??

I need to run your script as i havent done it yet.

Cheers dude.

Dazzclub

wildteen88 · March 27, 2008

I need to run your script as i havent done it yet.

If you are running the script to hash the passwords in the database, you'll need to undo what you did here:

I thought it might have been this so i removed md5 from here
$password = md5($_POST['password']);
so it looks like this
$password = ($_POST['password']);

Sign In

[SOLVED] password protection in phpmyadmin

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information