SQL-based authentication

This task has three parts:

Connect to a MySQL database (connect_db)
Create user/password records in the following table (create_user)
Authenticate login requests against the table (authenticate_user)

This is the table definition:

create table users (
    userid int primary key auto_increment,
    username varchar(32) unique key not null,
    pass_salt tinyblob not null,  -- a string of 16 random bytes
    pass_md5 tinyblob not null
            -- binary MD5 hash of pass_salt concatenated with the password
);

(pass_salt and pass_md5 would be binary(16) values, but MySQL versions before 5.0.15 strip trailing spaces when selecting them.)

Perl

Interpreter: perl 5.8.8

Libraries: DBI, DBD::mysql

use DBI;

 # returns a database handle configured to throw an exception on query errors
sub connect_db {
    my ($dbname, $host, $user, $pass) = @_;
    my $db = DBI->connect("dbi:mysql:$dbname:$host", $user, $pass)
        or die $DBI::errstr;
    $db->{RaiseError} = 1;
    $db
}

 # if the user was successfully created, returns its user id.
 # if the name was already in use, returns undef.
sub create_user {
    my ($db, $user, $pass) = @_;
    my $salt = pack "C*", map {int rand 256} 1..16;
    $db->do("insert ignore into users (username, pass_salt, pass_md5)
        values (?, ?, unhex(md5(concat(pass_salt, ?))))",
        undef, $user, $salt, $pass)
      and $db->{mysql_insertid} or undef
}

 # if the user is authentic, returns its user id.  otherwise returns undef.
sub authenticate_user {
    my ($db, $user, $pass) = @_;
    my $userid = $db->selectrow_array("select userid from users where
        username=? and pass_md5=unhex(md5(concat(pass_salt, ?)))",
        undef, $user, $pass);
    $userid
}

Raven

MySQL connectivity is available out of the box. Below, we do not ever send the plain text password over the wire.

 'mysql://root@localhost/test' open as mysql
'abcdefghijklmnopqrstuvwxyz0123456789' as $salt_chars

# return userid for success and FALSE for failure.
define create_user use $user, $pass
    group 16 each as i
        $salt_chars choose chr
    join as $pass_salt
     "%($pass_salt)s%($pass)s" md5 as $pass_md5
    $user copy mysql escape as $user_name
    group 'INSERT IGNORE into users (username, pass_md5, pass_salt)'
        " VALUES ('%($user_name)s', unhex('%($pass_md5)s'), '%($pass_salt)s')"
    join mysql query inserted

# return userid for success and FALSE for failure.
define authenticate_user use $user, $pass
    FALSE as $userid
    $user copy mysql escape as $user_name
    group 'SELECT userid, pass_salt, hex(pass_md5)'
        " FROM users WHERE username = '%($user_name)s'"
    join mysql query as rs
    rs selected
    if  rs fetch values into $possible_userid, $pass_salt, $pass_md5
        "%($pass_salt)s%($pass)s" md5 $pass_md5 lower =
        if  $possible_userid as $userid
    $userid

'foo' 'bar' create_user       !if "could not create user\n"       print bye
'foo' 'bar' authenticate_user !if "could not authenticate user\n" print bye

"user successfully created and authenticated!\n" print

Python

Version: 2.4, 2.5

Note: In example below, mysql-python library is used. You can get it from http://mysql-python.sourceforge.net/

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb
import md5

import sys
import random

DB_HOST = "localhost"
DB_USER = "root"
DB_PASS = ""
DB_NAME = "test"

def connect_db():
    ''' Try to connect DB and return DB instance, if not, return False '''
    try:
        return MySQLdb.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASS, db=DB_NAME)
    except:
        return False

def create_user(username, passwd):
    ''' if user was successfully created, returns its ID '''
    db = connect_db()
    if not db:
        print 'Can\'t connect MySQL!'
        sys.exit(1)

    cursor = db.cursor()

    passwd_md5 = md5.md5(passwd).hexdigest()

    # If username already taken, inform it
    try:
        cursor.execute("INSERT INTO users (`username`, `pass_salt`, `pass_md5`) VALUES ('%s', '%s', '%s')" % (username, passwd, passwd_md5))
        cursor.execute("SELECT userid FROM users WHERE username='%s'" % username)
        id = cursor.fetchall()
        return id[0][0]
    except:
        print 'Username was already taken. Please select another'
        sys.exit(1)

def authenticate_user(username, passwd):
    db = connect_db()
    if not db:
        print 'Can\'t connect MySQL!'
        sys.exit(1)

    cursor = db.cursor()

    passwd = md5.md5(passwd).hexdigest()

    # cursor returns 1 if query is successfull else it returns 0
    user = cursor.execute("SELECT userid, username FROM users WHERE username='%s' AND pass_md5='%s'" % (username, passwd))
    if user != 0:
        return True
    else:
        False

def randomValue(length):
    ''' Creates random value with given length'''
    salt_chars = 'abcdefghijklmnopqrstuvwxyz0123456789'
    output = ""

    for x in range(length):
        rand = random.randrange(0, 35)
        output = output + salt_chars[rand]

    return output

if __name__ == '__main__':
    user = randomValue(10)
    passwd = randomValue(16)

    create_user(user, passwd)
    auth = authenticate_user(user, passwd)
    if auth:
        print 'User %s authenticated successfully' % user
    else:
        print 'User %s failed' % user