SQL-based authentication

SQL-based authentication
You are encouraged to solve this task according to the task description, using any language you may know.

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

Works with: Perl version 5.8.8

Library: DBI

Library: DBD::mysqlmysql

<lang perl>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

}</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

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

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

}

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