SQL-based authentication: Difference between revisions
m
→{{header|Wren}}: Changed to Wren S/H
m (→{{header|Wren}}: Changed to Wren S/H) |
|||
(4 intermediate revisions by 4 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.
Line 199:
=={{header|C sharp|C#}}==
Class for hashing and random salt generation.
<
using System.Text;
Line 236:
}
}
}</
Class for creating and authenticating users.
<
namespace rosettaMySQL
Line 302:
}
}
}</
Class with main method and database connection method.
<
using MySql.Data.MySqlClient;
Line 356:
}
}
}</
Output
<pre>Bob has been created: True
Line 364:
=={{header|Go}}==
<
import (
Line 442:
// clear table to run program again
db.Exec(`truncate table users`)
}</
{{out}}
<pre>
Line 455:
=={{header|Java}}==
{{works with|Java|1.7}}
<
import java.sql.Connection;
import java.sql.DriverManager;
Line 596:
}
}
}</
=={{header|Julia}}==
<
using MySQL
using Nettle # for md5
Line 648:
println("""Mary does not authenticate with password of 123: $(authenticate_user(mydb, "Mary", "123")==false)""")
mysql_disconnect(mydb)
</syntaxhighlight>
=={{header|Kotlin}}==
{{trans|Java}}
<
import java.sql.Connection
Line 752:
}
}
}</
=={{header|Mathematica}}/{{header|Wolfram Language}}==
{{libheader|DatabaseLink`}}
<
connectDb[dbUser_, dbPass_, dbUrl_] :=
OpenSQLConnection[JDBC["mysql", dbUrl], "Username" -> dbUser,
Line 779:
SQLColumn["username"] == user][[1]]},
Hash[FromCharacterCode[data[[2, 1]]] <> pass, "MD5"] ==
FromDigits[data[[3, 1]], 256]]; CloseSQLConnection[db]; rtn];</
=={{header|Nim}}==
Line 785:
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.
<
proc connectDb(user, password: string): DbConn =
Line 850:
# Clean-up and close.
db.clean()
db.close()</
{{out}}
Line 858:
=={{header|Objeck}}==
<
use Encryption;
Line 961:
return true;
}
}</
=={{header|Perl}}==
Line 967:
{{libheader|DBI}}<br>
{{libheader|DBD::mysql}}
<
# returns a database handle configured to throw an exception on query errors
Line 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
--
"""</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>
Line 1,082 ⟶ 1,085:
=={{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 1,152 ⟶ 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 1,239 ⟶ 1,242:
else:
print 'User %s failed' % user
</syntaxhighlight>
=={{header|Racket}}==
<
(require db file/md5)
(define-logger authentication)
Line 1,307 ⟶ 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;
Line 1,341 ⟶ 1,344:
my $userid = $sth.fetch;
$userid[0] or Any;
}</
=={{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 1,376 ⟶ 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]
<
require 'securerandom'
require 'digest'
Line 1,409 ⟶ 1,412:
password_md5 = Digest::MD5.hexdigest(user_record['pass_salt'] + password)
password_md5 == user_record['pass_md5']
end</
=={{header|Sidef}}==
{{trans|Perl}}
<
# returns a database handle configured to throw an exception on query errors
Line 1,439 ⟶ 1,442:
nil, user, pass
)
}</
=={{header|Tcl}}==
{{works with|Tcl|8.6}}
Also requires the TDBC driver for MySQL.
<
proc connect_db {handleName dbname host user pass} {
Line 1,479 ⟶ 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.}}
|