MrScabby Posted July 21, 2015 Share Posted July 21, 2015 (edited) 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 July 21, 2015 by MrScabby Quote Link to comment https://forums.phpfreaks.com/topic/297414-problem-with-sql-statement-newbe-to-sql/ Share on other sites More sharing options...
requinix Posted July 21, 2015 Share Posted July 21, 2015 (edited) Sounds like $pwd is wrong. What's the rest of the code? Be sure to use tags when you post it. Edited July 21, 2015 by requinix Quote Link to comment https://forums.phpfreaks.com/topic/297414-problem-with-sql-statement-newbe-to-sql/#findComment-1517034 Share on other sites More sharing options...
MrScabby Posted July 21, 2015 Author Share Posted July 21, 2015 (edited) 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 July 21, 2015 by MrScabby Quote Link to comment https://forums.phpfreaks.com/topic/297414-problem-with-sql-statement-newbe-to-sql/#findComment-1517035 Share on other sites More sharing options...
requinix Posted July 21, 2015 Share Posted July 21, 2015 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? Quote Link to comment https://forums.phpfreaks.com/topic/297414-problem-with-sql-statement-newbe-to-sql/#findComment-1517038 Share on other sites More sharing options...
MrScabby Posted July 21, 2015 Author Share Posted July 21, 2015 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! Quote Link to comment https://forums.phpfreaks.com/topic/297414-problem-with-sql-statement-newbe-to-sql/#findComment-1517039 Share on other sites More sharing options...
Ch0cu3r Posted July 22, 2015 Share Posted July 22, 2015 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). Quote Link to comment https://forums.phpfreaks.com/topic/297414-problem-with-sql-statement-newbe-to-sql/#findComment-1517053 Share on other sites More sharing options...
MrScabby Posted July 22, 2015 Author Share Posted July 22, 2015 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 Quote Link to comment https://forums.phpfreaks.com/topic/297414-problem-with-sql-statement-newbe-to-sql/#findComment-1517057 Share on other sites More sharing options...
Strider64 Posted July 22, 2015 Share Posted July 22, 2015 Still using obsolete code, trying search for mysqli or PDO login tutorials....I'm sure you find plenty. Quote Link to comment https://forums.phpfreaks.com/topic/297414-problem-with-sql-statement-newbe-to-sql/#findComment-1517064 Share on other sites More sharing options...
mac_gyver Posted July 22, 2015 Share Posted July 22, 2015 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. 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. Quote Link to comment https://forums.phpfreaks.com/topic/297414-problem-with-sql-statement-newbe-to-sql/#findComment-1517067 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.