SQL-based authentication: Difference between revisions
mNo edit summary |
(omitted from batch file) |
||
Line 127: | Line 127: | ||
}</lang> |
}</lang> |
||
{{omit from|Batch File}} |
|||
{{omit from|TI-83 BASIC}} {{omit from|TI-89 BASIC}} |
{{omit from|TI-83 BASIC}} {{omit from|TI-89 BASIC}} |
||
{{omit from|M4}} |
{{omit from|M4}} |
Revision as of 13:38, 15 April 2010
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.
<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
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>