Page 1 of 1

Login Ė seen from the SQL point-of-view Rate Topic: ***** 2 Votes

#1 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3550
  • View blog
  • Posts: 10,319
  • Joined: 08-June 10

Post icon  Posted 25 April 2011 - 05:34 AM

*
POPULAR

introduction

Many tutorials have been written about this subject looking at the problem from different angles. Nevertheless, I have hardly seen a tutorial which had optimised or even sensible SQL calls. Therefore I want to look less on implementing it in PHP and focus on what you can do on the DB-side to improve your code.

requirements
- PHP 5
- a Database (MySQL, PostgreSQL, DB2, Ö)

When checking, whether a user is allowed to acces the restricted area (a.k.a. Log-In), there is only one thing that you want to know from your DB: Do username and password match? The usual way of doing it is requesting a result set with username and password and checking the number of returned results.
SELECT * FROM users WHERE username = '$name' AND password = '$pass';

Why should you not do that?
  • you fetch data you donít need
  • itís susceptible to SQL Injections, or Ö
  • Ö if you sanitised the input, itís tied to a specific database
  • you need a database specific function/method to get the number of rows

For the first disadvantage, imagine you have a larger BLOB field (e.g. a userís avatar). Every time you check the login credentials, you fetch that image as well! On the other hand side I do need to mention that an avatar image does not belong in the login table. A sensible DB design would hardly contain more than the absolutely necessary information (login name, password [, user id [, status]]).

The second disadvantage will hit hard on you if you change your DB. It might seem unlikely, but when you sell your scripts or do contract work, you donít have the same database everywhere (companies rather have PostgreSQL or Oracle than MySQL).
Were your code re-useable (with regards to the DB), you donít need to alter your whole script, just the database specific parts like DB credentials and DB type.

Now, what are we going to do to make it better? Focus on what we need1. I previously said that we only need to know, whether there is a match or not. We donít need to know, what that match is! SQL has a lot of functions and one of these does what we need: COUNT(). As the name suggests, it returns the number of matches, that might be the length of an array, but also the number of results.
SELECT COUNT(*) FROM users WHERE username = '$name' AND password = '$pass';

Now that we have taken care of the data amount, we need to get that count value in a sensible way (and deal with the Injection problem). For both issues PHP provides us with an elegant solution: PDO. It is capable of Prepared Statements (wich will counter SQL Injection) and allows us to fetch a single value from the query. The SQL for a Prepared Statement looks as follows.
SELECT COUNT(*) FROM users WHERE username = :name AND password = :pass;

Then we can write a standard PDO procedure to get the result
function checkLogin($username, $password)
{
    // using a Postgres DSN for example "pgsql:host=localhost;dbname=mydb"
    // create PDO object
    $pdo = new PDO(DB_DSN, DB_USER, DB_PASS); // constants make coding easier!
    // add error handling
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // create the query
    $login = $pdo->prepare('SELECT COUNT(*) FROM users WHERE username = :name AND password = :pass;');
    // lazy execute (for string type fields)
    $login->execute(array('pass' => $password, 'name' => $username));
    // fetch data and convert it to boolean
    return (bool) $login->fetchColumn(0);
}

One possibility to use that function implements Exceptions:
try
{
    // throws a PDOException, if there is a DB problem
    if (!checkLogin($_POST['user'], $_POST['pass']))
    {
        throw new Exception('Login failed.');
    }
    // display the index file for logged-in users
    include 'login_index.html';
}
// deal with the DB problem
catch (PDOException $e)
{
    send_error_mail($e->getMessage());
    include 'error_500.html';
}
// deal with the 'failed login' problem
catch (Exception $e)
{
    include 'failed_login.html';
}

This PHP code is by far not optimised, as you can nicely wrap up the PDO code to make it more re-useable (there could be a Singleton like instance for the PDO class, so that you donít need to write the connection code each time) or you can display an appropriate error message, but thatís out of the scope of this tutorial.

Thatís it, isnít it?

No. There is more. I was previously talking about database security, but SQL Injections are not the only issues. It should be well known, that you never ever save plain passwords in a database. It is recommended to use some kind of hashing on the passwords, so should your database ever be cracked, the passwords ainít exposed.

To implements that you have two options:
  • If, and only if your database is located on localhost, then you can hash the password on the DB side.
  • If you want a hash algorithm not available on the DB side or you have a CDN, you need to hash on the PHP side.


For option one you only need to modify the SQL to incorporate the hashing function:
SELECT COUNT(*) FROM users WHERE username = :name AND password = MD5(:pass);


Option two requires you to modify the password before sending it to the DB:
    // using a salted hash with RIPEMD160, SHA256 is also a good choice
    $hash = hash_hmac('ripemd160', $_POST['pass'], DB_HASH_SALT);
    if (!checkLogin($_POST['user'], $hash))
    {
        throw new Exception('Login failed.');
    }



1 Ė always ask yourself while coding: What is it that I need here? Often you write too much code just because you lost track of the objective. And itís very likely that answering the above question gives you the solution strategy.

Is This A Good Question/Topic? 11
  • +

Replies To: Login Ė seen from the SQL point-of-view

#2 eTech1  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 135
  • Joined: 27-August 10

Posted 25 May 2011 - 12:49 PM

Would a limit 1 optimize this query further so once the match is found it will not continue to search the table?
Was This Post Helpful? 0
  • +
  • -

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3550
  • View blog
  • Posts: 10,319
  • Joined: 08-June 10

Posted 25 May 2011 - 01:06 PM

assuming that username is either a Primary Key or at least UNIQUE (duplicate usernames should be avoided at all costs) – no.

despite that, the COUNT() function makes sure you only have one result. eventually, you’re not querying for rows.

This post has been edited by Dormilich: 25 May 2011 - 01:07 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1