1if 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
10
11
12
13local sql = utilities.sql
14
15local find, topattern = string.find, string.topattern
16local sumHEXA = md5.sumHEXA
17local toboolean = string.toboolean
18local lpegmatch = lpeg.match
19
20local sql = require("util-sql")
21local users = { }
22sql.users = users
23
24local trace_sql = false trackers.register("sql.users.trace", function(v) trace_sql = v end)
25local report = logs.reporter("sql","users")
26
27local split = lpeg.splitat(":")
28
29local valid = nil
30local hash = function(s) return "MD5:" .. sumHEXA(s) end
31local sha2 = sha2 or (utilities and utilities.sha2)
32
33if not sha2 and LUAVERSION >= 5.3 then
34 sha2 = require("util-sha")
35end
36
37if 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
52else
53
54 valid = {
55 MD5 = hash,
56 SHA224 = hash,
57 SHA256 = hash,
58 SHA384 = hash,
59 SHA512 = hash,
60 }
61
62end
63
64local 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)
73end
74
75local 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
81end
82
83local function samepasswords(one,two)
84 if not one or not two then
85 return false
86 end
87 return encryptpassword(one) == encryptpassword(two)
88end
89
90local 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
99end
100
101users.encryptpassword = encryptpassword
102users.cleanuppassword = cleanuppassword
103users.samepasswords = samepasswords
104users.validaddress = validaddress
105
106
107
108local function checkeddb(presets,datatable)
109 return sql.usedatabase(presets,datatable or presets.datatable or "users")
110end
111
112users.usedb = checkeddb
113
114local groupnames = { }
115local groupnumbers = { }
116
117local 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
126end
127
128registergroup("superuser")
129registergroup("administrator")
130registergroup("user")
131registergroup("guest")
132
133users.groupnames = groupnames
134users.groupnumbers = groupnumbers
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150local 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
167local 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
182local 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
195function 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
210end
211
212local template =[[
213 SELECT
214 %fields%
215 FROM
216 %basename%
217 WHERE
218 `name` = '%[name]%'
219 AND
220 `password` = '%[password]%'
221 ;
222]]
223
224local template =[[
225 SELECT
226 %fields%
227 FROM
228 %basename%
229 WHERE
230 `name` = '%[name]%'
231 ;
232]]
233
234function 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
259end
260
261local 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
285function 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
311end
312
313local template =[[
314 SELECT
315 %fields%
316 FROM
317 %basename%
318 WHERE
319 `name` = '%[name]%' ;
320]]
321
322function 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
336end
337
338local template =[[
339 SELECT
340 %fields%
341 FROM
342 %basename%
343 WHERE
344 `id` = '%id%' ;
345]]
346
347local 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
361end
362
363users.getbyid = getbyid
364
365local 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
382function 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
423end
424
425local template =[[
426 DELETE FROM
427 %basename%
428 WHERE
429 `id` = '%id%' ;
430]]
431
432function users.remove(db,id)
433
434 db.execute {
435 template = template,
436 variables = {
437 basename = db.basename,
438 id = id,
439 },
440 }
441
442end
443
444local template =[[
445 SELECT
446 %fields%
447 FROM
448 %basename%
449 ORDER BY
450 `name` ;
451]]
452
453function users.collect(db)
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
466end
467 |