Jump to content

Problem with SQL statement - newbe to SQL


MrScabby

Recommended Posts

Hi all

 

Im having a problem with this MySQL statement, Im trying to set up a login system following instructions from another site

All is working well except it fails when I try to log in,

 

Why would this NOT work

$sql = "SELECT * FROM user WHERE userid = '$uid' AND password = PASSWORD('$pwd')";

 

when this does

$sql = "SELECT * FROM user WHERE userid = '$uid' ";

 

It would seem this part is not functioning correctly

 AND password = PASSWORD('$pwd')

 

Just for ref this is the insert statement used to create a new user, working ok!

$sql = "INSERT INTO user SET
              userid = '$_POST[newid]',
              password = PASSWORD('$newpass'),
              fullname = '$_POST[newname]',
              email = '$_POST[newemail]',
              notes = '$_POST[newnotes]'";

 

 

 

 

Specs:-

  • Server: Localhost via UNIX socket
  • Server type: MySQL
  • Server version: 5.5.42 - MySQL Community Server (GPL)
  • Apache/2.4.10 (Unix)
  • Database client version: libmysql - mysqlnd 5.0.8-dev
  • PHP extension: mysqli   Version information: 4.0.7, latest stable version: 4.4.12
  • PHP version 5.3
Edited by MrScabby
Link to comment
Share on other sites

The site Im using is here but I will say the code is old and basic and has no protection/sanitation, but it is it's simplicity which makes it clearer to follow if you have not done it before which is where I am at the moment....

 

Managing Users with PHP Sessions and MySQL

 

Ive downloaded the sample code and altered the parts to work on my server. I have everything working except or some reason I cannot login. I've checked everything is working including seeing the tables in the database to see that they are being created correctly.

The only area I cannot check is the password because the encryption does not allow me to check it, if you see my point.

I have narrowed the issue down to this one statement contained in the accesscontrol.php which I have repeated below,

 

$sql = "SELECT * FROM user WHERE userid = '$uid' AND password = PASSWORD('$pwd')";
 <?php // accesscontrol.php
include_once 'common.php';
include_once 'db.php';

session_start();

$uid = isset($_POST['uid']) ? $_POST['uid'] : $_SESSION['uid'];
$pwd = isset($_POST['pwd']) ? $_POST['pwd'] : $_SESSION['pwd'];
echo "Data Variables" . "  uid =" . $uid . "  pwd =" . $pwd;
if(!isset($uid)) {
  ?>
  <!DOCTYPE html PUBLIC "-//W3C/DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  <html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <title> Please Log In for Access </title>
    <meta http-equiv="Content-Type"
      content="text/html; charset=iso-8859-1" />
  </head>
  <body>
  <h1> Login Required </h1>
  <p>You must log in to access this area of the site. If you are
     not a registered user, <a href="signup.php">click here</a>
     to sign up for instant access!</p>
  <p><form method="post" action="<?=$_SERVER['PHP_SELF']?>">
    User ID: <input type="text" name="uid" size="8" /><br /><br />
    Password: <input type="password" name="pwd" SIZE="8" /><br /><br />
    <input type="submit" value="Log in" />
  </form></p>
  </body>
  </html>
  <?php
  exit;
}

$_SESSION['uid'] = $uid;
$_SESSION['pwd'] = $pwd;
echo "Session Variables: uid=" . $_SESSION['uid'] . " pwd=" . $_SESSION['pwd'];
dbConnect("cl42-daystaff");
$sql = "SELECT * FROM user WHERE userid = '$uid' AND password = PASSWORD('$pwd')";
$result = mysql_query($sql);
if (!$result) {
  error('A database error occurred while checking your '.
        'login details.\\nIf this error persists, please '.
        'contact xxxxxx@xxx.com.');
}
echo "Num of rows =" . mysql_num_rows($result);
if (mysql_num_rows($result) == 0) {
  unset($_SESSION['uid']);
  unset($_SESSION['pwd']);
  ?>
  <!DOCTYPE html PUBLIC "-//W3C/DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  <html xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <title> Access Denied </title>
    <meta http-equiv="Content-Type"
      content="text/html; charset=iso-8859-1" />
  </head>
  <body>
  <h1> Access Denied </h1>
  <p>Your user ID or password is incorrect, or you are not a
     registered user on this site. To try logging in again, click
     <a href="<?=$_SERVER['PHP_SELF']?>">here</a>. To register for instant
     access, click <a href="signup.php">here</a>.</p>
  </body>
  </html>
  <?php
  exit;
}

$username = mysql_result($result,0,'fullname');
?>

Edited by MrScabby
Link to comment
Share on other sites

That tutorial sucks. You need to find another one. Bad/outdated information, poor practices, SQL injection, XSS, deprecated extensions... a whole host of problems with it that are showing up in your code too.

 

Did you happen to create your table using something like CHAR(16) for the password column?

Link to comment
Share on other sites

lol, I did warn you it was old but it does happen to have what a lot don't - its easy to understand and gets the structure across

 

Yes by the way, CHAR(16) and I created it myself as one of the tests to see if it needed a pre-defined database.

Your going to say its wrong now aren't you!

 

oh well at least Im trying,

Dont suppose you can suggest a more up to date site can you, one without a learning curve like Beaches Brook!

Link to comment
Share on other sites

 

 

Yes by the way, CHAR(16) and I created it myself as one of the tests to see if it needed a pre-defined database.

Your going to say its wrong now aren't you!

At the time that tutorial was written, it use using a version of MYSQL before 4.1 was released, the MySQL PASSWORD function would of returned a 16 character hash. However you are using MySQL version 5.x the password hash function now returns a 41 character hash.

 

Because you have set the password field to be 16 characters when the password is inserted it will be truncated, from 41 characters to 16 characters. The SQL query that checks the password will be trying to find a 41 character hash. This is why the sql query for checking the password is failing.

 

So you need to change the password field to hold 41 characters not 16. You will need to reset users existing passwords also

ALTER TABLE  user CHANGE `password` CHAR( 41 ) NOT NUL;

But using MySQL password function for hashing passwords is not recommend. You should use PHP's password_hash function (if your are not using PHP5.5 or newer then use the compatibility library).

Link to comment
Share on other sites

One last request if I may, and thankyou for the above advice.

 

I have now found this site to work from but on this occasion I want to make sure that Im singing off the same song sheet as everyone else.

Is this up-to-date and a good starting point for a (usable) website login system for my site?

 

just want to be sure I do not repeat the same mistake,,,

http://www.ineedtutorials.com/code/php/complete-advanced-login-member-system-php-tutorial

Link to comment
Share on other sites

the tutorial you linked to in the above post is also out of date and won't even run on php5.4+. it suffers from the following problems -
 
1) uses session_is_registered/session_unregister. these functions were deprecated 12 years ago and aren't even present in php5.4+. php will throw a fatal runtime error and stop at these function calls.
 
2) uses the obsolete/deprecated mysql_ database functions. these will be removed from php in the not to distant future, requiring that the code be rewritten using either the mysqli_ or PDO database functions. if you are learning php or writing new code, you should not use the mysql_ database functions.
 
3) uses sha/sha1 hash with a fixed/same salt for passwords. sha/sha1 hash is not appropriate for password hashing and using a fixed/same salt value for all passwords makes it easier to find all the passwords matching any particular hash value. see the php password_hash()/password_verify() functions that Ch0cu3r mentioned in his reply.
 
4) uses poor programming practices, one of which is passing values into functions using the global keyword.
 
5) specifically turns php error reporting off. error_reporting should always be set to E_ALL and for development/learning/debugging display_errors should be ON and on a live server, display_errors should be OFF and log_errors should be ON.

 

6) none of the forms repopulate already entered values.

 

7) none of the database query are tested for errors, so results testing if a row was matched or not can return an incorrect indication.

 

8) uses ereg() that is also obsolete and deprecated.

 

9) uses htmlentities() on input data. htmlentities() is an output function and does nothing useful for inputs.

 

given the spammy and nonsense comments posted on that site that the author hasn't even bothered to clean up, that's just another search result/click bait site that could careless about the quality of the content on it.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.