too long

Question

SCROLL DOWN TO HELP ME, THE PROBLEM IS EXPLAINED.

PROBLEM ALMOST SOLVE !

I am trying to perform an automatic login script with cookies and I came across a very interesting tutorial: http://blog.monstruosor.com/2013/05/18/php-auto-connexion-par-cookie/

Here is the code he proposes :

function autologin() {
$key = $_COOKIE['autologin'];

$ip = $_SERVER['REMOTE_ADDR'];

$query = "SELECT * FROM users WHERE SHA1(CONCAT('SEL1-df546', `name`, `id`, 'SEL2-sd55fd', `last_connection`, $ip))=$key";
$infos = $users->exec($query);

if(!is_array($infos) || empty($infos)) {
    // Mauvais cookie !
    return false;
}

$_SESSION['user'] = $infos; // Mise en SESSION
return true;

The main line that interests me is this one:

$query = "SELECT * FROM users WHERE SHA1(CONCAT('SEL1-df546', `name`, `id`, 'SEL2-sd55fd', `last_connection`, $ip))=$key";
$infos = $users->exec($query);

However, it is not secure. I want to know how to use an application prepared on this line. I tried this but it does not work:

    $req = $this->_db->prepare("SELECT * FROM Members WHERE SHA1(CONCAT(HASH1-1dg9sf', `id`, `first_name`, 'HASH2-dt5w1q', `last_name`, ip = :ip)) = :key");
    $req->execute(array(
        'ip' => htmlspecialchars($ip),
        'key' => htmlspecialchars($key)));

The $this->_db is from my class and is well written don't worry. The problem comes from here :

$req = $this->_db->prepare("SELECT * FROM Members WHERE SHA1(CONCAT(HASH1-1dg9sf', `id`, `first_name`, 'HASH2-dt5w1q', `last_name`, ip = :ip)) = :key");

Hope someone has a solution.

---

Here's more information :

my index.php page contains :

$ip = $_SERVER['REMOTE_ADDR'];
$key = $_COOKIE['auto_login'];
$mg->autoLogin($ip, $key);

and my autoLogin method from my class :

    public function autoLogin($ip, $key)
{
    $req = $this->_db->prepare("SELECT * FROM Members WHERE SHA1(CONCAT(HASH1-1dg9sf', `id`, `first_name`, 'HASH2-dt5w1q', `last_name`, :ip)) = :key");
    $req->execute(array(
        'ip' => htmlspecialchars($ip),
        'key' => htmlspecialchars($key)));
    $donnee = $req->fetch();    
    $req->closeCursor();
    if(!is_array($donnee) || empty($donnee)) 
    {
       return false;
    }
    else
    {
        $_SESSION['user'] = $donnee;
        return true;
    }
}

If you still need more information, just ask me, I'll update my question again !

---

HERE'S THE LAST PROBLEM

Thanks to pala_ who almost resolve it

I know where the problem is. In my database, the IP stored of the member I'm trying to log in is " ::1 ". But when I do " echo $ip; " which is generated here : "$ip = $_SERVER['REMOTE_ADDR']; ", I have " 127.0.0.1 " ! It's not the same IP when I do my verification so the problem is probably here ! Any idea how to fix this ?

The problem is here because if I do " $ip = '::1'; ", it's WORKING. Any idea??

Answer 1

Try this:

$req = $this->_db->prepare("SELECT * FROM Members WHERE SHA1(CONCAT('HASH1-1dg9sf', `id`, `first_name`, 'HASH2-dt5w1q', `last_name`, :ip)) = :key");

The error is here: ip = :ip just needs to be :ip and also here CONCAT(HASH1-1dg9sf' needs to be CONCAT('HASH1-1dg9sf'

Here is some complete, tested (and working) code. Uses PDO.

 $stmt = $pdo->prepare("SELECT * FROM users WHERE SHA1(CONCAT('HASH1-1dg9sf', `id`, `first_name`, 'HASH2-dt5w1q', `last_name`, :ip)) = :key");
        $ip = '10.0.0.1';
        $key = 'HASH1-1dg9sf2himHASH2-dt5w1qalso10.0.0.1';
        $key = sha1($key);
        $res = $stmt->execute(array('ip' => $ip, 'key' => $key));
        print_r($stmt->fetchAll(PDO::FETCH_ASSOC));

And has the following output: Array

(
    [0] => Array
        (
            [id] => 2
            [first_name] => him
            [last_name] => also
            [ip] => 10.0.0.1
        )

)

which is retrieved from the following row from my test database:

mysql> SELECT * FROM users WHERE SHA1(CONCAT('HASH1-1dg9sf', `id`, `first_name`, 'HASH2-dt5w1q', `last_name`,'10.0.0.1'));
+------+------------+-----------+-----------+
| id   | first_name | last_name | ip        |
+------+------------+-----------+-----------+
|    1 | me         | last      | 127.0.0.1 |
+------+------------+-----------+-----------+