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.)
C
(MySQL client library)
(for MD5)
<lang c>#include <stdio.h>
- include <stdlib.h>
- include <string.h>
- include <stdbool.h>
- include <time.h>
- include <mysql.h>
- include <openssl/md5.h>
void end_with_db(void);
MYSQL *mysql = NULL; // global...
bool connect_db(const char *host, const char *user, const char *pwd, const char *db, unsigned int port) {
if ( mysql == NULL ) { if (mysql_library_init(0, NULL, NULL)) return false; mysql = mysql_init(NULL); if ( mysql == NULL ) return false; MYSQL *myp = mysql_real_connect(mysql, host, user, pwd, db, port, NULL, 0); if (myp == NULL) { fprintf(stderr, "connection error: %s\n", mysql_error(mysql)); end_with_db(); return false; } } return true; // already connected... ?
}
- define USERNAMELIMIT 32
// no part of the spec, but it is reasonable!!
- define PASSWORDLIMIT 32
- define SALTBYTE 16
bool create_user(const char *username, const char *password) {
int i; char binarysalt[SALTBYTE]; char salt[SALTBYTE*2+1]; char md5hash[MD5_DIGEST_LENGTH]; char saltpass[SALTBYTE+PASSWORDLIMIT+1]; char pass_md5[MD5_DIGEST_LENGTH*2 + 1]; char user[USERNAMELIMIT*2 + 1]; char *q = NULL; static const char query[] = "INSERT INTO users " "(username,pass_salt,pass_md5) " "VALUES ('%s', X'%s', X'%s')"; static const size_t qlen = sizeof query;
for(i=0; username[i] != '\0' && i < USERNAMELIMIT; i++) ; if ( username[i] != '\0' ) return false; for(i=0; password[i] != '\0' && i < PASSWORDLIMIT; i++) ; if ( password[i] != '\0' ) return false;
srand(time(NULL));
for(i=0; i < SALTBYTE; i++) { // this skews the distribution but it is lazyness-compliant;) binarysalt[i] = rand()%256; }
(void)mysql_hex_string(salt, binarysalt, SALTBYTE); for(i=0; i < SALTBYTE; i++) saltpass[i] = binarysalt[i]; strcpy(saltpass+SALTBYTE, password); (void)MD5(saltpass, SALTBYTE + strlen(password), md5hash); (void)mysql_hex_string(pass_md5, md5hash, MD5_DIGEST_LENGTH);
(void)mysql_real_escape_string(mysql, user, username, strlen(username));
// salt, pass_md5, user are db-query-ready q = malloc(qlen + USERNAMELIMIT*2 + MD5_DIGEST_LENGTH*2 + SALTBYTE*2 + 1); if ( q == NULL ) return false; sprintf(q, query, user, salt, pass_md5);
- if defined(DEBUG)
fprintf(stderr, "QUERY:\n%s\n\n", q);
- endif
int res = mysql_query(mysql, q); free(q); if ( res != 0 ) { fprintf(stderr, "create_user query error: %s\n", mysql_error(mysql)); return false; } return true;
}
bool authenticate_user(const char *username, const char *password)
{
char user[USERNAMELIMIT*2 + 1]; char md5hash[MD5_DIGEST_LENGTH]; char saltpass[SALTBYTE+PASSWORDLIMIT+1]; bool authok = false; char *q = NULL; int i; static const char query[] = "SELECT * FROM users WHERE username='%s'"; static const size_t qlen = sizeof query;
// can't be authenticated with invalid username or password for(i=0; username[i] != '\0' && i < USERNAMELIMIT; i++) ; if ( username[i] != '\0' ) return false; for(i=0; password[i] != '\0' && i < PASSWORDLIMIT; i++) ; if ( password[i] != '\0' ) return false;
(void)mysql_real_escape_string(mysql, user, username, strlen(username));
q = malloc(qlen + strlen(user) + 1); if (q == NULL) return false; sprintf(q, query, username);
int res = mysql_query(mysql, q); free(q); if ( res != 0 ) { fprintf(stderr, "authenticate_user query error: %s\n", mysql_error(mysql)); return false; }
MYSQL_RES *qr = mysql_store_result(mysql); if ( qr == NULL ) return false;
// should be only a result, or none if ( mysql_num_rows(qr) != 1 ) { mysql_free_result(qr); return false; }
MYSQL_ROW row = mysql_fetch_row(qr); // 1 row must exist unsigned long *len = mysql_fetch_lengths(qr); // and should have 4 cols...
memcpy(saltpass, row[2], len[2]); // len[2] should be SALTBYTE memcpy(saltpass + len[2], password, strlen(password)); (void)MD5(saltpass, SALTBYTE + strlen(password), md5hash);
authok = memcmp(md5hash, row[3], len[3]) == 0; mysql_free_result(qr);
return authok;
}
void end_with_db(void) {
mysql_close(mysql); mysql = NULL; mysql_library_end();
}
int main(int argc, char **argv) {
if ( argc < 4 ) return EXIT_FAILURE;
if ( connect_db("localhost", "devel", "", "test", 0 ) ) { if ( strcmp(argv[1], "add") == 0 ) { if (create_user(argv[2], argv[3]))
printf("created\n");
} else if ( strcmp(argv[1], "auth") == 0 ) { if (authenticate_user(argv[2], argv[3]))
printf("authorized\n");
else
printf("access denied\n");
} else { printf("unknown command\n"); } end_with_db(); } return EXIT_SUCCESS;
}</lang>
From the command line, program add user password to add users, and program auth user password to see if the user with that password is authorized or not.
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>
PHP
To use MySQL in PHP you need the php_mysql module installed <lang php> function connect_db($database, $db_user, $db_password, $host = 'localhost', $port = NULL, $die = false) { // Returns a MySQL link identifier (handle) on success // Returns false or dies() on error depending on the setting of parameter $die // Parameter $die configures error handling, setting it any non-false value will die() on error // Parameters $host, $port and $die have sensible defaults and are not usually required
if(!$db_handle = @mysql_connect($host.($port ? ':'.$port : ), $db_user, $db_password)) { if($die) die("Can't connect to MySQL server:\r\n".mysql_error()); else return false; } if(!@mysql_select_db($database, $db_handle)) { if($die) die("Can't select database '$database':\r\n".mysql_error()); else return false; } return $db_handle; }
function create_user($username, $password, $db_handle) { // Returns the record ID on success or false on failure // Username limit is 32 characters (part of spec) if(strlen($username) > 32) return false;
// Salt limited to ASCII 32 thru 254 (not part of spec) $salt = ; do { $salt .= chr(mt_rand(32, 254)); } while(strlen($salt) < 16);
// Create pass_md5 $pass_md5 = md5($salt.$password);
// Make it all binary safe $username = mysql_real_escape_string($username); $salt = mysql_real_escape_string($salt);
// Try to insert it into the table - Return false on failure if(!@mysql_query("INSERT INTO users (username,pass_salt,pass_md5) VALUES('$username','$salt','$pass_md5')", $db_handle)) return false;
// Return the record ID return mysql_insert_id($db_handle); }
function authenticate_user($username, $password, $db_handle) { // Checks a username/password combination against the database // Returns false on failure or the record ID on success
// Make the username parmeter binary-safe $safe_username = mysql_real_escape_string($username);
// Grab the record (if it exists) - Return false on failure if(!$result = @mysql_query("SELECT * FROM users WHERE username='$safe_username'", $db_handle)) return false;
// Grab the row $row = @mysql_fetch_assoc($result);
// Check the password and return false if incorrect if(md5($row['pass_salt'].$password) != $row['pass_md5']) return false;
// Return the record ID return $row['userid']; } </lang>
Python
<lang python>with Python 2.6, gives a deprecation warning for sets module, but works import MySQLdb import hashlib
import sys import random
DB_HOST = "localhost" DB_USER = "devel" DB_PASS = "" DB_NAME = "test"
def connect_db():
Try to connect DB and return DB instance, if not, return False try: return MySQLdb.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASS, db=DB_NAME) except: return False
def create_user(username, passwd):
if user was successfully created, returns its ID db = connect_db() if not db: print 'Can\'t connect MySQL!' sys.exit(1)
cursor = db.cursor()
salt = randomValue(16) passwd_md5 = hashlib.md5(salt+passwd).hexdigest()
# If username already taken, inform it try: cursor.execute("INSERT INTO users (`username`, `pass_salt`, `pass_md5`) VALUES ('%s', '%s', '%s')" % (username, salt, passwd_md5)) cursor.execute("SELECT userid FROM users WHERE username='%s'" % username) id = cursor.fetchall() return id[0][0] except: print 'Username was already taken. Please select another' sys.exit(1)
def authenticate_user(username, passwd):
db = connect_db() if not db: print 'Can\'t connect MySQL!' sys.exit(1)
cursor = db.cursor()
try: scheck = cursor.execute("SELECT pass_salt FROM users WHERE username='%s'" % (username)) except: return False
if scheck == 0: return False
salt = cursor.fetchone()[0] passwd = hashlib.md5(salt+passwd).hexdigest()
# cursor returns 1 if query is successfull else it returns 0 user = cursor.execute("SELECT userid, username FROM users WHERE username='%s' AND pass_md5='%s'" % (username, passwd)) if user != 0: return True else: return False
def randomValue(length):
Creates random value with given length salt_chars = 'abcdefghijklmnopqrstuvwxyz0123456789' output = ""
for x in range(length): rand = random.randrange(0, 35) output = output + salt_chars[rand]
return output
if __name__ == '__main__':
user = randomValue(10) passwd = randomValue(16)
create_user(user, passwd) auth = authenticate_user(user, passwd) if auth: print 'User %s authenticated successfully' % user else: print 'User %s failed' % user</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>