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: <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.
C#
Class for hashing and random salt generation. <lang csharp>using System.Security.Cryptography; using System.Text;
namespace rosettaMySQL {
class Hasher { private static string _BytesToHex(byte[] input) { var strBuilder = new StringBuilder(); foreach (byte _byte in input) { strBuilder.Append(_byte.ToString("x2")); } return strBuilder.ToString(); }
public static string Hash(string salt, string input) { using (MD5 md5 = new MD5CryptoServiceProvider()) { var bytes = Encoding.Default.GetBytes(salt + input); var data = md5.ComputeHash(bytes); return _BytesToHex(data); } }
public static string GenSalt() { using (RandomNumberGenerator rng = new RNGCryptoServiceProvider()) { var salt = new byte[16]; rng.GetBytes(salt); return _BytesToHex(salt); } } }
}</lang> Class for creating and authenticating users. <lang csharp>using MySql.Data.MySqlClient;
namespace rosettaMySQL {
class UserOperator { private MySqlConnection _connection;
public UserOperator(MySqlConnection connection) { _connection = connection; }
public bool CreateUser(string username, string password) { try { var salt = Hasher.GenSalt(); var hash = Hasher.Hash(salt, password); var sql = $"INSERT INTO users " + $"(username, pass_salt, pass_md5) " + $"VALUES ('{username}','{salt}','{hash}')"; using (var command = new MySqlCommand(sql, _connection)) { command.ExecuteNonQuery(); return true; } } catch (MySqlException e) { if (e.Number == 1062) //username is a duplicate { return false; } else { throw e; } } }
public bool AuthenticateUser(string username, string password) { var sql = $"SELECT userid, username, pass_salt, pass_md5 " + $"FROM users " + $"WHERE username='{username}';";
using (var command = new MySqlCommand(sql, _connection)) using (var reader = command.ExecuteReader()) { if (reader.HasRows) { reader.Read(); var salt = reader.GetString("pass_salt"); var hash = reader.GetString("pass_md5"); return (Hasher.Hash(salt, password) == hash); } else { return false; } } } }
}</lang> Class with main method and database connection method. <lang csharp>using System; using MySql.Data.MySqlClient;
namespace rosettaMySQL {
class Program { public static MySqlConnection ConnectDB(string server, int port, string db, string username, string password) { var connectStr = $"server={server};" + $"user={username};" + $"database={db};" + $"port={port};" + $"password={password}"; return new MySqlConnection(connectStr); }
static void Main(string[] args) { try { var connection = ConnectDB("localhost", 3306, "test", "root", "password"); connection.Open(); var userOperator = new UserOperator(connection); Console.WriteLine("Bob has been created: " + userOperator.CreateUser("Bob", "123456")); Console.WriteLine("Bob has been duplicated: " + userOperator.CreateUser("Bob", "123456")); Console.WriteLine("Wrong password works: " + userOperator.AuthenticateUser("BOB", "notpassword")); Console.WriteLine("Right password works: " + userOperator.AuthenticateUser("BOB", "123456")); } catch(MySqlException e) { switch(e.Number) { case 0: Console.WriteLine("Cannot connect to server"); break; case 1045: Console.WriteLine("Invalid database username/password"); break; default: Console.WriteLine(e.ToString()); Console.WriteLine(e.Number); break; } } catch (Exception e) { Console.WriteLine(e.ToString()); } } }
}</lang> Output
Bob has been created: True Bob has been duplicated: False Wrong password works: False Right password works: True
Go
<lang go>package main
import (
"bytes" "crypto/md5" "crypto/rand" "database/sql" "fmt"
_ "github.com/go-sql-driver/mysql"
)
func connectDB() (*sql.DB, error) {
return sql.Open("mysql", "rosetta:code@/rc")
}
func createUser(db *sql.DB, user, pwd string) error {
salt := make([]byte, 16) rand.Reader.Read(salt) _, err := db.Exec(`insert into users (username, pass_salt, pass_md5) values (?, ?, ?)`, user, salt, saltHash(salt, pwd)) if err != nil { return fmt.Errorf("User %s already exits", user) } return nil
}
func authenticateUser(db *sql.DB, user, pwd string) error {
var salt, hash []byte row := db.QueryRow(`select pass_salt, pass_md5 from users where username=?`, user) if err := row.Scan(&salt, &hash); err != nil { return fmt.Errorf("User %s unknown", user) } if !bytes.Equal(saltHash(salt, pwd), hash) { return fmt.Errorf("User %s invalid password", user) } return nil
}
func saltHash(salt []byte, pwd string) []byte {
h := md5.New() h.Write(salt) h.Write([]byte(pwd)) return h.Sum(nil)
}
func main() {
// demonstrate db, err := connectDB() defer db.Close() createUser(db, "sam", "123") err = authenticateUser(db, "sam", "123") if err == nil { fmt.Println("User sam authenticated") }
// extra fmt.Println() // show contents of database rows, _ := db.Query(`select username, pass_salt, pass_md5 from users`) var user string var salt, hash []byte for rows.Next() { rows.Scan(&user, &salt, &hash) fmt.Printf("%s %x %x\n", user, salt, hash) } // try creating same user again err = createUser(db, "sam", "123") fmt.Println(err) // try authenticating unknown user err = authenticateUser(db, "pam", "123") fmt.Println(err) // try wrong password err = authenticateUser(db, "sam", "1234") fmt.Println(err) // clear table to run program again db.Exec(`truncate table users`)
}</lang>
- Output:
User sam authenticated sam d5d1ef775a89f2b504dc808e66087f3d a939f77466e91527e748377cc14dfd66 User sam already exits User pam unknown User sam invalid password
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>
Julia
<lang julia> using MySQL using Nettle # for md5
function connect_db(uri, user, pw, dbname)
mydb = mysql_connect(uri, user, pw, dbname)
const command = """CREATE TABLE IF NOT EXISTS 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 );""" mysql_execute(mydb, command) mydb
end
function create_user(dbh, user, pw)
mysql_stmt_prepare(dbh, "INSERT IGNORE INTO users (username, pass_salt, pass_md5) values (?, ?, ?);") salt = join([Char(c) for c in rand(UInt8, 16)], "") passmd5 = digest("md5", salt * pw) mysql_execute(dbh, [MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR, MYSQL_TYPE_VARCHAR], [user, salt, passmd5])
end
function addusers(dbh, userdict)
for user in keys(userdict) create_user(dbh, user, userdict[user]) end
end
"""
authenticate_user
Note this returns true if password provided authenticates as correct, false otherwise """ function authenticate_user(dbh, username, pw)
mysql_stmt_prepare(dbh, "SELECT pass_salt, pass_md5 FROM users WHERE username = ?;") pass_salt, pass_md5 = mysql_execute(dbh, [MYSQL_TYPE_VARCHAR], [username], opformat=MYSQL_TUPLES)[1] pass_md5 == digest("md5", pass_salt * pw)
end
const users = Dict("Joan" => "joanspw", "John" => "johnspw", "Mary" => "marpw", "Mark" => "markpw") const mydb = connect_db("192.168.1.1", "julia", "julia", "mydb")
addusers(mydb, users) println("""John authenticates correctly: $(authenticate_user(mydb, "John", "johnspw")==true)""") println("""Mary does not authenticate with password of 123: $(authenticate_user(mydb, "Mary", "123")==false)""") mysql_disconnect(mydb) </lang>
Kotlin
<lang scala>// Version 1.2.41
import java.sql.Connection import java.sql.DriverManager import java.sql.ResultSet import java.security.MessageDigest import java.security.SecureRandom import java.math.BigInteger
class UserManager {
private lateinit var dbConnection: Connection
private fun md5(message: String): String { val hexString = StringBuilder() val bytes = message.toByteArray() val md = MessageDigest.getInstance("MD5") val dig = md.digest(bytes) for (i in 0 until dig.size) { val hex = (0xff and dig[i].toInt()).toString(16) if (hex.length == 1) hexString.append('0') hexString.append(hex) } return hexString.toString() }
fun connectDB(host: String, port: Int, db: String, user: String, pwd: String) { Class.forName("com.mysql.jdbc.Driver") dbConnection = DriverManager.getConnection( "jdbc:mysql://$host:$port/$db", user, pwd ) }
fun createUser(user: String, pwd: String): Boolean { val random = SecureRandom() val salt = BigInteger(130, random).toString(16) val insert = "INSERT INTO users " + "(username, pass_salt, pass_md5) " + "VALUES (?, ?, ?)" try { val pstmt = dbConnection.prepareStatement(insert) with (pstmt) { setString(1, user) setString(2, salt) setString(3, md5(salt + pwd)) val rowCount = executeUpdate() close() if (rowCount == 0) return false } return true } catch (ex: Exception) { return false } }
fun authenticateUser(user: String, pwd: String): Boolean { val select = "SELECT pass_salt, pass_md5 FROM users WHERE username = ?" lateinit var res: ResultSet try { val pstmt = dbConnection.prepareStatement(select) with (pstmt) { setString(1, user) res = executeQuery() res.next() // assuming that username is unique val passSalt = res.getString(1) val passMD5 = res.getString(2) close() return passMD5 == md5(passSalt + pwd) } } catch (ex: Exception) { return false } finally { if (!res.isClosed) res.close() } }
fun closeConnection() { if (!dbConnection.isClosed) dbConnection.close() }
}
fun main(args: Array<String>) {
val um = UserManager() with (um) { try { connectDB("localhost", 3306, "test", "root", "admin") if (createUser("johndoe", "test")) println("User created") if (authenticateUser("johndoe", "test")) { println("User authenticated") } } catch(ex: Exception) { ex.printStackTrace() } finally { closeConnection() } }
}</lang>
Mathematica
<lang Mathematica>Needs["DatabaseLink`"]; connectDb[dbUser_, dbPass_, dbUrl_] :=
OpenSQLConnection[JDBC["mysql", dbUrl], "Username" -> dbUser, "Password" -> dbPass];
createUser::nameTaken = "The username '`1`' is already taken."; createUser[dbUser_, dbPass_, dbUrl_, user_, pass_] :=
Module[{db = connectDb[dbUser, dbPass, dbUrl], salt = RandomChoice[Range[32, 127], 16]}, If[MemberQ[SQLSelect[db, "users", {"username"}], {user}], Message[createUser::nameTaken, user]; Return[]]; SQLInsert[db, "users", {"username", "pass_salt", "pass_md5"}, {user, SQLBinary[salt], SQLBinary[ IntegerDigits[Hash[FromCharacterCode[salt] <> pass, "MD5"], 256, 16]]}]; CloseSQLConnection[db];];
authenticateUser[dbUser_, dbPass_, dbUrl_, user_, pass_] :=
Module[{db = connectDb[dbUser, dbPass, dbUrl], rtn}, rtn = MemberQ[SQLSelect[db, "users", {"username"}], {user}] && Module[{data = SQLSelect[db, "users", {"username", "pass_salt", "pass_md5"}, SQLColumn["username"] == user]1}, Hash[FromCharacterCode[data2, 1] <> pass, "MD5"] == FromDigits[data3, 1, 256]]; CloseSQLConnection[db]; rtn];</lang>
Nim
Even if this program is only an example, we have chosen to generate the salt using the random number generator provided by the third-party module “nimcrypto” rather than using the PRNG from the standard module “random” which is totally inadequate for cryptographic usage.
<lang Nim>import db_mysql, nimcrypto, md5, strutils
proc connectDb(user, password: string): DbConn =
## Connect to the database "user_db" and create ## the table "users" if it doesn’t exist yet.
result = open("localhost", user, password, "user_db") result.exec(sql"""CREATE TABLE IF NOT EXISTS users ( userid INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(32) UNIQUE KEY NOT NULL, pass_salt tinyblob NOT NULL, pass_md5 tinyblob NOT NULL)""")
proc createUser(db: DbConn; username, password: string) =
## Create a new user in the table "users". ## The password salt and the password MD5 are managed as strings ## but stored in tinyblobs as required. var passSalt = newString(16) if randomBytes(passSalt) != 16: raise newException(ValueError, "unable to build a salt.") var passMd5 = newString(16) for i, b in toMD5(passSalt & password): passMd5[i] = chr(b) if db.tryExec(sql"INSERT INTO users (username, pass_salt, pass_md5) VALUES (?, ?, ?)", username, passSalt, passMd5): echo "User $1 created." % username else: echo "Could not create user $1." % username
proc authenticateUser(db: DbConn; user, password: string): bool =
## Try to authenticate the user. ## The authentication fails if the user doesn’t exist in "users" table or if the ## password doesn’t match with the salt and password MD5 retrieved from the table. let row = db.getRow(sql"SELECT pass_salt, pass_md5 FROM users WHERE username = ?", user) if row[0].len != 0: let digest = toMd5(row[0] & password) for i in 0..15: if digest[i] != byte(row[1][i]): return result = true
proc clean(db: DbConn) =
## Remove all users from "users" table. db.exec(sql"DELETE FROM user_db.users")
when isMainModule:
proc authResult(status: bool): string = if status: "Succeeded" else: "Failed"
# Connect to database and create user "Alice". let db = connectDb("admin", "admin_password") db.createUser("Alice", "Alice_password")
# Try to authenticate Alice... # ... with a wrong password... var result = db.authenticateUser("Alice", "another_password").authResult() echo result, " to authenticate Alice with a wrong password." # ... then with the right password. result = db.authenticateUser("Alice", "Alice_password").authResult() echo result, " to authenticate Alice with the right password."
# Clean-up and close. db.clean() db.close()</lang>
- Output:
User Alice created. Failed to authenticate Alice with a wrong password. Succeeded to authenticate Alice with the right password.
Objeck
<lang objeck>use ODBC; use Encryption;
class SqlTest {
@conn : Connection; function : Main(args : String[]) ~ Nil { SqlTest->New()->Run(); }
New() { @conn := Connection->New("test", "root", "helloworld"); } method : Run() ~ Nil { CreateUser("objeck", "beer"); AuthenticateUser("objeck", "beer"); leaving { @conn->Close(); }; }
method : AuthenticateUser(username : String, password : String) ~ Nil { status := false; ps : ParameterStatement; result : ResultSet; if(@conn->IsOpen()) { sql := "SELECT pass_salt, pass_md5 FROM users WHERE username = ?"; ps := @conn->CreateParameterStatement(sql); ps->SetVarchar(1, username); result := ps->Select(); if(result <> Nil & result->Next()) { salt_buffer := Byte->New[16]; result->GetBlob(1, salt_buffer); salt := ""; for(i := 0; i < 16; i+=1;) { salt->Append(salt_buffer[i]); };
db_password_buffer := Byte->New[16]; result->GetBlob(2, db_password_buffer);
password->Append(salt); user_password_buffer := Hash->MD5(password->ToByteArray()); IO.Console->Print("user: authenticated=")->PrintLine(IsEqual(db_password_buffer, user_password_buffer)); }; }; leaving { if(ps <> Nil) { ps->Close(); }; if(ps <> Nil) { ps->Close(); }; }; } method : CreateUser(username : String, password : String) ~ Nil { salt := ""; for(i := 0; i < 16; i+=1;) { salt->Append((Float->Random() * 100)->As(Int)); }; salt := salt->SubString(16); password->Append(salt); md5_password := Hash->MD5(password->ToByteArray());
ps : ParameterStatement; if(@conn->IsOpen()) { sql := "INSERT INTO users(username, pass_salt, pass_md5) VALUES (?, ?, ?)"; ps := @conn->CreateParameterStatement(sql); ps->SetVarchar(1, username); ps->SetBytes(2, salt->ToByteArray()); ps->SetBytes(3, md5_password); IO.Console->Print("adding user: username=")->Print(username) ->Print(", salt=")->Print(salt) ->Print(", status=")->PrintLine(ps->Update()); }; leaving { if(ps <> Nil) { ps->Close(); }; }; } method : IsEqual(left : Byte[], right : Byte[]) ~ Bool { if(left->Size() <> right->Size()) { return false; }; each(i : left) { if(left[i] <> right[i]) { return false; }; }; return true; }
}</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>
Phix
Uses SQLite, there is a MySQL wrapper in PCAN that I have not personally tried yet.
Uses code from MD5, now modified to be an executable library.
<lang Phix>-- demo/rosetta/SQL-based_authentication.exw
include pSQLite.e
include md5.exw
sqlite3_stmt pAddUser = NULL
procedure add_user(sqlite3 db, string name, pw)
if pAddUser=NULL then pAddUser = sqlite3_prepare(db,"INSERT INTO users (username,pass_salt,pass_md5) VALUES(:name, :salt, :md5);") end if string salt = sq_rand(repeat(#FF,16)), md5s = md5(salt&pw) sqlite3_bind_text(pAddUser,":name", name) sqlite3_bind_text(pAddUser,":salt", salt) sqlite3_bind_text(pAddUser,":md5", md5s) {} = sqlite3_step(pAddUser) -- (nb: ignores any errors.) sqlite3_reset(pAddUser)
end procedure
sqlite3_stmt pAuthUser = NULL
function authenticate_user(sqlite3 db, string name, pw)
if pAuthUser=NULL then pAuthUser = sqlite3_prepare(db,"SELECT pass_salt, pass_md5 FROM users WHERE username = :name;") end if sqlite3_bind_text(pAuthUser,":name", name) integer res = sqlite3_step(pAuthUser) if res!=SQLITE_ROW then res = false -- (no such user) else string salt = sqlite3_column_text(pAuthUser,1) string pass_md5 = sqlite3_column_text(pAuthUser,2) res = (pass_md5==md5(salt&pw)) end if sqlite3_reset(pAuthUser) return res
end function
constant create_cmd = """ CREATE TABLE IF NOT EXISTS users(
userid INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR(32) UNIQUE NOT NULL, pass_salt tinyblob, -- a string of 16 random bytes pass_md5 tinyblob); -- binary MD5 hash of pass_salt concatenated with the password
"""
procedure main()
sequence sqlversion = sqlite3_libversion(true) if sqlversion<{3,3,0} then crash("a newer sqlite.dll/so is required (for IF NOT EXISTS)") end if sqlite3 db = sqlite3_open("users.sqlite") integer res = sqlite3_exec(db,create_cmd) if res!=SQLITE_OK then ?9/0 end if
sqlite3_set_fatal_id(SQLITE3_NON_FATAL) -- (else trying to re-add user crashes) add_user(db,"user","password") printf(1,"user with correct password:%t\n",authenticate_user(db, "user", "password")) printf(1,"user with incorrect password:%t\n",authenticate_user(db, "user", "wrong"))
if pAddUser!=NULL then if sqlite3_finalize(pAddUser)!=SQLITE_OK then ?9/0 end if end if if pAuthUser!=NULL then if sqlite3_finalize(pAuthUser)!=SQLITE_OK then ?9/0 end if end if sqlite3_close(db) ?"done" {} = wait_key()
end procedure main()</lang>
- Output:
user with correct password:true user with incorrect password:false
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>
Racket
<lang racket>#lang racket (require db file/md5) (define-logger authentication) (current-logger authentication-logger)
(define DB-HOST "localhost") (define DB-USER "devel") (define DB-PASS "devel") (define DB-NAME "test")
(define (connect-db)
(mysql-connect #:user DB-USER #:database DB-NAME #:password DB-PASS))
(define (salt+password->hash salt password #:hex-encode? (hex-encode? #f))
(md5 (bytes-append salt password) hex-encode?))
(define (report-sql-error e)
(log-authentication-error "Failed to create user:~s" (exn-message e)) #f)
(define (create-user db username passwd)
; if user was successfully created, returns its ID else #f (define salt (list->bytes (for/list ((i (in-range 16))) (random 256)))) (define hash (salt+password->hash salt passwd)) (with-handlers ((exn:fail:sql? report-sql-error)) (query db "INSERT INTO users (username, pass_salt, pass_md5) VALUES (?, ?, ?)" username salt hash)))
(define (authenticate-user db username password)
(or (match (query-maybe-row db "SELECT pass_salt, pass_md5 FROM users WHERE username = ?" username) [#f #f] [(vector salt hash) (bytes=? hash (salt+password->hash salt password))]) ; don't let the deviants know whether it's the username or password that's dodgy (error "the username, password combination does not exist in system")))
(module+ test
(require rackunit) (define test-DB (connect-db)) ; typically, you only do this the once (or risk upsetting your users bigtime!) ; call this just the once! (define (create-users-table db) (query-exec db "DROP TABLE IF EXISTS users") (query-exec db #<<EOS
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
); EOS
)) (create-users-table test-DB) (create-user test-DB #"tim" #"shh! it's a secret!") ; ensure the user exists (for testing purposes) (check-match (query-list test-DB "SELECT userid FROM users WHERE username = 'tim'") (list _)) ; (ah... but tim exists!!!) (check-false (create-user test-DB #"tim" #"tim's password")) (check-exn exn:fail? (λ () (authenticate-user test-DB #"tim" #"password"))) (check-true (authenticate-user test-DB #"tim" #"shh! it's a secret!")))</lang>
Raku
(formerly Perl 6)
<lang perl6> use v6; use DBIish;
multi connect_db(:$dbname, :$host, :$user, :$pass) {
my $db = DBIish.connect("mysql",host => $host, database =>$dbname, user =>$user, password =>$pass, :RaiseError) or die "ERROR: {DBIish.errstr}."; $db;
}
multi create_user(:$db, :$user, :$pass) {
#https://stackoverflow.com/questions/53365101/converting-pack-to-perl6 my $salt = Buf.new((^256).roll(16)); my $sth = $db.prepare(q:to/STATEMENT/); INSERT IGNORE INTO users (username, pass_salt, pass_md5) VALUES (?, ?, unhex(md5(concat(pass_salt, ?)))) STATEMENT $sth.execute($user,$salt,$pass); $sth.insert-id or Any;
}
multi authenticate_user (:$db, :$user, :$pass) {
my $sth = $db.prepare(q:to/STATEMENT/); SELECT userid FROM users WHERE username=? AND pass_md5=unhex(md5(concat(pass_salt, ?))) STATEMENT $sth.execute($user,$pass); my $userid = $sth.fetch; $userid[0] or Any;
}</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>
Ruby
Uses the mysql2 gem <lang ruby>require 'mysql2' require 'securerandom' require 'digest'
def connect_db(host, port = nil, username, password, db)
Mysql2::Client.new( host: host, port: port, username: username, password: password, database: db )
end
def create_user(client, username, password)
salt = SecureRandom.random_bytes(16) password_md5 = Digest::MD5.hexdigest(salt + password)
statement = client.prepare('INSERT INTO users (username, pass_salt, pass_md5) VALUES (?, ?, ?)') statement.execute(username, salt, password_md5) statement.last_id
end
def authenticate_user(client, username, password)
user_record = client.prepare("SELECT SELECT pass_salt, pass_md5 FROM users WHERE username = '#{client.escape(username)}'").first return false unless user_record
password_md5 = Digest::MD5.hexdigest(user_record['pass_salt'] + password) password_md5 == user_record['pass_md5']
end</lang>
Sidef
<lang ruby>require('DBI')
# returns a database handle configured to throw an exception on query errors
func connect_db(dbname, host, user, pass) {
var db = %s<DBI>.connect("dbi:mysql:#{dbname}:#{host}", user, pass) db || die (global DBI::errstr) db{:RaiseError} = 1 db
}
# if the user was successfully created, returns its user id. # if the name was already in use, returns nil.
func create_user(db, user, pass) {
var salt = "C*".pack(16.of { 256.irand }...) db.do( "INSERT IGNORE INTO users (username, pass_salt, pass_md5) VALUES (?, ?, unhex(md5(concat(pass_salt, ?))))", nil, user, salt, pass ) ? db{:mysql_insertid} : nil
}
# if the user is authentic, returns its user id. otherwise returns nil.
func authenticate_user(db, user, pass) {
db.selectrow_array("SELECT userid FROM users WHERE username=? AND pass_md5=unhex(md5(concat(pass_salt, ?)))", nil, user, pass )
}</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>
- Programming Tasks
- Database operations
- C
- Mysqlclient
- OpenSSL
- C sharp
- Go
- Java
- Julia
- Kotlin
- Mathematica
- DatabaseLink`
- Nim
- Nimcrypto
- Objeck
- Perl
- DBI
- Phix
- PHP
- Python
- Racket
- Raku
- Raven
- Ruby
- Sidef
- Tcl
- Batch File/Omit
- Brainf***/Omit
- Lotus 123 Macro Scripting/Omit
- M4/Omit
- Maxima/Omit
- ML/I/Omit
- PARI/GP/Omit
- PostScript/Omit
- Retro/Omit
- SNOBOL4/Omit
- SQL/Omit
- SQL PL/Omit
- TI-83 BASIC/Omit
- TI-89 BASIC/Omit
- Unlambda/Omit
- Yorick/Omit
- ZX Spectrum Basic/Omit