util-sql-users.lua /size: 11 Kb    last modification: 2020-07-01 14:35
1
if
not
modules
then
modules
=
{
}
end
modules
[
'
util-sql-users
'
]
=
{
2
version
=
1
.
001
,
3
comment
=
"
companion to lmx-*
"
,
4
author
=
"
Hans Hagen, PRAGMA-ADE, Hasselt NL
"
,
5
copyright
=
"
PRAGMA ADE / ConTeXt Development Team
"
,
6
license
=
"
see context related readme files
"
7
}
8 9
-- This is experimental code and currently part of the base installation simply
10
-- because it's easier to dirtribute this way. Eventually it will be documented
11
-- and the related scripts will show up as well.
12 13
local
sql
=
utilities
.
sql
14 15
local
find
,
topattern
=
string
.
find
,
string
.
topattern
16
local
sumHEXA
=
md5
.
sumHEXA
17
local
toboolean
=
string
.
toboolean
18
local
lpegmatch
=
lpeg
.
match
19 20
local
sql
=
require
(
"
util-sql
"
)
-- utilities.sql
21
local
users
=
{
}
22
sql
.
users
=
users
23 24
local
trace_sql
=
false
trackers
.
register
(
"
sql.users.trace
"
,
function
(
v
)
trace_sql
=
v
end
)
25
local
report
=
logs
.
reporter
(
"
sql
"
,
"
users
"
)
26 27
local
split
=
lpeg
.
splitat
(
"
:
"
)
28 29
local
valid
=
nil
30
local
hash
=
function
(
s
)
return
"
MD5:
"
.
.
sumHEXA
(
s
)
end
31
local
sha2
=
sha2
or
(
utilities
and
utilities
.
sha2
)
32 33
if
not
sha2
and
LUAVERSION
>
=
5
.
3
then
34
sha2
=
require
(
"
util-sha
"
)
35
end
36 37
if
sha2
then
38 39
local
HASH224
=
sha2
.
HASH224
40
local
HASH256
=
sha2
.
HASH256
41
local
HASH384
=
sha2
.
HASH384
42
local
HASH512
=
sha2
.
HASH512
43 44
valid
=
{
45
MD5
=
hash
,
46
SHA224
=
function
(
s
)
return
"
SHA224:
"
.
.
HASH224
(
s
)
end
,
47
SHA256
=
function
(
s
)
return
"
SHA256:
"
.
.
HASH256
(
s
)
end
,
48
SHA384
=
function
(
s
)
return
"
SHA384:
"
.
.
HASH384
(
s
)
end
,
49
SHA512
=
function
(
s
)
return
"
SHA512:
"
.
.
HASH512
(
s
)
end
,
50
}
51 52
else
53 54
valid
=
{
55
MD5
=
hash
,
56
SHA224
=
hash
,
57
SHA256
=
hash
,
58
SHA384
=
hash
,
59
SHA512
=
hash
,
60
}
61 62
end
63 64
local
function
encryptpassword
(
str
,
how
)
65
if
not
str
or
str
=
=
"
"
then
66
return
"
"
67
end
68
local
prefix
,
rest
=
lpegmatch
(
split
,
str
)
69
if
prefix
and
rest
and
valid
[
prefix
]
then
70
return
str
71
end
72
return
(
how
and
valid
[
how
]
or
valid
.
MD5
)
(
str
)
73
end
74 75
local
function
cleanuppassword
(
str
)
76
local
prefix
,
rest
=
lpegmatch
(
split
,
str
)
77
if
prefix
and
rest
and
valid
[
prefix
]
then
78
return
rest
79
end
80
return
str
81
end
82 83
local
function
samepasswords
(
one
,
two
)
84
if
not
one
or
not
two
then
85
return
false
86
end
87
return
encryptpassword
(
one
)
=
=
encryptpassword
(
two
)
88
end
89 90
local
function
validaddress
(
address
,
addresses
)
91
if
address
and
addresses
and
address
~
=
"
"
and
addresses
~
=
"
"
then
92
if
find
(
address
,
topattern
(
addresses
,
true
,
true
)
)
then
93
return
true
,
"
valid remote address
"
94
end
95
return
false
,
"
invalid remote address
"
96
else
97
return
true
,
"
no remote address check
"
98
end
99
end
100 101
users
.
encryptpassword
=
encryptpassword
102
users
.
cleanuppassword
=
cleanuppassword
103
users
.
samepasswords
=
samepasswords
104
users
.
validaddress
=
validaddress
105 106
-- print(users.encryptpassword("test")) -- MD5:098F6BCD4621D373CADE4E832627B4F6
107 108
local
function
checkeddb
(
presets
,
datatable
)
109
return
sql
.
usedatabase
(
presets
,
datatable
or
presets
.
datatable
or
"
users
"
)
110
end
111 112
users
.
usedb
=
checkeddb
113 114
local
groupnames
=
{
}
115
local
groupnumbers
=
{
}
116 117
local
function
registergroup
(
name
)
118
local
n
=
#
groupnames
+
1
119
groupnames
[
n
]
=
name
120
groupnames
[
tostring
(
n
)
]
=
name
121
groupnames
[
name
]
=
name
122
groupnumbers
[
n
]
=
n
123
groupnumbers
[
tostring
(
n
)
]
=
n
124
groupnumbers
[
name
]
=
n
125
return
n
126
end
127 128
registergroup
(
"
superuser
"
)
129
registergroup
(
"
administrator
"
)
130
registergroup
(
"
user
"
)
131
registergroup
(
"
guest
"
)
132 133
users
.
groupnames
=
groupnames
134
users
.
groupnumbers
=
groupnumbers
135 136
-- password 'test':
137
--
138
-- INSERT insert into users (`name`,`password`,`group`,`enabled`) values ('...','MD5:098F6BCD4621D373CADE4E832627B4F6',1,1) ;
139
--
140
-- MD5:098F6BCD4621D373CADE4E832627B4F6
141
-- SHA224:90A3ED9E32B2AAF4C61C410EB925426119E1A9DC53D4286ADE99A809
142
-- SHA256:9F86D081884C7D659A2FEAA0C55AD015A3BF4F1B2B0B822CD15D6C15B0F00A08
143
-- SHA384:768412320F7B0AA5812FCE428DC4706B3CAE50E02A64CAA16A782249BFE8EFC4B7EF1CCB126255D196047DFEDF17A0A9
144
-- SHA512:EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF
145 146
-- old values (a name can have utf and a password a long hash):
147
--
148
-- name 80, fullname 80, password 50
149 150
local
template
=
[[
151 CREATE TABLE `users` ( 152 `id` int(11) NOT NULL AUTO_INCREMENT, 153 `name` varchar(100) NOT NULL, 154 `fullname` varchar(100) NOT NULL, 155 `password` varchar(200) DEFAULT NULL, 156 `group` int(11) NOT NULL, 157 `enabled` int(11) DEFAULT '1', 158 `email` varchar(80) DEFAULT NULL, 159 `address` varchar(256) DEFAULT NULL, 160 `theme` varchar(50) DEFAULT NULL, 161 `data` longtext, 162 PRIMARY KEY (`id`), 163 UNIQUE KEY `name_unique` (`name`) 164 ) DEFAULT CHARSET = utf8 ; 165
]]
166 167
local
sqlite_template
=
[[
168 CREATE TABLE `users` ( 169 `id` INTEGER PRIMARY KEY AUTOINCREMENT, 170 `name` TEXT NOT NULL, 171 `fullname` TEXT NOT NULL, 172 `password` TEXT DEFAULT NULL, 173 `group` INTEGER NOT NULL, 174 `enabled` INTEGER DEFAULT '1', 175 `email` TEXT DEFAULT NULL, 176 `address` TEXT DEFAULT NULL, 177 `theme` TEXT DEFAULT NULL, 178 `data` TEXT DEFAULT NULL 179 ) ; 180
]]
181 182
local
converter
,
fields
=
sql
.
makeconverter
{
183
{
name
=
"
id
"
,
type
=
"
number
"
}
,
184
{
name
=
"
name
"
,
type
=
"
string
"
}
,
185
{
name
=
"
fullname
"
,
type
=
"
string
"
}
,
186
{
name
=
"
password
"
,
type
=
"
string
"
}
,
187
{
name
=
"
group
"
,
type
=
groupnames
}
,
188
{
name
=
"
enabled
"
,
type
=
"
boolean
"
}
,
189
{
name
=
"
email
"
,
type
=
"
string
"
}
,
190
{
name
=
"
address
"
,
type
=
"
string
"
}
,
191
{
name
=
"
theme
"
,
type
=
"
string
"
}
,
192
{
name
=
"
data
"
,
type
=
"
deserialize
"
}
,
193
}
194 195
function
users
.
createdb
(
presets
,
datatable
)
196 197
local
db
=
checkeddb
(
presets
,
datatable
)
198 199
db
.
execute
{
200
template
=
db
.
usedmethod
=
=
"
sqlite
"
and
sqlite_template
or
template
,
201
variables
=
{
202
basename
=
db
.
basename
,
203
}
,
204
}
205 206
report
(
"
datatable %a created in %a
"
,
db
.
name
,
db
.
base
)
207 208
return
db
209 210
end
211 212
local
template
=
[[
213 SELECT 214 %fields% 215 FROM 216 %basename% 217 WHERE 218 `name` = '%[name]%' 219 AND 220 `password` = '%[password]%' 221 ; 222
]]
223 224
local
template
=
[[
225 SELECT 226 %fields% 227 FROM 228 %basename% 229 WHERE 230 `name` = '%[name]%' 231 ; 232
]]
233 234
function
users
.
valid
(
db
,
username
,
password
,
address
)
235 236
local
data
=
db
.
execute
{
237
template
=
template
,
238
converter
=
converter
,
239
variables
=
{
240
basename
=
db
.
basename
,
241
fields
=
fields
,
242
name
=
username
,
243
}
,
244
}
245
local
data
=
data
and
data
[
1
]
246
if
not
data
then
247
return
false
,
"
unknown user
"
248
elseif
not
data
.
enabled
then
249
return
false
,
"
disabled user
"
250
elseif
data
.
password
~
=
encryptpassword
(
password
)
then
251
return
false
,
"
wrong password
"
252
elseif
not
validaddress
(
address
,
data
.
address
)
then
253
return
false
,
"
invalid address
"
254
else
255
data
.
password
=
nil
256
return
data
,
"
okay
"
257
end
258 259
end
260 261
local
template
=
[[
262 INSERT INTO %basename% ( 263 `name`, 264 `fullname`, 265 `password`, 266 `group`, 267 `enabled`, 268 `email`, 269 `address`, 270 `theme`, 271 `data` 272 ) VALUES ( 273 '%[name]%', 274 '%[fullname]%', 275 '%[password]%', 276 '%[group]%', 277 '%[enabled]%', 278 '%[email]%', 279 '%[address]%', 280 '%[theme]%', 281 '%[data]%' 282 ) ; 283
]]
284 285
function
users
.
add
(
db
,
specification
)
286 287
local
name
=
specification
.
username
or
specification
.
name
288 289
if
not
name
or
name
=
=
"
"
then
290
return
291
end
292 293
local
data
=
specification
.
data
294 295
db
.
execute
{
296
template
=
template
,
297
variables
=
{
298
basename
=
db
.
basename
,
299
name
=
name
,
300
fullname
=
name
or
fullname
,
301
password
=
encryptpassword
(
specification
.
password
or
"
"
)
,
302
group
=
groupnumbers
[
specification
.
group
]
or
groupnumbers
.
guest
,
303
enabled
=
toboolean
(
specification
.
enabled
)
and
"
1
"
or
"
0
"
,
304
email
=
specification
.
email
,
305
address
=
specification
.
address
,
306
theme
=
specification
.
theme
,
307
data
=
type
(
data
)
=
=
"
table
"
and
db
.
serialize
(
data
,
"
return
"
)
or
"
"
,
308
}
,
309
}
310 311
end
312 313
local
template
=
[[
314 SELECT 315 %fields% 316 FROM 317 %basename% 318 WHERE 319 `name` = '%[name]%' ; 320
]]
321 322
function
users
.
getbyname
(
db
,
name
)
323 324
local
data
=
db
.
execute
{
325
template
=
template
,
326
converter
=
converter
,
327
variables
=
{
328
basename
=
db
.
basename
,
329
fields
=
fields
,
330
name
=
name
,
331
}
,
332
}
333 334
return
data
and
data
[
1
]
or
nil
335 336
end
337 338
local
template
=
[[
339 SELECT 340 %fields% 341 FROM 342 %basename% 343 WHERE 344 `id` = '%id%' ; 345
]]
346 347
local
function
getbyid
(
db
,
id
)
348 349
local
data
=
db
.
execute
{
350
template
=
template
,
351
converter
=
converter
,
352
variables
=
{
353
basename
=
db
.
basename
,
354
fields
=
fields
,
355
id
=
id
,
356
}
,
357
}
358 359
return
data
and
data
[
1
]
or
nil
360 361
end
362 363
users
.
getbyid
=
getbyid
364 365
local
template
=
[[
366 UPDATE 367 %basename% 368 SET 369 `fullname` = '%[fullname]%', 370 `password` = '%[password]%', 371 `group` = '%[group]%', 372 `enabled` = '%[enabled]%', 373 `email` = '%[email]%', 374 `address` = '%[address]%', 375 `theme` = '%[theme]%', 376 `data` = '%[data]%' 377 WHERE 378 `id` = '%id%' 379 ; 380
]]
381 382
function
users
.
save
(
db
,
id
,
specification
)
383 384
id
=
tonumber
(
id
)
385 386
if
not
id
then
387
return
388
end
389 390
local
user
=
getbyid
(
db
,
id
)
391 392
if
tonumber
(
user
.
id
)
~
=
id
then
393
return
394
end
395 396
local
fullname
=
specification
.
fullname
=
=
nil
and
user
.
fulname
or
specification
.
fullname
397
local
password
=
specification
.
password
=
=
nil
and
user
.
password
or
specification
.
password
398
local
group
=
specification
.
group
=
=
nil
and
user
.
group
or
specification
.
group
399
local
enabled
=
specification
.
enabled
=
=
nil
and
user
.
enabled
or
specification
.
enabled
400
local
email
=
specification
.
email
=
=
nil
and
user
.
email
or
specification
.
email
401
local
address
=
specification
.
address
=
=
nil
and
user
.
address
or
specification
.
address
402
local
theme
=
specification
.
theme
=
=
nil
and
user
.
theme
or
specification
.
theme
403
local
data
=
specification
.
data
=
=
nil
and
user
.
data
or
specification
.
data
404 405
db
.
execute
{
406
template
=
template
,
407
variables
=
{
408
basename
=
db
.
basename
,
409
id
=
id
,
410
fullname
=
fullname
,
411
password
=
encryptpassword
(
password
)
,
412
group
=
groupnumbers
[
group
]
,
413
enabled
=
toboolean
(
enabled
)
and
"
1
"
or
"
0
"
,
414
email
=
email
,
415
address
=
address
,
416
theme
=
theme
,
417
data
=
type
(
data
)
=
=
"
table
"
and
db
.
serialize
(
data
,
"
return
"
)
or
"
"
,
418
}
,
419
}
420 421
return
getbyid
(
db
,
id
)
422 423
end
424 425
local
template
=
[[
426 DELETE FROM 427 %basename% 428 WHERE 429 `id` = '%id%' ; 430
]]
431 432
function
users
.
remove
(
db
,
id
)
433 434
db
.
execute
{
435
template
=
template
,
436
variables
=
{
437
basename
=
db
.
basename
,
438
id
=
id
,
439
}
,
440
}
441 442
end
443 444
local
template
=
[[
445 SELECT 446 %fields% 447 FROM 448 %basename% 449 ORDER BY 450 `name` ; 451
]]
452 453
function
users
.
collect
(
db
)
-- maybe also an id/name only variant
454 455
local
records
,
keys
=
db
.
execute
{
456
template
=
template
,
457
converter
=
converter
,
458
variables
=
{
459
basename
=
db
.
basename
,
460
fields
=
fields
,
461
}
,
462
}
463 464
return
records
,
keys
465 466
end
467