SQL-based authentication
![Task](http://static.miraheze.org/rosettacodewiki/thumb/b/ba/Rcode-button-task-crushed.png/64px-Rcode-button-task-crushed.png)
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
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 }