Rosetta Code

SQL-based authentication: Difference between revisions

Page Discussion

SQL-based authentication (view source)

Revision as of 02:37, 9 April 2022

12,959 bytes added ,  2 years ago
m
→‎{{header|Phix}}: syntax coloured, marked p2js incompatible
(Added Wren)
m (→‎{{header|Phix}}: syntax coloured, marked p2js incompatible)
Line 1,000:
=={{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.
<!--<lang Phix>(notonline)-->
<lang Phix>-- demo/rosetta/SQL-based_authentication.exw
<span style="color: #000080;font-style:italic;">-- demo\rosetta\SQL-based_authentication.exw</span>
include pSQLite.e
<span style="color: #008080;">without</span> <span style="color: #008080;">js</span> <span style="color: #000080;font-style:italic;">-- (file i/o)</span>
include md5.exw
<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>
sqlite3_stmt pAddUser = NULL
<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>
procedure add_user(sqlite3 db, string name, pw)
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
if pAddUser=NULL then
<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>
pAddUser = sqlite3_prepare(db,"INSERT INTO users (username,pass_salt,pass_md5) VALUES(:name, :salt, :md5);")
<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>
end if
<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>
string salt = sq_rand(repeat(#FF,16)),
<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>
md5s = md5(salt&pw)
<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>
sqlite3_bind_text(pAddUser,":name", name)
<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>
sqlite3_bind_text(pAddUser,":salt", salt)
<span style="color: #000000;">sqlite3_reset</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAddUser</span><span style="color: #0000FF;">)</span>
sqlite3_bind_text(pAddUser,":md5", md5s)
<span style="color: #008080;">end</span> <span style="color: #008080;">procedure</span>
{} = sqlite3_step(pAddUser) -- (nb: ignores any errors.)
sqlite3_reset(pAddUser)
<span style="color: #000000;">sqlite3_stmt</span> <span style="color: #000000;">pAuthUser</span> <span style="color: #0000FF;">=</span> <span style="color: #004600;">NULL</span>
end procedure
 
<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>
sqlite3_stmt pAuthUser = NULL
<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>
function authenticate_user(sqlite3 db, string name, pw)
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
if pAuthUser=NULL then
<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>
pAuthUser = sqlite3_prepare(db,"SELECT pass_salt, pass_md5 FROM users WHERE username = :name;")
<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>
end if
<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>
sqlite3_bind_text(pAuthUser,":name", name)
<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>
integer res = sqlite3_step(pAuthUser)
<span style="color: #008080;">else</span>
if res!=SQLITE_ROW then
<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>
res = false -- (no such user)
<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>
else
<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>
string salt = sqlite3_column_text(pAuthUser,1)
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
string pass_md5 = sqlite3_column_text(pAuthUser,2)
<span style="color: #000000;">sqlite3_reset</span><span style="color: #0000FF;">(</span><span style="color: #000000;">pAuthUser</span><span style="color: #0000FF;">)</span>
res = (pass_md5==md5(salt&pw))
<span style="color: #008080;">return</span> <span style="color: #000000;">res</span>
end if
<span style="color: #008080;">end</span> <span style="color: #008080;">function</span>
sqlite3_reset(pAuthUser)
return res
<span style="color: #008080;">constant</span> <span style="color: #000000;">create_cmd</span> <span style="color: #0000FF;">=</span> <span style="color: #008000;">"""
end function
CREATE TABLE IF NOT EXISTS users(
 
userid INTEGER PRIMARY KEY AUTOINCREMENT,
constant create_cmd = """
username VARCHAR(32) UNIQUE NOT NULL,
CREATE TABLE IF NOT EXISTS users(
pass_salt tinyblob,
userid INTEGER PRIMARY KEY AUTOINCREMENT,
-- a string of 16 random bytes
username VARCHAR(32) UNIQUE NOT NULL,
pass_salt pass_md5 tinyblob,);
-- abinary stringMD5 hash of 16pass_salt concatenated with randomthe bytespassword
"""</span>
pass_md5 tinyblob);
-- binary MD5 hash of pass_salt concatenated with the password
<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>
procedure main()
<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>
sequence sqlversion = sqlite3_libversion(true)
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
if sqlversion<{3,3,0} then
<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>
crash("a newer sqlite.dll/so is required (for IF NOT EXISTS)")
<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>
end if
<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>
sqlite3 db = sqlite3_open("users.sqlite")
integer res = sqlite3_exec(db,create_cmd)
<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>
if res!=SQLITE_OK then ?9/0 end if
<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>
sqlite3_set_fatal_id(SQLITE3_NON_FATAL) -- (else trying to re-add user crashes)
<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>
add_user(db,"user","password")
printf(1,"user with correct password:%t\n",authenticate_user(db, "user", "password"))
<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>
printf(1,"user with incorrect password:%t\n",authenticate_user(db, "user", "wrong"))
<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>
if pAddUser!=NULL then
<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>
if sqlite3_finalize(pAddUser)!=SQLITE_OK then ?9/0 end if
<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>
end if
<span style="color: #008080;">end</span> <span style="color: #008080;">if</span>
if pAuthUser!=NULL then
<span style="color: #000000;">sqlite3_close</span><span style="color: #0000FF;">(</span><span style="color: #000000;">db</span><span style="color: #0000FF;">)</span>
if sqlite3_finalize(pAuthUser)!=SQLITE_OK then ?9/0 end if
<span style="color: #0000FF;">?</span><span style="color: #008000;">"done"</span>
end if
<span style="color: #0000FF;">{}</span> <span style="color: #0000FF;">=</span> <span style="color: #7060A8;">wait_key</span><span style="color: #0000FF;">()</span>
sqlite3_close(db)
<span style="color: #008080;">end</span> <span style="color: #008080;">procedure</span>
?"done"
<span style="color: #000000;">main</span><span style="color: #0000FF;">()</span>
{} = wait_key()
<!--</lang>-->
end procedure
main()</lang>
{{out}}
<pre>
Petelomax
7,806

edits

Retrieved from "https://rosettacode.org/wiki/Special:MobileDiff/20573"