SQL-based authentication: Difference between revisions
Content added Content deleted
(→Tcl: Added implementation) |
(→{{header|Tcl}}: Got rid of the external md5 requirement by "borrowing" SQL statements from the Perl version) |
||
Line 91: | Line 91: | ||
=={{header|Tcl}}== |
=={{header|Tcl}}== |
||
{{works with|Tcl|8.6}} |
{{works with|Tcl|8.6}} |
||
Also requires the TDBC driver for MySQL |
Also requires the TDBC driver for MySQL. |
||
<lang Tcl>package require tdbc |
<lang Tcl>package require tdbc |
||
package require |
package require |
||
Line 102: | Line 102: | ||
} |
} |
||
# A simple helper to keep code shorter |
|||
proc r64k {} { |
proc r64k {} { |
||
expr int(65536*rand()) |
expr int(65536*rand()) |
||
} |
|||
proc encode {salt pass} { |
|||
md5::md5 $salt$pass |
|||
} |
} |
||
Line 112: | Line 110: | ||
set salt [binary format ssssssss \ |
set salt [binary format ssssssss \ |
||
[r64k] [r64k] [r64k] [r64k] [r64k] [r64k] [r64k] [r64k]] |
[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 { |
$handle allrows { |
||
INSERT IGNORE INTO users (username, pass_salt, pass_md5) |
INSERT IGNORE INTO users (username, pass_salt, pass_md5) |
||
VALUES (:user, :salt, |
VALUES (:user, :salt, unhex(md5(concat(:salt, :pass)))) |
||
} |
} |
||
return ;# Ignore the result of the allrows method |
return ;# Ignore the result of the allrows method |
||
Line 122: | Line 121: | ||
proc authenticate_user {handle user pass} { |
proc authenticate_user {handle user pass} { |
||
$handle foreach row { |
$handle foreach row { |
||
SELECT userid |
SELECT userid FROM users WHERE |
||
username=:user |
username=:user AND pass_md5=unhex(md5(concat(pass_salt, :pass))) |
||
} { |
} { |
||
dict |
return [dict get $row userid] |
||
if {$pass_md5 eq [encode $pass_salt $pass]} { |
|||
return $userid |
|||
} |
|||
} |
|||
} |
} |
||
# Only get here if no rows selected |
|||
error "authentication failed for user \"$user\"" |
error "authentication failed for user \"$user\"" |
||
}</lang> |
}</lang> |
Revision as of 20:23, 10 May 2009
SQL-based authentication
You are encouraged to solve this task according to the task description, using any language you may know.
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. <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
}
- 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>