Results 1 to 8 of 8

Thread: [RESOLVED] MySQL - find password by username?

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2008
    Posts
    1,754

    Resolved [RESOLVED] MySQL - find password by username?

    Hello i am new to MySQL, i have created a registration form for my website now i am making the login.

    By making the registration form i have learned how to use mySQL's insert function. I need the login form to on submit take the username the user entered and from that retrieve the password from the database. It should then match up the server retrieved password with the one the user supplied to see if the user entered the correct username and password. I am guessing i will need to use mySQL's Where. How do i do this?

    Here is the code for the form:
    Code:
    <h3 align="center">Login</h3>
    <br />
    <br />
    <div align="center">
    <form action="login.php" method="post">
    <b>Username:</b>
    <br /><input type="text" name="username">
    <br />
    <br />
    <b>Enter your Password:</b>
    <br /><input type="password" name="password">
    <br />
    <br />
    <input type="submit" value="Login">
    </form>
    </div>

  2. #2
    PowerPoster
    Join Date
    Sep 2003
    Location
    Edmonton, AB, Canada
    Posts
    2,629

    Re: MySQL - find password by username?

    you need to use a select statement with a where clause, yes. you can use this query:

    SELECT username FROM users WHERE username='<username>' AND password='<hashed-password>' LIMIT 1

    where <username> is the person logging on and <hashed-password> is a hashed password. also, if you didn't do this when you made your registration form, you might want to store only hashed (using md5 or another hash) passwords so that if your database is ever compromised you won't have people logging into their accounts.

    you could apply it like this:
    PHP Code:
    <?php
      
    if($_SERVER['REQUEST_METHOD'] == "POST"){
        
    $username mysql_real_escape_string($_POST['username']);
        
    $password md5($_POST['password']);
        
    $sql "SELECT username as check_login FROM users WHERE username='$username' AND password='$password' LIMIT 1";
        
    extract(mysql_fetch_assoc(mysql_query($sql)));
        if(isset(
    $check_login)){
          echo 
    'successfully logged in!';
          
    //set cookies/sessions or whatever
        
    }else{
          echo 
    'invalid username or password!';
        }
      }else{
        
    //show the form
    ?>
    <form action="login.php" method="post">

    <!-- ..... -->

    </form>
    <?php ?>

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2008
    Posts
    1,754

    Re: MySQL - find password by username?

    When you say:

    //show the form
    You mean redirect the user back to the form?
    Last edited by noahssite; Apr 10th, 2009 at 03:04 PM.

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2008
    Posts
    1,754

    Re: MySQL - find password by username?

    There is an error with this line:

    Code:
    extract(mysql_fetch_assoc(mysql_query($sql)));
    Warning: extract() [function.extract]: First argument should be an array in /home/a1401476/public_html/login.php on line 14

  5. #5
    PowerPoster
    Join Date
    Sep 2003
    Location
    Edmonton, AB, Canada
    Posts
    2,629

    Re: MySQL - find password by username?

    when I commented "//show the form," I meant that you should then have all of your form HTML between my <form> and </form> tags.

    If you're getting an error with extract() then you're probably getting no result from the query (eg. wrong password/username combination), or you have an error with the SQL. if you're sure the SQL is fine, then you can do one of two things: either suppress the error (with @, shown below), or use a similar but different method (which requires a bit more to change on your part):
    PHP Code:
    //suppress error
    @extract(mysql_fetch_assoc(mysql_query($sql)));

    //alternate method
    $query mysql_query($sql);
    $check_login mysql_num_rows($query);
    if(
    $check_login 0){
      
    //successful login
    }else{
      
    //incorrect login


  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2008
    Posts
    1,754

    Re: MySQL - find password by username?

    I am using the alternate method you supplied. Did i incorporate the code wrong? I have updated my register form so it uploads the password with md5 encryption. I constantly get the 'Invalid username and/or password.' echo..

    What is wrong?

    Here is my complete code for the page:
    Code:
    <?php
    session_start();
    include ("Includes/database_info.php");
    $con = mysql_connect($sql_servername,$sql_username,$sql_password);
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }
    mysql_select_db("a1401476_records", $con);
    if($_SERVER['REQUEST_METHOD'] == "POST"){
        $username = mysql_real_escape_string($_POST['username']);
        $password = md5($_POST['password']);
        $sql = "SELECT Username as check_login FROM accounts WHERE Username='$username' AND Password='$password' LIMIT 1";
    $query = mysql_query($sql);
    $check_login = mysql_num_rows($query);
    if($check_login > 0){
    //success
    $_SESSION['username'] = $username;
    echo 'You have logged in!';
    }else{
    //incorrect login
    echo 'Invalid username and/or password!';
    } 
    }
    ?>

  7. #7
    PowerPoster
    Join Date
    Sep 2003
    Location
    Edmonton, AB, Canada
    Posts
    2,629

    Re: MySQL - find password by username?

    I would suggest putting your calls to mysql_connect() and mysql_select_database() into "database_info.php." this will clean up repetitive code like you seem to have there. also, if you're not using multiple connections, defining $con seems pointless. you can simply do:
    PHP Code:
    mysql_connect("host""username""password") or die(mysql_error());
    mysql_select_db("database"); 
    since you're never specifying your connection in other database calls (mysql_query, etc), there doesn't seem to be any point to doing it in the first place.

    now, the code you have works fine, and you did implement it correctly. since you've only just switched to using MD5 hashes, you need to make sure that your password field in the database is a varchar with a length of 32. the MD5 function creates a 32 character string; if your password field cannot hold a string of that length, MySQL will truncate it to whatever length you've set. this might mean that your passwords are being stored as 20-30 character strings, but PHP is creating a 32 character string to compare against. it will always be false.

    if that isn't the problem, make sure that your query is correct (echo $sql if you need to), and run it through the MySQL console or phpMyAdmin. you may be looking at the wrong database, wrong table, have different field names maybe?

    edit: oh, and you can get rid of the alias used in your SQL query (username as check_login). that was just something I put there for when I originally used extract(). it won't affect anything to leave it in there (especially since you're not even fetching that row), but when you look back on this code in the future you may not understand why it's there.
    Last edited by kows; Apr 11th, 2009 at 04:18 PM.

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2008
    Posts
    1,754

    Re: MySQL - find password by username?

    Thank you, and yes the password length was varchar(30) so after i changed it to 32 the login worked perfectly. RESOLVED!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width