SQL-based authentication: Difference between revisions
Content added Content deleted
(Added Wren) |
m (→{{header|Phix}}: syntax coloured, marked p2js incompatible) |
||
Line 1,000: | Line 1,000: | ||
=={{header|Phix}}== |
=={{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 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#Phix|MD5]], now modified to be an executable library. |
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_md5 tinyblob); |
|||
-- |
-- binary MD5 hash of pass_salt concatenated with the password |
||
"""</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}} |
{{out}} |
||
<pre> |
<pre> |