Hack 90. Implement Application-Level Accounts

When you are managing user details in your own applications, you need to keep in mind ease of design and security.

You should never record important passwords in plain text in an SQL table. People who have operating-system-level access to your server may be able to access other people's passwords. All of your backups include this sensitive information and anyone handling the backups could be tempted to look at the passwords. As well, if you copy backups over the network, anyone can sniff the traffic on the network and get the passwords, so operating-system-level access on the database server is not the only way to compromise your security.

11.1.1. Storing User-Specific Information

Web-based applications, as well as other types of systems, may need to use username-based identification. An application may store different pieces of information for each user, such as a password field. Part of this design will involve the concept of a user table.

An email address is an excellent choice for a username. Users don't need to generate their own usernames, and a made-up username is more easily forgotten than an email address. However, an email address is a bad choice for a primary key in your users table. Email addresses may be unique to individuals, but individuals can have multiple email addresses. As time passes, a user may decide to change his email address. Changing a primary key in an application with a complex schema can be difficult, as the change may have to be cascaded down through many different foreign key relationships. An email-based username needs to be unique in the users table, but because it should not be the primary key, it should never appear as a field in a foreign key.

If your application posts user information in publicly accessible parts of your web site, you should consider hiding the user's email address and replacing it with something else. For instance, a site with a forum could allow posters to be identified by some kind of nickname. This helps to protect the privacy of your users, while still maintaining user identity. Some users may appreciate the ability to log in using either their nickname or their email address, if you choose to offer this capability.

Here's a sample subscribers table:

CREATE TABLE subscribers
(userid VARCHAR(20)
,email VARCHAR(100)
,nickname VARCHAR(50) 
,password VARCHAR(41)
,PRIMARY KEY(userid)
,UNIQUE (email)
,UNIQUE (nickname)
);

The userid should be a unique identifier for that user, and should never change for the lifetime of that user account. It could be as simple as a sequence number. A disadvantage of a sequence number is that a user may be able to predict other valid userid values, and use that information to somehow breach your application's security. Therefore, a random (but unique) ID is generally preferred:

mysql> INSERT INTO subscribers
 -> VALUES (1122334,'[e-mail]','bingo','house');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO subscribers
 -> VALUES (4433221,'[e-mail]','poker','flush');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM subscribers;
+---------+-----------------------+----------+----------+
| userid | email | nickname | password |
+---------+-----------------------+----------+----------+
| 1122334 | [e-mail] | bingo | house |
| 4433221 | [e-mail] | poker | flush |
+---------+-----------------------+----------+----------+
2 rows in set (0.00 sec)

 

11.1.2. Hash Your Passwords

If the passwords are stored in plain text, as shown in the preceding examples, you compromise not only your security, but also potentially the security of other systems. Users may use the same password (or a variation on the password) for different systems. The malicious hacker who gets bingo's password on your system may find that bingo uses the same password for his bank account. Even though you may not be liable for someone using the same password on two different systems, do you really want the negative publicity that would result if a security breach on your system caused someone's bank account to be compromised?

A hash function allows you to scramble the incoming passwords and store the hash value in place of the plain text password. Good hashes are one-way, which means that you cannot figure out the password even if you know the hash function, and deterministic, which means that they give the same result every time.

A trivial hash function would be to take the first and third characters of the password and store them in place of the actual password. Even though this approach is one-way and deterministic, it is not free of collisions (many different passwords will hash to the same value). This is a terrible hash function to use in a real system (but it does demonstrate the idea):

mysql> UPDATE subscribers
 -> SET password = CONCAT(SUBSTRING(password,1,1),
 -> SUBSTRING(password,3,1))
 -> ;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> SELECT * FROM subscribers;
+---------+-----------------------+----------+----------+
| userid | email | nickname | password |
+---------+-----------------------+----------+----------+
| 1122334 | [e-mail] | bingo | hu |
| 4433221 | [e-mail] | poker | fu |
+---------+-----------------------+----------+----------+
2 rows in set (0.00 sec)

Even though the system no longer stores passwords, you can still check them. When bingo attempts to log in you simply apply the same hashing function to the password he gives you and compare that to the stored value. You do not know what the original password is, so this is a one-way hash. You also get the same result every time: if bingo's password is house, the hash function will always return hu.

However, if bingo's password were house, you'd have a serious problem. When your application asks a user for a password, it should perform a strength check to make sure the password is not a word that appears in a dictionary (or a variation on one). See http://en.wikipedia.org/wiki/Password_strength for more information.

Now, if your security has been breached, you do not have to worry about people being able to log in to your user's accounts. No passwords have been given away. Anyone who sees the password stored in the database would need to either reverse the hashing function (which is an intractable problem) or perform a successful dictionary attack against the password database in order to discover the password. Once your password database has been stolen, the attacker has all the time in the world (at least until the next time users change passwords), and can try to hash all the words in the dictionary using the same hashing algorithm until one of them matches the hashed password.

There are many problems with a simple hash function such as the one shown earlier. First, it gives the cracker a clue about the actual password. Second, the hash value depends on only part of the password; if bingo changes his password from house to housey the old password still works.

Fortunately, some good, well-tested hashing algorithms exist. MD5 is very popular, and the database engines have hashing algorithms built in.

In MySQL and PostgreSQL, the MD5 function applies a hash:

mysql> UPDATE subscribers
 -> SET password = MD5('0uR_h0Us3') WHERE nickname='bingo';
UPDATE subscribers
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> UPDATE subscribers
 -> SET password = MD5('pL34S3_fLu5H') WHERE nickname='poker';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM subscribers;
*************************** 1. row ***************************
 userid: 1122334
 email: [e-mail]
nickname: bingo
password: b9e1d36934b8736b231a318b39e084fb
*************************** 2. row ***************************
 userid: 4433221
 email: [e-mail]
nickname: poker
password: 3fb96fde2997de43cbd1ba1af9404cf0 
2 rows in set (0.00 sec)

Note that the users in this example have made slightly better password choices, which use two words (our house and please flush) with simple number replacements (0 for O, 3 for E, 4 for A, and 5 for S), a nonalphanumeric character (_), and mixed uppercase/lowercase.

When bingo logs in next time, you can check whether his password is correct with the following SQL:

mysql> SELECT nickname FROM subscribers
 -> WHERE nickname='bingo'
 -> AND password=MD5('0uR_h0Us3');
+----------+
| nickname |
+----------+
| bingo |
+----------+
1 row in set (0.00 sec)

If he gives an incorrect password, no rows will be returned:

mysql> SELECT nickname FROM subscribers
 -> WHERE nickname='bingo'
 -> AND password=MD5('wrong-password');
Empty set (0.00 sec)

 

11.1.3. Oracle

In Oracle 10g, you need to use the DBMS_CRYPTO library to generate an MD5 hash. This needs the current user to have sufficient rights to access DBMS_CRYPTO. So, as SYSDBA:

GRANT execute ON dbms_crypto TO username;

Now as a normal user, create a helper function to access the library:

CREATE OR REPLACE FUNCTION return_hash(data IN VARCHAR) RETURN RAW IS
 BEGIN
 RETURN DBMS_CRYPTO.HASH(UTL_I18N.STRING_TO_RAW (data, 'AL32UTF8'),
 DBMS_CRYPTO.HASH_MD5);
 END;
/

With this function, your UPDATE statements become:

UPDATE subscribers 
 SET password = return_hash('0uR_h0Us3') WHERE nickname='bingo';
UPDATE subscribers 
 SET password = return_hash('pL34S3_fLu5H') WHERE nickname='poker';

This leaves the subscribers table as:

SQL> SELECT * FROM subscribers;

USERID EMAIL NICKNAME PASSWORD
---------- --------------------- --------- --------------------------------
1122334 [e-mail] bingo B9E1D36934B8736B231A318B39E084FB
4433221 [e-mail] poker 3FB96FDE2997DE43CBD1BA1AF9404CF0

 

11.1.4. SQL Server

An MD5 implementation for SQL Server is freely available; you can download it from http://www.thecodeproject.com/database/xp_md5.asp. This provides you with a dynamic library which, once loaded, allows you to create a user-defined function such as:

CREATE FUNCTION [dbo].[fn_md5] (@data TEXT) 
RETURNS CHAR(32) AS
BEGIN
 DECLARE @hash CHAR(32)
 EXEC master.dbo.xp_md5 @data, -1, @hash OUTPUT
 RETURN @hash
END

This function would allow you to write UPDATE statements such as:

UPDATE subscribers 
 SET password = dbo.fn_md5('0uR_h0Us3') WHERE nickname='bingo';
UPDATE subscribers 
 SET password = dbo.fn_md5('pL34S3_fLu5H') WHERE nickname='poker';

If you are unable to install this library, an undocumented hash function, pwdencrypt, is available that implements a hashing function. Undocumented functions are not guaranteed to survive the next release of SQL Server, but they can be useful. This function applies the technique of salting. Executing pwdencrypt with the same parameters a few seconds apart will produce different output, so it appears at first glance to be a nondeterministic function. However, just the salt changes, and it is appended to the end of each hash so that you can check a password against a hash using the pwdcheck function (also undocumented), which then runs the salt and the supplied plain text through the same deterministic function and confirms that it indeed hashes to the same value:

CREATE TABLE subscribers(
 userid VARCHAR(20)
,email VARCHAR(100)
,nickname VARCHAR(50) 
,password VARBINARY(255)
,PRIMARY KEY(userid)
,UNIQUE (email)
,UNIQUE (nickname)
)
GO
INSERT INTO subscribers VALUES 
 ('1122334','[e-mail]','bingo',pwdencrypt('0uR_h0Us3'))
GO
INSERT INTO subscribers VALUES 
 ('4433221','[e-mail]','poker',pwdencrypt('pL34S3_fLu5H'))
GO

You can check whether the given password is correct. Here it is with the right password:

1> SELECT nickname
2> FROM subscribers
3> WHERE nickname='bingo'
4> AND pwdcompare('0uR_h0Us3',password) = 1;
5> GO
 nickname
 --------------------------------------------------
 bingo 

(1 row affected)

And this is what happens when the password is wrong:

1> SELECT nickname
2> FROM subscribers
3> WHERE nickname='name'
4> AND pwdcompare('wrong-password',password) = 1;
5> GO
 
(0 rows affected)

 

11.1.5. In the Programming Language

Libraries exist for programming languages to perform MD5 hashing. The hashes can therefore be calculated in the programming language itself, rather than by the database. This is preferable in a distributed architecture; if the traffic from the web server to the database server is not encrypted, anyone sniffing the network will be able to retrieve the password (proper password hygiene dictates that you reduce your handling of plain text to the bare minimum). For example, in Perl and PHP you can take the steps shown below (be sure you delete bingo from the table before you try this).

11.1.5.1. Perl

Here is the code in Perl:

#!/usr/bin/perl
use strict;
use DBI;
use Digest::MD5 qw(md5_base64);

my $dbh=DBI->connect('DBI:mysql:dbname','username','password');

#Add bingo to the subscribers table
$dbh->do(sprintf("INSERT INTO subscribers VALUES (%s,%s,%s,%s)",
 $dbh->quote('1122334'),
 $dbh->quote('[e-mail]'),
 $dbh->quote('bingo'),
 $dbh->quote(md5_base64('0uR_h0Us3'))));


#Check bingo's password
my $sql = sprintf ("SELECT nickname FROM subscribers
 WHERE nickname=%s AND password=%s",
 $dbh->quote('bingo'),
 $dbh->quote(md5_base64('0uR_h0Us3')));
my ($who) = $dbh->selectrow_array($sql);
print "Welcome $who
" if $who;

 

11.1.5.2. PHP

Here's how you'd do it in PHP:

username','password') or die(mysql_error( ));
mysql_select_db('dbname') or die(mysql_error( ));

//Add bingo to the subscribers table
$sql = sprintf("INSERT INTO subscribers VALUES ('%s','%s','%s','%s')",
 mysql_real_escape_string('1122334'),
 mysql_real_escape_string('[e-mail]'),
 mysql_real_escape_string('bingo'),
 mysql_real_escape_string(md5('0uR_h0Us3'))
 );
mysql_query($sql) or die(mysql_error( ));

//Check bingo's password
$sql = sprintf("SELECT nickname FROM subscribers
 WHERE nickname='%s' AND password='%s'",
 mysql_real_escape_string('bingo'),
 mysql_real_escape_string(md5('0uR_h0Us3'))
 );
$cursor = mysql_query($sql) or die(mysql_error( ));
$who = mysql_fetch_array($cursor,MYSQL_ASSOC);
if ($who) {
 print "Welcome ".$who{nickname}."
";
}
?>

 

11.1.6. See Also

• "Exploit an SQL Injection Vulnerability" [Hack #47]
• PHP Hacks, by Jack D. Herrington (O'Reilly), includes Hack #58 on hashing passwords and Hack #59 on fixing a system that stores passwords in plain text.