SQL-based authentication: Difference between revisions
Content added Content deleted
(Add Python Code) |
m (Alphabetized) |
||
Line 51: | Line 51: | ||
$userid |
$userid |
||
} |
} |
||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
=={{header|Python}}== |
=={{header|Python}}== |
||
Line 175: | Line 139: | ||
print 'User %s failed' % user |
print 'User %s failed' % user |
||
</nowiki></pre> |
</nowiki></pre> |
||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ | |||
⚫ |
Revision as of 17:46, 13 December 2007
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
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 }
Python
Version: 2.4, 2.5
Note: In example below, mysql-python library is used. You can get it from http://mysql-python.sourceforge.net/
#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb import md5 import sys import random DB_HOST = "localhost" DB_USER = "root" 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() passwd_md5 = md5.md5(passwd).hexdigest() # If username already taken, inform it try: cursor.execute("INSERT INTO users (`username`, `pass_salt`, `pass_md5`) VALUES ('%s', '%s', '%s')" % (username, passwd, 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() passwd = md5.md5(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: 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
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