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
<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.
'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
Tcl
Also requires the TDBC driver for MySQL, and the md5 package from tcllib. <lang Tcl>package require tdbc package require
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
}
proc r64k {} {
expr int(65536*rand())
} proc encode {salt pass} {
md5::md5 $salt$pass
}
proc create_user {handle user pass} {
set salt [binary format ssssssss \
[r64k] [r64k] [r64k] [r64k] [r64k] [r64k] [r64k] [r64k]]
set md5 [encode $salt $pass]
$handle allrows {
INSERT IGNORE INTO users (username, pass_salt, pass_md5)
VALUES (:user, :salt, :md5)
}
return ;# Ignore the result of the allrows method
}
proc authenticate_user {handle user pass} {
$handle foreach row {
SELECT userid, pass_salt, pass_md5 FROM users WHERE
username=:user LIMIT 1
} {
dict with row {
if {$pass_md5 eq [encode $pass_salt $pass]} {
return $userid
}
}
}
error "authentication failed for user \"$user\""
}</lang>