util-sql-users.lua /size: 11 Kb    last modification: 2020-07-01 14:35
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-- 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
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") -- utilities.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-- print(users.encryptpassword("test")) -- MD5:098F6BCD4621D373CADE4E832627B4F6
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-- 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
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) -- 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
466end
467