Solved

Login script issues after MySQL upgraded from 5.1 to version 5.5

Posted on 2013-05-18
32
698 Views
Last Modified: 2013-05-19
Experts,
Over the last 3 years, I've been using the same login script for my users to login to my website.  I've never had any issues.

3 days ago, my web host upgraded MySQL from version 5.1 to version 5.5.
All of my user logins are now failing.  The mysql upgrade is the only thing that recently changed.  In looking at my code, I can't pinpoint what the problem is.
The login function below returns FALSE at every login attempt and the text "Login Failed" is now outputted.

function process_login() {
	global $str_login_error;
	$uname = trim($_POST["uname"]);
	$psw = trim($_POST["psw"]);
//    dbConnect('allgo5_ncaa');
	$sql = "SELECT * FROM USERS WHERE
        userid = '$uname' AND password = PASSWORD('$psw')";
	$rs = mysql_query($sql);
	//echo "<!--$sql-->";
	if ($rs && $data=mysql_fetch_array($rs)) {
		
		$_SESSION["user_login"] = "yes";
		$_SESSION["user_FirstName"] = $data["FirstName"];
		$_SESSION["user_fullname"] = $data["FirstName"] . " " . $data["LastName"];
		$_SESSION["UserID"] = $data["ID"];
		$_SESSION["uid"] = $data["userid"];
		$_SESSION["pwd"] = $psw;
		
		// Set the user values after clearing the previous values for next logging the user in if he doesn't logout
		// Reset cookie
		setcookie("user_login", "yes", time()-60*60*24*100, "/");
		setcookie("user_FirstName", $data["FirstName"], time()-60*60*24*100, "/");
		setcookie("user_fullname", $data["FirstName"] . " " . $data["LastName"], time()-60*60*24*100, "/");
		setcookie("UserID", $data["ID"], time()-60*60*24*100, "/");
		setcookie("uid", $data["userid"], time()-60*60*24*100, "/");
		setcookie("pwd", $psw, time()-60*60*24*100, "/");
		
		// Set cookie
		setcookie("user_login", "yes", time()+60*60*24*100, "/");
		setcookie("user_FirstName", $data["FirstName"], time()+60*60*24*100, "/");
		setcookie("user_fullname", $data["FirstName"] . " " . $data["LastName"], time()+60*60*24*100, "/");
		setcookie("UserID",$data["ID"], time()+60*60*24*100, "/");
		setcookie("uid", $data["userid"], time()+60*60*24*100, "/");
		setcookie("pwd", $psw, time()+60*60*24*100, "/");
		header('Location: '.$_SERVER['PHP_SELF']);	
		return TRUE;
	}
	$str_login_error = "Login failed! &nbsp; &nbsp; &nbsp; ";
	return FALSE;
}

Open in new window


I should mention that if the users still have their session open, they can fully use my site.  The problem is affecting users that don't have an open session and need to log in.
All other database interactions on my website work fine except for this login issue.

phpinfo is here:  http://www.officepickem.com/phpinfo.php

Please let me know if you have any ideas.  I'm really stuck here.

Thanks,
-dsg
0
Comment
Question by:dsg138
  • 15
  • 11
  • 6
32 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39177208
The general design pattern of a "login" script is here.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_2391-PHP-login-logout-and-easy-access-control.html

I'll take a look and see if I can detect anything in the code above.  Do you have a link to the phpinfo() before the upgrade (maybe on a shadow server)?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39177221
I am immediately suspicious of password hashing.  
http://dev.mysql.com/doc/refman/5.5/en/password-hashing.html

Can you try printing out some of the information?  For example, does the query actually work?  You can find that out by testing the value of $rs after this statement:

$rs = mysql_query($sql);
if (!$rs) { echo $sql; trigger_error( mysql_error() ); }

Can you try something like SELECT PASSWORD('$psw') and print out the result.  Compare that to the contents of the data base table.

Can you verify that there have not been any changes in magic_quotes or register_globals?
0
 

Author Comment

by:dsg138
ID: 39177232
Thanks Ray,
Ironically, I was just reading your article on the mysql upgrade and wondered if the issue is because I didn't switch over to MySQLi or PDO.
For example, changing from:  
$rs = mysql_query($sql);
to this:
$rs = $mysqli->query($sql);

I don't have a phpini() output before the upgrade.
But I will put together a test page to see if we can output some of the results to make sure the query actually works.

Thanks,
-dsg
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39177243
The switch from MySQL to MySQLi has to be "whole hog" and can't be done on a query-by-query basis.  I would not expect it to be a problem at PHP 5.3 levels.  Eventually you will have to make the change, but I do not think that's the problem here.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39177246
Any chance there was a change to session.auto_start ?
0
 
LVL 82

Expert Comment

by:hielo
ID: 39177281
>> //    dbConnect('allgo5_ncaa');
Line 5 of your post seems to suggest that you have are not connecting to the db before executing the query.  You must be connected to the db before calling mysql_query().  Try getting rid of the leading "//".
0
 

Author Comment

by:dsg138
ID: 39177286
I'm not aware of any changes to session.auto_start.

I put a test page up.  The $rs is now outputted at the top.
http://www.officepickem.com/rftwtest.php

The user password:  test1/test1 should work.
It appears that $rs is correct.  This is exactly what I'd expect it to look like, even with the PASSWORD() hash.
0
 

Author Comment

by:dsg138
ID: 39177296
Sorry for the confusion about the dbconnect.
The db connection is done earlier on this php page.
The commented connection is old and shouldn't be there.
I only pasted the function.  If it helps, I can attach the entire php page?
Let me know.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39177299
Here's a script you may be able to experiment with.

<?php // RAY_temp_dsg138.php
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php
// MAN PAGE: http://php.net/manual/en/function.error-log.php


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $err <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!mysql_select_db($db_name, $db_connection))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $err <br/>";
    trigger_error('NO DATA BASE', E_USER_ERROR);
}

// CREATING A TABLE
$sql
=
"
CREATE TEMPORARY TABLE myTable
( myKey INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, name VARCHAR(24) NOT NULL DEFAULT ''
, pass VARCHAR(41) NOT NULL DEFAULT ''
)
"
;
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    trigger_error("FAIL: $sql $errmsg", E_USER_ERROR);
}

// INSERTING A ROW
$sql = "INSERT INTO myTable ( name, pass ) VALUES ( 'Ray', PASSWORD('Hello') )";
if (!$res= mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    trigger_error("FAIL: $sql $errmsg", E_USER_ERROR);
}

// INSPECTING A ROW
$sql = "SELECT * FROM myTable LIMIT 1";
if (!$res= mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    trigger_error("FAIL: $sql $errmsg", E_USER_ERROR);
}
$row = mysql_fetch_object($res);
var_dump($row);

// LOOKING AT PASSWORD FUNCTION
$sql = "SELECT PASSWORD('Hello'), UPPER(CONCAT('*', CAST(SHA1(UNHEX(SHA1('Hello'))) AS CHAR))) AS SamePassword";
if (!$res= mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    trigger_error("FAIL: $sql $errmsg", E_USER_ERROR);
}
$row = mysql_fetch_object($res);
var_dump($row);

// INSPECTING A ROW
$sql = "SELECT * FROM myTable WHERE pass = PASSWORD('Hello') LIMIT 1";
if (!$res= mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    trigger_error("FAIL: $sql $errmsg", E_USER_ERROR);
}
$row = mysql_fetch_object($res);
var_dump($row);

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39177304
The $rs is now outputted at the top.
Eh?  What should I be looking for?

Please try outputting it this way: var_dump($rs);
0
 
LVL 82

Expert Comment

by:hielo
ID: 39177305
On the test page that you have, put the following:
...
$rs = mysql_query($sql);
	echo "$sql <br>";
if( !$rs )
{
  echo mysql_error();
}
else
{
$row=mysql_fetch_assoc($rs);
print_r($row);
}
...

Open in new window

0
 

Author Comment

by:dsg138
ID: 39177314
OK, I added that code at the top.

I'll try your script now to see if I can get it to work.
0
 

Author Comment

by:dsg138
ID: 39177326
0
 
LVL 82

Expert Comment

by:hielo
ID: 39177343
On my post above (ID: 39177305), did you see the "if" clause or the "else" clause?
0
 

Author Comment

by:dsg138
ID: 39177355
Yes, I have the if and else clauses.
Neither are outputting below the SQL?

$rs = mysql_query($sql);

echo "$sql <br>";
if(!$rs)
{
  echo mysql_error();
}
else
{
$row=mysql_fetch_assoc($rs);
print_r($row);
}

Open in new window

0
 

Author Comment

by:dsg138
ID: 39177371
Ray, in your post:  39177243
Just to clarify, the current MySQL version that is running is 5.5.30.
Not 5.3.  Not sure if that makes a difference, but just wanted to clarify.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 82

Expert Comment

by:hielo
ID: 39177388
>>If it helps, I can attach the entire php page?
That would help.
0
 

Author Comment

by:dsg138
ID: 39177427
Attached is the entire header file which contains the user login area.
toptest.php
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39177642
http://officepickem.com/RAY_temp_dsg138.php produces the same output on my server as on your server.  

I think you may want to completely refactor this script soon. Please see the large red warning label here:
http://php.net/manual/en/function.session-unregister.php

For the instant case, can you please install this script somewhere and post a link.  It will help visualize information about the query and the results.

<?
session_set_cookie_params(3600 * 24 * 7);
session_start();
include_once("/home/allgo5/public_html/officepickem.com/_lib_kkl.php");
include_once("/home/allgo5/public_html/officepickem.com/common.php");
include_once("/home/allgo5/public_html/officepickem.com/db.php"); 
//$ajax = (isset($_REQUEST['callType']) && $_REQUEST['callType'] == 'ajax') ? true : false;
require_once('payment_terminal/config.php');  // include the config file
require_once('payment_terminal/paypal.class.php');  // include the class file
$paypal = new paypal_class;             // initiate an instance of the class
$paypal->paypal_url = 'https://www.paypal.com/cgi-bin/webscr';     // paypal url

dbConnect('allgo5_ncaa');

$str_login_error = "";
if (isset($_POST["kkl_dodo_logout"]) && $_POST["kkl_dodo_logout"]=="yes") {
	do_logout();
} else if (isset($_POST["kkl_dodo"]) && $_POST["kkl_dodo"]=="1") {
	process_login();
}


?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<HEAD>

<link rel="shortcut icon" type="image/x-icon" href="/favicon.ico">
<title>Office Pickem | Easy and Fun Pick'em style games | Baseball, Football, College and more</title>
</HEAD>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<link href="style.css" rel="stylesheet" type="text/css" />
<link href="layout.css" rel="stylesheet" type="text/css" />

<script type="text/javascript" src="scriptnote.js"></script>

<script src="js/jquery-1.3.2.min.js" type="text/javascript"></script>
<script src="js/jquery.featureList-1.0.0.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript" src="js/prototype.js"></script>

<script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>
<script src="js/jquery-1.6.2.min.js" type="text/javascript"></script>

<script src="http://ajax.microsoft.com/ajax/jQuery/jquery-1.9.1.min.js" type="text/javascript"></script>


<script language="javascript" type="text/javascript" src="js/blockUI.js"></script>
<script language="javascript" type="text/javascript" src="js/common.js"></script>
<!--[if lt IE 7]>
	<link href="ie_style.css" rel="stylesheet" type="text/css" />
   <script type="text/javascript" src="js/ie_png.js"></script>
   <script type="text/javascript">
       ie_png.fix('.png, .header-box .left-top-corner, .header-box .right-top-corner, .header-box .border-top, .header-box .indent, .description');
   </script>
<![endif]-->

<script type="text/javascript">
function do_logout() {
	tmpObj = document.getElementById("kkl_dodo_logout");
	if (tmpObj) {
		tmpObj.value = "yes";
		document.frmKKL.submit();
	}
}

function _submit(teamid) {
document.form.teamid.value=teamid;
}

</script>



<script type="text/javascript">
function do_logout() {
	tmpObj = document.getElementById("kkl_dodo_logout");
	if (tmpObj) {
		tmpObj.value = "yes";
		document.frmKKL.submit();
	}
}

</script>
<body id="page1">
<div id="fb-root"></div>
<script>(function(d, s, id) {
  var js, fjs = d.getElementsByTagName(s)[0];
  if (d.getElementById(id)) return;
  js = d.createElement(s); js.id = id;
  js.src = "//connect.facebook.net/en_US/all.js#xfbml=1";
  fjs.parentNode.insertBefore(js, fjs);
}(document, 'script', 'facebook-jssdk'));</script>


  <div id="main">
    <!-- header -->
    <div id="header">
      <!-- .header-box -->
      <div class="header-box">
      	<div class="left-top-corner">
        	<div class="right-top-corner">
          	<div class="border-top"></div>
          </div>
        </div>
        <div class="indent">
        	<div class="row-1">
          	<div class="wrapper">
            	<div class="fleft"><a href="index.php"><img src="images/logo.gif" alt="" /></a></div>
              <div class="fright">
              	<!-- .adv-nav -->
              	<ul class="adv-nav">
                	<li>Welcome to Office Pickem! !</li>
                </ul>
              	<!-- /.adv-nav -->
                <form name="frmKKL" action="<?=$_SERVER["REQUEST_URI"]?>" method="post">
<input type="hidden" name="kkl_dodo" id="kkl_dodo" value="1" />
<input type="hidden" name="kkl_dodo_logout" id="kkl_dodo_logout" value="" />
<? if (!is_user_login()) { ?>
        <font color="#FF0000">
          <?=$str_login_error?>
         </font> <font color="#0000FF">Member Name:</font> 
          <input type="text" name="uname" />
          <font color="#0000FF"> Password:</font> 
          <input type="password" name="psw" />
          <input type="submit" name="Submit" value="  Sign in!  " />
          <a href="forgot-password.php">Forgot password?</a><br />
          <? } else { ?>
          <strong><font color="#0000FF">Welcome, 
          <?=$_SESSION["user_FirstName"]?>
          </font></strong> | You are logged in! | <A HREF=accountedit.php>Edit Account/Password</A> | <a href="javascript:do_logout();">Sign 
          Out</a> 
          <? } ?>

                </form>
              </div>
            </div>
          </div>
          <div class="row-2">
          	<!-- .nav-box -->
          	<div class="nav-box">
            	<div class="left">
              	<div class="right">
                	<!-- .nav -->
                	<ul class="nav">
                  	<li><a href="index.php"><span><span>Home</span></span></a></li>
       <?
       	if (is_user_login()){
		$MemberID = $_SESSION['UserID'];
//		$q2="select * from USERS_TEAM where UserID=$MemberID";
//		$r2=mysql_query($q2) or die(mysql_error());
//			while ($a2=mysql_fetch_array($r2))
//			{
//			$coleague = $a2[TeamID];	
//			}	
	
		
		$q7="select * from LEAGUES_JOINED where UserID=$MemberID and LeagueID>99";
		$r7=mysql_query($q7) or die(mysql_error());
			while ($a7=mysql_fetch_array($r7))
			{
						
						$q17="select * from USER_LEAGUES where id=$a7[LeagueID]";
						$r17=mysql_query($q17) or die(mysql_error());
							while ($a17=mysql_fetch_array($r17))
							{
							
										$q177="select * from GAMES where GAMENAME ='$a17[gametype]'";
										$r177=mysql_query($q177) or die(mysql_error());
										while ($a177=mysql_fetch_array($r177))
										{
											$lglink = $a177[PAGE] . "?league_id=" . $a7[LeagueID];
											
			echo "<li><a href='$lglink' class='current'><span><span>" . $a17[leaguename] . "</span></span></a></li>";
			//echo "<li><h3><a href='$lglink'>" . $a17[leaguename] . "</a></h3></li>";							
										}
							}
			//echo "<A HREF='weeklypicks2.php'>Make Picks for Week:" . $PickWeek . "</A><BR>";		
			//echo "<P><A HREF='weekpicks.php'>View All Picks For the current week!</A><BR>";
			
			}}
			
			else
			{
			echo "<li>Login to see your games!</li>";	
			}
            
            ?>
                  </ul>
                	<!-- /.nav -->
                </div>

<?
function is_user_login() {
	if (isset($_COOKIE['user_login']) && $_COOKIE['user_login']=="yes")
	{
		$_SESSION["user_login"] = "yes";
		$_SESSION["user_FirstName"] = $_COOKIE["user_FirstName"];
		$_SESSION["user_fullname"] = $_COOKIE["user_fullname"];
		$_SESSION["UserID"] = $_COOKIE["UserID"];
		$_SESSION["uid"] = $_COOKIE["uid"];
		$_SESSION["pwd"] = $_COOKIE['pwd'];
		return TRUE;
	}
	else
		return FALSE;
}

function do_logout() {
	session_unregister("user_login");
	session_unregister("user_FirstName");
	session_unregister("user_fullname");
	session_unregister("UserID");
	session_unregister("uid");
	session_unregister("pwd");
	
	// reset the priviousely set cookie value if any
	setcookie("user_login", "yes", time()-60*60*24*100, "/");
	setcookie("user_FirstName", $data["FirstName"], time()-60*60*24*100, "/");
	setcookie("user_fullname", $data["FirstName"] . " " . $data["LastName"], time()-60*60*24*100, "/");
	setcookie("UserID", $data["ID"], time()-60*60*24*100, "/");
	setcookie("uid", $data["userid"], time()-60*60*24*100, "/");
	setcookie("pwd", $psw, time()-60*60*24*100, "/");
	header('Location: '.$_SERVER['PHP_SELF']);	
}

function process_login() {

    // RAISE THE ERROR REPORTING LEVEL
    error_reporting(E_ALL);
    
    global $str_login_error;
    $uname = trim($_POST["uname"]);
    $psw = trim($_POST["psw"]);
    
    // RUN THE QUERY, REPORT THE OUTCOME AND SHOW THE DATA
    $sql = "SELECT * FROM USERS WHERE userid = '$uname' AND password = PASSWORD('$psw')";
    $rs  = mysql_query($sql);
    $num = mysql_num_rows($rs);
    echo '<pre>';
    var_dump($sql);
    var_dump($rs);
    var_dump($num);
    while ($data = mysql_fetch_object($rs)) { var_dump($data); }
    
    // TERMINATE THE SCRIPT
    die();
}

Open in new window

Thanks and regards, ~Ray
0
 

Author Comment

by:dsg138
ID: 39177650
Thank you Ray.
Yes, I know that longterm I need to redo this script.
My immediate concern is figuring out how to get my users on this weekend.

Your latest code is here:
http://www.officepickem.com/newtest.php

I really appreciate your help.

Thanks!
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39177782
I've tried it using test1/test1 and found that the query worked, but returned zero rows of results.  Can you please check the data base carefully, using phpMyAdmin, and show me the row that has the fully-resolved password for test1
0
 

Author Comment

by:dsg138
ID: 39177790
Sure, here you go...
ID       userid       password       LastName       FirstName
1441       test1       *06C0BF5B64ECE2F       Test1       Test1
0
 
LVL 82

Assisted Solution

by:hielo
hielo earned 50 total points
ID: 39177832
Just so we are on the same page, try resetting the password first:
...
$psw = trim($_POST["psw"]);

//add this just for troubleshooting now.  Remove it later
mysql_query("UPDATE `USERS` SET `password`=PASSWORD('$psw') WHERE `ID`=1441 LIMIT 1") ;

    // RUN THE QUERY, REPORT THE OUTCOME AND SHOW THE DATA
    $sql = "SELECT * FROM `USERS` WHERE (`userid` = '$uname') AND (`password`=PASSWORD('$psw'))";
    $rs  = mysql_query($sql);
...

Open in new window

0
 

Author Comment

by:dsg138
ID: 39177868
Thanks.  I added that line of code.  The password for user 1441 stayed the same in phpMyAdmin after running the code.  *06C0BF5B64ECE2F
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 450 total points
ID: 39177884
OK, we may be closer to finding the culprit.  Please have a look at this:
1...5...10...15.
*06C0BF5B64ECE2F

Open in new window

The length of the PASSWORD()  in the data base table is appropriate for MySQL at 4-level, not 5-level.  Please read this man page for an explanation of what you're dealing with.
http://dev.mysql.com/doc/refman/4.1/en/password-hashing.html
0
 
LVL 82

Expert Comment

by:hielo
ID: 39177895
Just out of curiosity, let's try MD5() instead of PASSWORD():

...
$psw = trim($_POST["psw"]);

//add this just for troubleshooting now.  Remove it later
mysql_query("UPDATE `USERS` SET `password`=MD5('$psw') WHERE `ID`=1441 LIMIT 1") ;

    // RUN THE QUERY, REPORT THE OUTCOME AND SHOW THE DATA
    $sql = "SELECT * FROM `USERS` WHERE (`userid` = '$uname') AND (`password`=MD5('$psw'))";
    $rs  = mysql_query($sql);
...

Open in new window

0
 

Author Comment

by:dsg138
ID: 39177904
MD5 changes the test1 password to:       5a105e8b9d40e132.
But it's still not synching up to log in.

In looking at the users table, the field password is a varchar(16).
So if PASSWORD is now looking for a 41 character hash, it's not even possible.

The thing I'm confused about is that the new password change wasn't a 5.5 change, but a 4.1 change.  I've been using 5.1 for a long time with no issues.

But, I am wondering if when my webhost did the upgrade to 5.5, could the value of:
old_password have been set to 0 (default) where it may have previously been 1, causing this to no longer work?  

Ray, these options were in the first article you shared:
http://dev.mysql.com/doc/refman/5.5/en/password-hashing.html
0
 

Author Comment

by:dsg138
ID: 39177916
Actually, I think this is starting to make sense.

I think I need to change PASSWORD() to OLD_PASSWORD(), as it mentions in your article Ray, like this:
 $sql = "SELECT * FROM `USERS` WHERE (`userid` = '$uname') AND (`password`=OLD_PASSWORD('$psw'))";

When I tested it with Test1 it didn't work.
But, I think that's because I created Test1 after the setting was changed.
So as far as my website is concerned: *06C0BF5B64ECE2F is incorrect.
It should be: 3caf84cb5217bc5e.

If I update the Test1 account to use: OLD_PASSWORD:
mysql_query("UPDATE `USERS` SET `password`=OLD_PASSWORD('$psw') WHERE `ID`=1441 LIMIT 1") ;
The array finally outputs.

So I guess I have 2 options.
1.  Get my webhost to add the setting OLD_PASSWORDS=1
2.  Change reference of PASSWORD() to OLD_PASSWORD() on all logon pages.
This options should allow my current users to log in again, but I think it creates a mess for new users trying to create an account, correct?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39177974
Please post the CREATE TABLE statement for `USERS` so we can see the definition of the `password` column length, thanks.

Change reference of PASSWORD() to OLD_PASSWORD() on all logon pages...
There should be only one "logon" page.  It would be the page that sets the session and cookie information for all of the other pages.
0
 

Author Comment

by:dsg138
ID: 39178041
Ray, the password field is a varchar(16).

CREATE TABLE 'users' (
`ID` int( 11 ) NOT NULL AUTO_INCREMENT ,
`userid` varchar( 100 ) NOT NULL DEFAULT '',
`password` varchar( 16 ) NOT NULL DEFAULT '',
`LastName` varchar( 50 ) NOT NULL DEFAULT '',
`FirstName` varchar( 50 ) NOT NULL DEFAULT '',
`City` varchar( 50 ) NOT NULL DEFAULT '',
`State` char( 2 ) NOT NULL DEFAULT '',

Open in new window


I think I identified all the pages that I needed to change the reference to OLD_PASSWORD:
-  The 1 login page which is in the header
-  Forgot password page
-  Signup page
0
 

Author Closing Comment

by:dsg138
ID: 39178050
Thanks guys.  I greatly appreciate the help.

Ray, many thanks for all the troubleshooting and especially for creating a test script.
Your initial hunch was about the password hash and it was right.
I do realize that long term, I need to upgrade my login solution to something better.

Hielo, thanks for the troubleshooting ideas.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39178469
Thanks for the points; glad we got it working again!  Best to all, ~Ray
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now