SQL-based authentication: Difference between revisions
m (format SQL as SQL) |
(→{{header|Python}}: Fixed SQL injection; changed library to the official one; fixed some other issues) |
||
Line 452: | Line 452: | ||
=={{header|Python}}== |
=={{header|Python}}== |
||
{{works with|Python|2.7}} |
|||
{{improve|Python|This example is missing proper encoding or bound parameters to avoid SQL injection, which are present in the other examples. In general is is an unsafe practice to create sql commands directly with string operations}} |
|||
Uses the [http://dev.mysql.com/downloads/connector/python/ official Python MySQL connector] |
|||
<lang python>'''with Python 2.6, gives a deprecation warning for sets module, but works''' |
|||
import |
<lang python>import mysql.connector |
||
import hashlib |
import hashlib |
||
import sys |
import sys |
||
import random |
import random |
||
DB_HOST = "localhost" |
DB_HOST = "localhost" |
||
DB_USER = "devel" |
DB_USER = "devel" |
||
DB_PASS = "" |
DB_PASS = "devel" |
||
DB_NAME = "test" |
DB_NAME = "test" |
||
def connect_db(): |
def connect_db(): |
||
''' Try to connect DB and return DB instance, if not, return False ''' |
''' Try to connect DB and return DB instance, if not, return False ''' |
||
try: |
try: |
||
return |
return mysql.connector.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASS, db=DB_NAME) |
||
except: |
except: |
||
return False |
return False |
||
def create_user(username, passwd): |
def create_user(username, passwd): |
||
''' if user was successfully created, returns its ID ''' |
''' if user was successfully created, returns its ID; returns None on error ''' |
||
db = connect_db() |
db = connect_db() |
||
if not db: |
if not db: |
||
print |
print "Can't connect MySQL!" |
||
return None |
|||
cursor = db.cursor() |
cursor = db.cursor() |
||
salt = randomValue(16) |
salt = randomValue(16) |
||
passwd_md5 = hashlib.md5(salt+passwd).hexdigest() |
passwd_md5 = hashlib.md5(salt+passwd).hexdigest() |
||
# If username already taken, inform it |
# If username already taken, inform it |
||
try: |
try: |
||
cursor.execute("INSERT INTO users (`username`, `pass_salt`, `pass_md5`) VALUES ( |
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= |
cursor.execute("SELECT userid FROM users WHERE username=%s", (username,) ) |
||
id = cursor. |
id = cursor.fetchone() |
||
db.commit() |
|||
⚫ | |||
db.close() |
|||
⚫ | |||
except: |
except: |
||
print 'Username was already taken. Please select another' |
print 'Username was already taken. Please select another' |
||
return None |
|||
def authenticate_user(username, passwd): |
def authenticate_user(username, passwd): |
||
db = connect_db() |
db = connect_db() |
||
if not db: |
if not db: |
||
print |
print "Can't connect MySQL!" |
||
return False |
|||
cursor = db.cursor() |
cursor = db.cursor() |
||
⚫ | |||
row = cursor.fetchone() |
|||
try: |
|||
cursor.close() |
|||
⚫ | |||
db.close() |
|||
if row is None: # username not found |
|||
return False |
return False |
||
salt = row[0] |
|||
correct_md5 = row[1] |
|||
⚫ | |||
return False |
|||
return correct_md5 == tried_md5 |
|||
⚫ | |||
⚫ | |||
# 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: |
|||
⚫ | |||
else: |
|||
return False |
|||
def randomValue(length): |
def randomValue(length): |
||
''' Creates random value with given length''' |
''' Creates random value with given length''' |
||
salt_chars = 'abcdefghijklmnopqrstuvwxyz0123456789' |
salt_chars = 'abcdefghijklmnopqrstuvwxyz0123456789' |
||
output = "" |
|||
return ''.join(random.choice(salt_chars) for x in range(length)) |
|||
for x in range(length): |
|||
rand = random.randrange(0, 35) |
|||
output = output + salt_chars[rand] |
|||
return output |
|||
if __name__ == '__main__': |
if __name__ == '__main__': |
||
user = randomValue(10) |
user = randomValue(10) |
||
passwd = randomValue(16) |
passwd = randomValue(16) |
||
create_user(user, passwd) |
new_user_id = create_user(user, passwd) |
||
if new_user_id is None: |
|||
print 'Failed to create user %s' % user |
|||
sys.exit(1) |
|||
auth = authenticate_user(user, passwd) |
auth = authenticate_user(user, passwd) |
||
if auth: |
if auth: |
||
print 'User %s authenticated successfully' % user |
print 'User %s authenticated successfully' % user |
||
else: |
else: |
||
print 'User %s failed' % user |
print 'User %s failed' % user |
||
</lang> |
|||
=={{header|Raven}}== |
=={{header|Raven}}== |
Revision as of 18:07, 21 October 2012
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: <lang sql>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
);</lang> (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.
Java
<lang java>import java.io.UnsupportedEncodingException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.security.MessageDigest; import java.security.NoSuchAlgorithmException; import java.security.SecureRandom; import java.math.BigInteger;
class UserManager {
private Connection dbConnection;
public UserManager() { }
private String md5(String aString) throws NoSuchAlgorithmException, UnsupportedEncodingException { MessageDigest md; String hex; StringBuffer hexString; byte[] bytesOfMessage; byte[] theDigest;
hexString = new StringBuffer(); bytesOfMessage = aString.getBytes("UTF-8"); md = MessageDigest.getInstance("MD5"); theDigest = md.digest(bytesOfMessage);
for (int i = 0; i < theDigest.length; i++) { hex = Integer.toHexString(0xff & theDigest[i]); if (hex.length() == 1) { hexString.append('0'); } hexString.append(hex); }
return hexString.toString(); }
public void connectDB(String host, int port, String db, String user, String password) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
this.dbConnection = DriverManager.getConnection("jdbc:mysql://" + host + ":" + port + "/" + db, user, password); }
public boolean createUser(String user, String password) { SecureRandom random; String insert; String salt;
random = new SecureRandom(); salt = new BigInteger(130, random).toString(16);
insert = "INSERT INTO users " + "(username, pass_salt, pass_md5) " + "VALUES (?, ?, ?)";
try (PreparedStatement pstmt = this.dbConnection.prepareStatement(insert)) { pstmt.setString(1, user); pstmt.setString(2, salt); pstmt.setString(3, this.md5(salt + password)); pstmt.executeUpdate();
return true; } catch(NoSuchAlgorithmException | SQLException | UnsupportedEncodingException ex) { return false; } }
public boolean authenticateUser(String user, String password) { String pass_md5; String pass_salt; String select; ResultSet res;
select = "SELECT pass_salt, pass_md5 FROM users WHERE username = ?"; res = null;
try(PreparedStatement pstmt = this.dbConnection.prepareStatement(select)) { pstmt.setString(1, user); res = pstmt.executeQuery();
res.next(); // We assume that username is unique
pass_salt = res.getString(1); pass_md5 = res.getString(2);
if (pass_md5.equals(this.md5(pass_salt + password))) { return true; } else { return false; }
} catch(NoSuchAlgorithmException | SQLException | UnsupportedEncodingException ex) { return false; } finally { try { if (res instanceof ResultSet && !res.isClosed()) { res.close(); } } catch(SQLException ex) { } } }
public void closeConnection() { try { this.dbConnection.close(); } catch(NullPointerException | SQLException ex) { } }
public static void main(String[] args) { UserManager um;
um = new UserManager(); try { um.connectDB("localhost", 3306, "test", "root", "admin");
if (um.createUser("johndoe", "test")) { System.out.println("User created"); }
if (um.authenticateUser("johndoe", "test")) { System.out.println("User authenticated"); } } catch(ClassNotFoundException | SQLException ex) { ex.printStackTrace(); } finally { um.closeConnection(); } }
}</lang>
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
Uses the official Python MySQL connector <lang python>import mysql.connector import hashlib
import sys import random
DB_HOST = "localhost" DB_USER = "devel" DB_PASS = "devel" DB_NAME = "test"
def connect_db():
Try to connect DB and return DB instance, if not, return False try: return mysql.connector.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; returns None on error db = connect_db() if not db: print "Can't connect MySQL!" return None 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.fetchone() db.commit() cursor.close() db.close() return id[0] except: print 'Username was already taken. Please select another' return None
def authenticate_user(username, passwd):
db = connect_db() if not db: print "Can't connect MySQL!" return False cursor = db.cursor() cursor.execute("SELECT pass_salt, pass_md5 FROM users WHERE username=%s", (username,))
row = cursor.fetchone() cursor.close() db.close() if row is None: # username not found return False salt = row[0] correct_md5 = row[1] tried_md5 = hashlib.md5(salt+passwd).hexdigest() return correct_md5 == tried_md5
def randomValue(length):
Creates random value with given length salt_chars = 'abcdefghijklmnopqrstuvwxyz0123456789'
return .join(random.choice(salt_chars) for x in range(length))
if __name__ == '__main__':
user = randomValue(10) passwd = randomValue(16) new_user_id = create_user(user, passwd) if new_user_id is None: print 'Failed to create user %s' % user sys.exit(1) 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> Template:Omit from PostScript