Query SQL for username and return password [duplicate]

This question already has answers here: Best way for a 'forgot password' implementation? [closed] (10 answers) Closed 2 years ago.

I have a database of usernames and passwords. I need to create a "Forgot password" function and have it search the table for a username and return that user's password. Then I would like it to send an email saying the name and password.

Here is my working code for querying the database for a specific user:

<?php
session_start();

include "config.php";

if($_POST['nameQuery']) {

$query = "SELECT * FROM myDatabase WHERE name = '" .$_POST['nameQuery']. "'";  
$result = mysql_query($query);  
if (mysql_num_rows($result) > 0) { 
    //User exists
    echo '1'; 
} else { 
    mysql_query($query);
//User does not exist
echo '0';开发者_如何学Go 
}
}
?>

---

DO NOT store passwords in your database. Cleartext passwords should never be stored. You should be storing a hash of the passwords to help prevent them being used on other sites. See Best way to store password in database for more information.

---

Your code is NOT secured ! Your $_POST['nameQuery'] is a gorgeous opened door to SQL Injection

The minimum security is to escape and sanitize all your inputs

$nameQuery = mysql_real_escape_string ($_POST['nameQuery']);

The golden rule: never trust incoming data.

---

Community Wiki:

Don't. Because that means you'll be saving retrievable passwords. Better to send a password-changing link to their email that gives access to a one-time password reset page. In this way, the password isn't changed until a reset cycle is completed by someone with access to that user's email.

In that way you can appropriately hash passwords and check incoming passwords against a hash only.

In addition, I recommend looking into php's PDO, because you're currently creating sql queries that are succeptible to sql-injection.

---

I have a few suggestions for you

1. Don't send people there password but rather provide them with a link to change there password
2. Look into kjetilh's suggestion

good luck and happy coding

---

First thing's first: you might want to make sure that you won't get SQL-injected via your login, as you're literally injecting the user input into your query... big no-no.

Swap this:

$query = "SELECT * FROM myDatabase WHERE name = '" .$_POST['nameQuery']. "'";  

...for this:

$query = sprintf(
    'SELECT * FROM myDatabase WHERE name = \'%s\'', 
    mysql_real_escape_string($_POST['nameQuery'])
);

Next up is what you asked for: a way to get both the users username and password. While I don't recommend that you actually store the password in plaintext for everyone to view, it's a decision you have to make on your own.

This snippet will do the deed:

<?php
    //Get the data from the DB
    $query = sprintf(
        'SELECT * FROM myDatabase WHERE name = \'%s\'', 
        mysql_real_escape_string($_POST['nameQuery'])
    );
    $result = mysql_query($query);
    $user_info = mysql_fetch_assoc($result);

    //Check if it's valid
    if( isset($user_info['name']) ) {

        //Construct the message
        $message = 'Your username is: ' . $user_info['name'] . "\n"
        $message .= 'Your password is: ' . $user_info['password'] . "\n";

        //Send it to the appropriate email
        $status = mail(
            $user_info['email'], 
            'Password recovery for ' . $user_info['name'], 
            $message
        );

        //Check if it actually worked
        if( $status ) echo 'Mail sent. Check your inbox. Login again. Thank you.';
        else echo 'The password recovery couldn\'nt be sent. Please try again later.';

    } else { 

        echo 'No user found with the supplied username.', 
            'Please try again (with another username)';

    }
?>

Edit: Adding password recovery-functionality

For the password recovery-functionality you requested below, you can try something like this:

recover_password.php:

<?php
    session_start();


    //mysql_connect()-here

    //Initalize the variable
    $do_update_password = false;

    //Grab the  token
    $token = isset($_REQUEST['token'])? $_REQUEST['token'] : '';
    $is_post_request = isset($_POST['update_pwd'])? true : false;
    $is_recovery_request = isset($_POST['request_recovery'])? true : false;
    $message = '';

    //Check if we're supposed to act upon a token
    if( $is_recovery_request ) {

        //Grab the email
        $email = isset($_POST['email'])? $_POST['email'] : '';

        //Create the query, execute it and fetch the results
        $sql = sprintf(
            'SELECT `user_id` FROM myDatabase WHERE `email` = \'%s\'',
            mysql_real_escape_string($email)
        );
        $result = mysql_query($sql);
        $user_info = mysql_fetch_assoc($result);

        //Validate the response
        if( isset($user_info['user_id') ) {

            //Let's generate a token
            $date = date('Y-m-d H:i:s');
            $token = md5($email . $date);

            //Create the "request"
            $sql = sprintf(
                'INSERT INTO myRequests (`user_id`, `token`, `date`) VALUES (\'%s\', \'%s\', \'%s\')',
                $user_info['user_id'],
                mysql_real_escape_string($token),
                $date
            );
            $result = mysql_query($sql);

            //Validate
            if( mysql_affected_rows($result) == 1 ) {


                //Construct the message
                $message = 'Your username is: ' . $user_info['email'] . "\n"
                $message .= 'Please click on the following link to update your password: http://yoursite.com/request_password.php?token=' . $token . "\n";

                //Send it to the appropriate email
                $status = mail(
                    $email, 
                    'Password recovery for ' . $email, 
                    $message
                );

                //Check if it actually worked
                if( $status ) {

                    echo 'Mail sent. Check your inbox. Login again. Thank you.';

                } else {

                    echo 'The password recovery couldn\'nt be sent. Please try again later.';

                }

            } else {

                $message = 'The DB-query failed. Sorry!';

            }

        } else {

            $message = 'The specified e-mail address could not be found in the system.';

        }

    } elseif( $token != '' ) {

        //Check so that the token is valid length-wise (32 characters ala md5)
        if( !isset($token[31]) || !isset($token[32])  ) { 

            $message = 'Invalid token!';

        } else {

            //Construct the query and execute it
            $sql = sprintf(
                'SELECT `user_id` FROM myRequest WHERE `token` = \'%s\'', 
                mysql_real_escape_string($token);
            );
            $result = mysql_query($sql);

            //Fetch the rows
            $request_info = mysql_fetch_assoc($result);

            //Check for a valid result
            if( isset($request_info['user_id']) ) {

                $message = 'Update your password below.';
                $do_update_password = true;

            } else {

                $message = 'No record found for the following token: ' . $token);

            }
        }
    } elseif( $is_post_request ) {

        //Grab the new password
        $password = isset($_POST['password'])? $_POST['password'] : '';

        //Construct the query
        $sql = sprintf(
            'UPDATE myDatabase SET `password` = \'%s\' WHERE `user_id` = ( SELECT `user_id` FROM myRequest WHERE `token` = \'%s\' )', 
            mysql_real_escape_string($password),
            mysql_real_escape_string($token)
        );    

        //Execute it, and check the results
        $result = mysql_query($sql);
        if( $result !== false ) {

            //Did we succeed?
            if( mysql_affected_rows($result) === 1 ) {

                //Remove the old recovery-request
                $sql = sprintf(
                    'DELETE FROM myRequests WHERE `token` = \'%s\'',
                    mysql_real_escape_string($token)
                );
                $result = mysql_query($sql);

                //^We don't actually need to validate it, but you can if you want to
                $message = 'Password updated. Go have fun!';

            } else {

                $message = 'Could not update the password. Are you sure that the token is correct?';

            }

        } else {

            $message = 'Error in the SQL-query. Please try again.';

        }
    }
?>
<!DOCTYPE html>
<html>
    <head>
        <title>Password recovery</title>
        <style>
            form > * { display: block; }
        </style>
    </head>
    <body>
        <h1><?php echo $message; ?></h1>
        <?php if( $do_update_password ): ?>

            <form method="post">
                <label for="token">Token:</label>
                <input type="text" name="token" id="token" value="<?php echo $token; ?>" />
                <label for="password1">Password:</label>
                <input type="text" name="password[]" id="password1" />
                <label for="password2">Password (again):</label>
                <input type="text" name="password[]" id="password2" /> 
                <input type="submit" name="update_pwd" value="Update your password!" />
            </form>

        <?php elseif($is_post_request && $token != ''): ?>

            <h2>Request that might've updated your password. Exciting!</h2>

        <?php else: ?>

            <form method="post">
                <label for="email">E-mail address:</label>
                <input type="text" name="email" id="email" />
                <input type="submit" name="request_recovery" value="Request a new password" />
            </form>

        <?php endif; ?>
    </body>
</html>

Note that I haven't had time to actually test the code, but I think it'll work just fine with some minor adjustments. Oh, before I forget, you'll need to add the following table to the DB:

Table structure for table myRequests

CREATE TABLE IF NOT EXISTS `myRequests` (
  `request_id` int(6) NOT NULL AUTO_INCREMENT,
  `token` varchar(32) NOT NULL,
  `user_id` int(6) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`request_id`),
  UNIQUE KEY `token` (`token`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Good luck!

---

While tangential to your original question, I would like to point out that storing passwords in plain text is a bad idea. You should store hashed versions of the password in the database. You can then hash user input and compare it to what is in the database for logging in.

Instead, your forgot password should create a new(temporary) password, and store the hash of that in the database, while sending the plain text password to the email account on file.

---

Just read the result:

/* ... */
if (mysql_num_rows($result) > 0) {
  // User exists
  $row = mysql_fetch_row($result);
  print_r($row);
}
/* ... */

On a more general note: You have a SQL injection vulnerability in your code, please look into that topic, or attackers will be able to read all your user's passwords.

Also, it is not advised to store the password in clear text in you database. Please use a hashing algorithm like sha1 oder sha256 to store passwords.

---

I will recommend you to change your table design to

• UserName
• Password ' store hash
• Password Retrieval Question ' store hash
• Password Retrieval Answer ' store hash

When login check the user against the hashed password, something like this

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

When loggin in then use sql like
select col1,col2,.. from tbl where user=? and password=? and then fill the parameter with $_POST['username'], $_POST['password']

so use Prepared Statement or PDO

use the same logic when user forgot his password

---

<?php
session_start();

include "config.php";

if($_POST['nameQuery']) {

    $query = "SELECT * FROM myDatabase WHERE name = '" .mysql_real_escape_string($_POST['nameQuery']). "'";  
    $result = mysql_query($query) or die ('Error: '.mysql_error());  
    if (mysql_num_rows($result) > 0) { 
        $row = mysql_fetch_assoc($result);
        $message = 'Your password is: '.$row['password'];
        if(mail($row['user_email'], 'Lost password', $message)){
            echo 'Password sent';
        }
    } else { 
        echo 'Nu such user'; 
    }
}
?>

---

You have to retrieve the username and password from the mysql_query result (stored in the $result variable) as such:

$row = mysql_fetch_array($result);
$username = $row['username'];
$password = $row['password'];

Then use php's mail() function to send the e-mail.