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.)

To use MySQL in PHP you need the php_mysql module installed <lang php> function connect_db($database, $db_user, $db_password, $host = 'localhost', $port = NULL, $die = false) { // Returns a MySQL link identifier (handle) on success // Returns false or dies() on error depending on the setting of parameter $die // Parameter $die configures error handling, setting it any non-false value will die() on error // Parameters $host, $port and $die have sensible defaults and are not usually required

if(!$db_handle = @mysql_connect($host.($port ? ':'.$port : ), $db_user, $db_password)) { if($die) die("Can't connect to MySQL server:\r\n".mysql_error()); else return false; } if(!@mysql_select_db($database, $db_handle)) { if($die) die("Can't select database '$database':\r\n".mysql_error()); else return false; } return $db_handle; }

function create_user($username, $password, $db_handle) { // Returns the record ID on success or false on failure // Username limit is 32 characters (part of spec) if(strlen($username) > 32) return false;

// Salt limited to ASCII 32 thru 254 (not part of spec) $salt = ; do { $salt .= chr(mt_rand(32, 254)); } while(strlen($salt) < 16);

// Create pass_md5 $pass_md5 = md5($salt.$password);

// Make it all binary safe $username = mysql_real_escape_string($username); $salt = mysql_real_escape_string($salt);

// Try to insert it into the table - Return false on failure if(!@mysql_query("INSERT INTO users (username,pass_salt,pass_md5) VALUES('$username','$salt','$pass_md5')", $db_handle)) return false;

// Return the record ID return mysql_insert_id($db_handle); }

function authenticate_user($username, $password, $db_handle) { // Checks a username/password combination against the database // Returns false on failure or the record ID on success

// Make the username parmeter binary-safe $safe_username = mysql_real_escape_string($username);

// Grab the record (if it exists) - Return false on failure if(!$result = @mysql_query("SELECT * FROM users WHERE username='$safe_username'", $db_handle)) return false;

// Grab the row $row = @mysql_fetch_assoc($result);

// Check the password and return false if incorrect if(md5($row['pass_salt'].$password) != $row['pass_md5']) return false;

// Return the record ID return $row['userid']; } </lang>

Raven

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

<lang raven> '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</lang>

Tcl

Works with: Tcl version 8.6

Also requires the TDBC driver for MySQL. <lang Tcl>package require tdbc

proc connect_db {handleName dbname host user pass} {

   package require tdbc::mysql
   tdbc::mysql::connection create $handleName -user $user -passwd $pass \
       -host $host -database $dbname
   return $handleName

}

A simple helper to keep code shorter

proc r64k {} {

   expr int(65536*rand())

}

proc create_user {handle user pass} {

   set salt [binary format ssssssss \
       [r64k] [r64k] [r64k] [r64k] [r64k] [r64k] [r64k] [r64k]]
   # Note that we are using named parameters below, :user :salt :pass
   # They are bound automatically to local variables with the same name
   $handle allrows {
       INSERT IGNORE INTO users (username, pass_salt, pass_md5)
           VALUES (:user, :salt, unhex(md5(concat(:salt, :pass))))
   }
   return   ;# Ignore the result of the allrows method

}

proc authenticate_user {handle user pass} {

   $handle foreach row {
       SELECT userid FROM users WHERE
           username=:user AND pass_md5=unhex(md5(concat(pass_salt, :pass)))
   } {
       return [dict get $row userid]
   }
   # Only get here if no rows selected
   error "authentication failed for user \"$user\""

}</lang>

Perl

PHP

Raven

Tcl