SQL-based authentication: Difference between revisions
m
→{{header|Wren}}: Changed to Wren S/H
(Added racket implementation) |
m (→{{header|Wren}}: Changed to Wren S/H) |
||
| (29 intermediate revisions by 16 users not shown) | |||
Line 7:
This is the table definition:
<
userid int primary key auto_increment,
username varchar(32) unique key not null,
Line 14:
pass_md5 tinyblob not null
-- binary MD5 hash of pass_salt concatenated with the password
);</
(<tt>pass_salt</tt> and <tt>pass_md5</tt> would be <tt>binary(16)</tt> values, but MySQL versions before 5.0.15 strip trailing spaces when selecting them.)
Line 20:
{{libheader|mysqlclient}} (MySQL client library)
{{libheader|OpenSSL}} (for MD5)
<
#include <stdlib.h>
#include <string.h>
Line 193:
}
return EXIT_SUCCESS;
}</
From the command line, <tt>program add user password</tt> to add users, and <tt>program auth user password</tt> to see if the user with that password is authorized or not.
=={{header|C sharp|C#}}==
Class for hashing and random salt generation.
<syntaxhighlight 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);
}
}
}
}</syntaxhighlight>
Class for creating and authenticating users.
<syntaxhighlight 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;
}
}
}
}
}</syntaxhighlight>
Class with main method and database connection method.
<syntaxhighlight 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());
}
}
}
}</syntaxhighlight>
Output
<pre>Bob has been created: True
Bob has been duplicated: False
Wrong password works: False
Right password works: True</pre>
=={{header|Go}}==
<syntaxhighlight 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`)
}</syntaxhighlight>
{{out}}
<pre>
User sam authenticated
sam d5d1ef775a89f2b504dc808e66087f3d a939f77466e91527e748377cc14dfd66
User sam already exits
User pam unknown
User sam invalid password
</pre>
=={{header|Java}}==
{{works with|Java|1.7}}
<
import java.sql.Connection;
import java.sql.DriverManager;
Line 340 ⟶ 596:
}
}
}</
=={{header|Julia}}==
<syntaxhighlight 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)
</syntaxhighlight>
=={{header|Kotlin}}==
{{trans|Java}}
<syntaxhighlight 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()
}
}
}</syntaxhighlight>
=={{header|Mathematica}}/{{header|Wolfram Language}}==
{{libheader|DatabaseLink`}}
<syntaxhighlight 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[data[[2, 1]]] <> pass, "MD5"] ==
FromDigits[data[[3, 1]], 256]]; CloseSQLConnection[db]; rtn];</syntaxhighlight>
=={{header|Nim}}==
{{libheader|nimcrypto}}
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.
<syntaxhighlight 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()</syntaxhighlight>
{{out}}
<pre>User Alice created.
Failed to authenticate Alice with a wrong password.
Succeeded to authenticate Alice with the right password.</pre>
=={{header|Objeck}}==
<syntaxhighlight 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;
}
}</syntaxhighlight>
=={{header|Perl}}==
Line 346 ⟶ 967:
{{libheader|DBI}}<br>
{{libheader|DBD::mysql}}
<
# returns a database handle configured to throw an exception on query errors
Line 375 ⟶ 996:
undef, $user, $pass);
$userid
}</
=={{header|Phix}}==
Uses SQLite, there is a MySQL wrapper in [http://phix.x10.mx/pmwiki/pmwiki.php PCAN] that I have not personally tried yet.<br>
Uses code from [[MD5/Implementation#Phix|MD5]], now modified to be an executable library.
<!--<syntaxhighlight lang="phix">(notonline)-->
<span style="color: #000080;font-style:italic;">-- demo\rosetta\SQL-based_authentication.exw</span>
<span style="color: #008080;">without</span> <span style="color: #008080;">js</span> <span style="color: #000080;font-style:italic;">-- (file i/o)</span>
<span style="color: #008080;">include</span> <span style="color: #000000;">pSQLite</span><span style="color: #0000FF;">.</span><span style="color: #000000;">e</span>
<span style="color: #008080;">include</span> <span style="color: #000000;">md5</span><span style="color: #0000FF;">.</span><span style="color: #000000;">exw</span>
<span style="color: #000000;">sqlite3_stmt</span> <span style="color: #000000;">pAddUser</span> <span style="color: #0000FF;">=</span> <span style="color: #004600;">NULL</span>
<span style="color: #008080;">procedure</span> <span style="color: #000000;">add_user</span><span style="color: #0000FF;">(</span><span style="color: #000000;">sqlite3</span> <span style="color: #000000;">db</span><span style="color: #0000FF;">,</span> <span style="color: #004080;">string</span> <span style="color: #000000;">name</span><span style="color: #0000FF;">,</span> <span style="color: #000000;">pw</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">if</span> <span style="color: #000000;">pAddUser</span><span style="color: #0000FF;">=</span><span style="color: #004600;">NULL</span> <span style="color: #008080;">then</span>
<span style="color: #000000;">pAddUser</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_prepare</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"INSERT INTO users (username,pass_salt,pass_md5) VALUES(:name, :salt, :md5);"</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
<span style="color: #004080;">string</span> <span style="color: #000000;">salt</span> <span style="color: #0000FF;">=</span> <span style="color: #7060A8;">sq_rand</span><span style="color: #0000FF;">(</span><span style="color: #7060A8;">repeat</span><span style="color: #0000FF;">(</span><span style="color: #000000;">#FF</span><span style="color: #0000FF;">,</span><span style="color: #000000;">16</span><span style="color: #0000FF;">)),</span>
<span style="color: #000000;">md5s</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">md5</span><span style="color: #0000FF;">(</span><span style="color: #000000;">salt</span><span style="color: #0000FF;">&</span><span style="color: #000000;">pw</span><span style="color: #0000FF;">)</span>
<span style="color: #000000;">sqlite3_bind_text</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAddUser</span><span style="color: #0000FF;">,</span><span style="color: #008000;">":name"</span><span style="color: #0000FF;">,</span> <span style="color: #000000;">name</span><span style="color: #0000FF;">)</span>
<span style="color: #000000;">sqlite3_bind_text</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAddUser</span><span style="color: #0000FF;">,</span><span style="color: #008000;">":salt"</span><span style="color: #0000FF;">,</span> <span style="color: #000000;">salt</span><span style="color: #0000FF;">)</span>
<span style="color: #000000;">sqlite3_bind_text</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAddUser</span><span style="color: #0000FF;">,</span><span style="color: #008000;">":md5"</span><span style="color: #0000FF;">,</span> <span style="color: #000000;">md5s</span><span style="color: #0000FF;">)</span>
<span style="color: #0000FF;">{}</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_step</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAddUser</span><span style="color: #0000FF;">)</span> <span style="color: #000080;font-style:italic;">-- (nb: ignores any errors.)</span>
<span style="color: #000000;">sqlite3_reset</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAddUser</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">procedure</span>
<span style="color: #000000;">sqlite3_stmt</span> <span style="color: #000000;">pAuthUser</span> <span style="color: #0000FF;">=</span> <span style="color: #004600;">NULL</span>
<span style="color: #008080;">function</span> <span style="color: #000000;">authenticate_user</span><span style="color: #0000FF;">(</span><span style="color: #000000;">sqlite3</span> <span style="color: #000000;">db</span><span style="color: #0000FF;">,</span> <span style="color: #004080;">string</span> <span style="color: #000000;">name</span><span style="color: #0000FF;">,</span> <span style="color: #000000;">pw</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">if</span> <span style="color: #000000;">pAuthUser</span><span style="color: #0000FF;">=</span><span style="color: #004600;">NULL</span> <span style="color: #008080;">then</span>
<span style="color: #000000;">pAuthUser</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_prepare</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"SELECT pass_salt, pass_md5 FROM users WHERE username = :name;"</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
<span style="color: #000000;">sqlite3_bind_text</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAuthUser</span><span style="color: #0000FF;">,</span><span style="color: #008000;">":name"</span><span style="color: #0000FF;">,</span> <span style="color: #000000;">name</span><span style="color: #0000FF;">)</span>
<span style="color: #004080;">integer</span> <span style="color: #000000;">res</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_step</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAuthUser</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">if</span> <span style="color: #000000;">res</span><span style="color: #0000FF;">!=</span><span style="color: #000000;">SQLITE_ROW</span> <span style="color: #008080;">then</span>
<span style="color: #000000;">res</span> <span style="color: #0000FF;">=</span> <span style="color: #004600;">false</span> <span style="color: #000080;font-style:italic;">-- (no such user)</span>
<span style="color: #008080;">else</span>
<span style="color: #004080;">string</span> <span style="color: #000000;">salt</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_column_text</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAuthUser</span><span style="color: #0000FF;">,</span><span style="color: #000000;">1</span><span style="color: #0000FF;">)</span>
<span style="color: #004080;">string</span> <span style="color: #000000;">pass_md5</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_column_text</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAuthUser</span><span style="color: #0000FF;">,</span><span style="color: #000000;">2</span><span style="color: #0000FF;">)</span>
<span style="color: #000000;">res</span> <span style="color: #0000FF;">=</span> <span style="color: #0000FF;">(</span><span style="color: #000000;">pass_md5</span><span style="color: #0000FF;">==</span><span style="color: #000000;">md5</span><span style="color: #0000FF;">(</span><span style="color: #000000;">salt</span><span style="color: #0000FF;">&</span><span style="color: #000000;">pw</span><span style="color: #0000FF;">))</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
<span style="color: #000000;">sqlite3_reset</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAuthUser</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">return</span> <span style="color: #000000;">res</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">function</span>
<span style="color: #008080;">constant</span> <span style="color: #000000;">create_cmd</span> <span style="color: #0000FF;">=</span> <span style="color: #008000;">"""
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
"""</span>
<span style="color: #008080;">procedure</span> <span style="color: #000000;">main</span><span style="color: #0000FF;">()</span>
<span style="color: #004080;">sequence</span> <span style="color: #000000;">sqlversion</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_libversion</span><span style="color: #0000FF;">(</span><span style="color: #004600;">true</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">if</span> <span style="color: #000000;">sqlversion</span><span style="color: #0000FF;"><{</span><span style="color: #000000;">3</span><span style="color: #0000FF;">,</span><span style="color: #000000;">3</span><span style="color: #0000FF;">,</span><span style="color: #000000;">0</span><span style="color: #0000FF;">}</span> <span style="color: #008080;">then</span>
<span style="color: #7060A8;">crash</span><span style="color: #0000FF;">(</span><span style="color: #008000;">"a newer sqlite.dll/so is required (for IF NOT EXISTS)"</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
<span style="color: #000000;">sqlite3</span> <span style="color: #000000;">db</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_open</span><span style="color: #0000FF;">(</span><span style="color: #008000;">"users.sqlite"</span><span style="color: #0000FF;">)</span>
<span style="color: #004080;">integer</span> <span style="color: #000000;">res</span> <span style="color: #0000FF;">=</span> <span style="color: #000000;">sqlite3_exec</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span><span style="color: #000000;">create_cmd</span><span style="color: #0000FF;">)</span>
<span style="color: #008080;">if</span> <span style="color: #000000;">res</span><span style="color: #0000FF;">!=</span><span style="color: #000000;">SQLITE_OK</span> <span style="color: #008080;">then</span> <span style="color: #0000FF;">?</span><span style="color: #000000;">9</span><span style="color: #0000FF;">/</span><span style="color: #000000;">0</span> <span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
<span style="color: #000000;">sqlite3_set_fatal_id</span><span style="color: #0000FF;">(</span><span style="color: #000000;">SQLITE3_NON_FATAL</span><span style="color: #0000FF;">)</span> <span style="color: #000080;font-style:italic;">-- (else trying to re-add user crashes)</span>
<span style="color: #000000;">add_user</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"user"</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"password"</span><span style="color: #0000FF;">)</span>
<span style="color: #7060A8;">printf</span><span style="color: #0000FF;">(</span><span style="color: #000000;">1</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"user with correct password:%t\n"</span><span style="color: #0000FF;">,</span><span style="color: #000000;">authenticate_user</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span> <span style="color: #008000;">"user"</span><span style="color: #0000FF;">,</span> <span style="color: #008000;">"password"</span><span style="color: #0000FF;">))</span>
<span style="color: #7060A8;">printf</span><span style="color: #0000FF;">(</span><span style="color: #000000;">1</span><span style="color: #0000FF;">,</span><span style="color: #008000;">"user with incorrect password:%t\n"</span><span style="color: #0000FF;">,</span><span style="color: #000000;">authenticate_user</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">,</span> <span style="color: #008000;">"user"</span><span style="color: #0000FF;">,</span> <span style="color: #008000;">"wrong"</span><span style="color: #0000FF;">))</span>
<span style="color: #008080;">if</span> <span style="color: #000000;">pAddUser</span><span style="color: #0000FF;">!=</span><span style="color: #004600;">NULL</span> <span style="color: #008080;">then</span>
<span style="color: #008080;">if</span> <span style="color: #000000;">sqlite3_finalize</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAddUser</span><span style="color: #0000FF;">)!=</span><span style="color: #000000;">SQLITE_OK</span> <span style="color: #008080;">then</span> <span style="color: #0000FF;">?</span><span style="color: #000000;">9</span><span style="color: #0000FF;">/</span><span style="color: #000000;">0</span> <span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
<span style="color: #008080;">if</span> <span style="color: #000000;">pAuthUser</span><span style="color: #0000FF;">!=</span><span style="color: #004600;">NULL</span> <span style="color: #008080;">then</span>
<span style="color: #008080;">if</span> <span style="color: #000000;">sqlite3_finalize</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAuthUser</span><span style="color: #0000FF;">)!=</span><span style="color: #000000;">SQLITE_OK</span> <span style="color: #008080;">then</span> <span style="color: #0000FF;">?</span><span style="color: #000000;">9</span><span style="color: #0000FF;">/</span><span style="color: #000000;">0</span> <span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
<span style="color: #000000;">sqlite3_close</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">)</span>
<span style="color: #0000FF;">?</span><span style="color: #008000;">"done"</span>
<span style="color: #0000FF;">{}</span> <span style="color: #0000FF;">=</span> <span style="color: #7060A8;">wait_key</span><span style="color: #0000FF;">()</span>
<span style="color: #008080;">end</span> <span style="color: #008080;">procedure</span>
<span style="color: #000000;">main</span><span style="color: #0000FF;">()</span>
<!--</syntaxhighlight>-->
{{out}}
<pre>
user with correct password:true
user with incorrect password:false
</pre>
=={{header|PHP}}==
To use MySQL in PHP you need the php_mysql module installed
<
function connect_db($database, $db_user, $db_password, $host = 'localhost', $port = NULL, $die = false) {
// Returns a MySQL link identifier (handle) on success
Line 449 ⟶ 1,155:
return $row['userid'];
}
</syntaxhighlight>
=={{header|Python}}==
{{works with|Python|2.7}}
Uses the [http://dev.mysql.com/downloads/connector/python/ official Python MySQL connector]
<
import hashlib
Line 536 ⟶ 1,242:
else:
print 'User %s failed' % user
</syntaxhighlight>
=={{header|Racket}}==
<
(require db file/md5)
(define-logger authentication)
Line 571 ⟶ 1,277:
username salt hash)))
(define (authenticate-user db username password)
(or
Line 605 ⟶ 1,310:
(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!")))</
=={{header|Raku}}==
(formerly Perl 6)
{{trans|Perl}}
<syntaxhighlight lang="raku" line>
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;
}</syntaxhighlight>
=={{header|Raven}}==
MySQL connectivity is available out of the box. Below, we do not ever send the plain text password over the wire.
<
'abcdefghijklmnopqrstuvwxyz0123456789' as $salt_chars
Line 640 ⟶ 1,379:
'foo' 'bar' authenticate_user !if "could not authenticate user\n" print bye
"user successfully created and authenticated!\n" print</
=={{header|Ruby}}==
Uses the [https://github.com/brianmario/mysql2 mysql2 gem]
<syntaxhighlight 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</syntaxhighlight>
=={{header|Sidef}}==
{{trans|Perl}}
<syntaxhighlight 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
)
}</syntaxhighlight>
=={{header|Tcl}}==
{{works with|Tcl|8.6}}
Also requires the TDBC driver for MySQL.
<
proc connect_db {handleName dbname host user pass} {
Line 680 ⟶ 1,482:
# Only get here if no rows selected
error "authentication failed for user \"$user\""
}</
=={{header|Wren}}==
{{trans|Phix}}
{{libheader|Wren-sql}}
{{libheader|Wren-crypto}}
As we don't have a MySQL wrapper (and it's hardly worth the effort of writing one specifically for this task), I've used our SQLite wrapper instead. SQLite can emulate SQL types used in other dialects even though it's native type system is quite simple.
Not an exact 'phiximile' but follows the same lines.
<syntaxhighlight lang="wren">import "./sql" for Sql, Connection
import "./crypto" for Md5
var addUser = Fn.new { |db, name, pw|
var sql = "INSERT OR IGNORE INTO users (username,pass_salt,pass_md5) VALUES(?, ?, ?)"
var stmt = db.prepare(sql)
var salt = Sql.randomness(16)
var md5s = Md5.digest(salt + pw)
stmt.bindText(1, name)
stmt.bindText(2, salt)
stmt.bindText(3, md5s)
stmt.step()
}
var authenticateUser = Fn.new { |db, name, pw|
var sql = "SELECT pass_salt, pass_md5 FROM users WHERE username = ?"
var stmt = db.prepare(sql)
stmt.bindText(1, name)
var res = stmt.step()
if (res != Sql.row) {
res = false // no such user
} else {
var salt = stmt.columnText(0)
var passMd5 = stmt.columnText(1)
res = passMd5 == Md5.digest(salt + pw)
}
return res
}
var createSql = """
DROP TABLE IF EXISTS users;
CREATE TABLE users(
userid INTEGER PRIMARY KEY AUTOINCREMENT,
username VARCHAR(32) UNIQUE NOT NULL,
pass_salt tinyblob NOT NULL,
pass_md5 tinyblob NOT NULL);
"""
var db = Connection.open("users.db")
var res = db.exec(createSql)
if (res != Sql.ok) Fiber.abort("Error creating users table.")
addUser.call(db, "user", "password")
System.print("User with correct password: %(authenticateUser.call(db, "user", "password"))")
System.print("User with incorrect password: %(authenticateUser.call(db, "user", "wrong"))")</syntaxhighlight>
{{out}}
<pre>
User with correct password: true
User with incorrect password: false
</pre>
{{omit from|Batch File|Does not have network access.}}
Line 689 ⟶ 1,548:
{{omit from|ML/I}}
{{omit from|PARI/GP}}
{{omit from
{{omit from|Retro|We have support for sqlite, but not MySQL}}
{{omit from|SNOBOL4}}
{{omit from|SQL|except maybe MySQL where this becomes trivial?}}
{{omit from|SQL PL|Db2 is a database}} <!-- However, Db2 can connect to other databases via federation or with an external stored procedure written in Java or C. -->
{{omit from|TI-83 BASIC}}
{{omit from|TI-89 BASIC}}
{{omit from|Unlambda|Does not have network access.}}
{{omit from|Yorick|Does not have network access.}}
| |||