path: root/tex/context/base/mkiv/util-sql-users.lua

if not modules then modules = { } end modules ['util-sql-users'] = {
    version   = 1.001,
    comment   = "companion to lmx-*",
    author    = "Hans Hagen, PRAGMA-ADE, Hasselt NL",
    copyright = "PRAGMA ADE / ConTeXt Development Team",
    license   = "see context related readme files"
}

-- This is experimental code and currently part of the base installation simply
-- because it's easier to dirtribute this way. Eventually it will be documented
-- and the related scripts will show up as well.

local sql = utilities.sql

local find, topattern = string.find, string.topattern
local sumHEXA = md5.sumHEXA
local toboolean = string.toboolean
local lpegmatch = lpeg.match

local sql   = require("util-sql") -- utilities.sql
local users = { }
sql.users   = users

local trace_sql = false  trackers.register("sql.users.trace", function(v) trace_sql = v end)
local report    = logs.reporter("sql","users")

local split = lpeg.splitat(":")

local valid = nil
local hash  = function(s) return "MD5:" .. sumHEXA(s) end
local sha2  = sha2 or (utilities and utilities.sha2)

if not sha2 and LUAVERSION >= 5.3 then
    sha2 = require("util-sha")
end

if sha2 then

    local HASH224 = sha2.HASH224
    local HASH256 = sha2.HASH256
    local HASH384 = sha2.HASH384
    local HASH512 = sha2.HASH512

    valid = {
        MD5    = hash,
        SHA224 = function(s) return "SHA224:" .. HASH224(s) end,
        SHA256 = function(s) return "SHA256:" .. HASH256(s) end,
        SHA384 = function(s) return "SHA384:" .. HASH384(s) end,
        SHA512 = function(s) return "SHA512:" .. HASH512(s) end,
    }

else

    valid = {
        MD5    = hash,
        SHA224 = hash,
        SHA256 = hash,
        SHA384 = hash,
        SHA512 = hash,
    }

end

local function encryptpassword(str,how)
    if not str or str == "" then
        return ""
    end
    local prefix, rest = lpegmatch(split,str)
    if prefix and rest and valid[prefix] then
        return str
    end
    return (how and valid[how] or valid.MD5)(str)
end

local function cleanuppassword(str)
    local prefix, rest = lpegmatch(split,str)
    if prefix and rest and valid[prefix] then
        return rest
    end
    return str
end

local function samepasswords(one,two)
    if not one or not two then
        return false
    end
    return encryptpassword(one) == encryptpassword(two)
end

local function validaddress(address,addresses)
    if address and addresses and address ~= "" and addresses ~= "" then
        if find(address,topattern(addresses,true,true)) then
            return true, "valid remote address"
        end
        return false, "invalid remote address"
    else
        return true, "no remote address check"
    end
end

users.encryptpassword = encryptpassword
users.cleanuppassword = cleanuppassword
users.samepasswords   = samepasswords
users.validaddress    = validaddress

-- print(users.encryptpassword("test")) -- MD5:098F6BCD4621D373CADE4E832627B4F6

local function checkeddb(presets,datatable)
    return sql.usedatabase(presets,datatable or presets.datatable or "users")
end

users.usedb = checkeddb

local groupnames   = { }
local groupnumbers = { }

local function registergroup(name)
    local n = #groupnames + 1
    groupnames  [n]           = name
    groupnames  [tostring(n)] = name
    groupnames  [name]        = name
    groupnumbers[n]           = n
    groupnumbers[tostring(n)] = n
    groupnumbers[name]        = n
    return n
end

registergroup("superuser")
registergroup("administrator")
registergroup("user")
registergroup("guest")

users.groupnames   = groupnames
users.groupnumbers = groupnumbers

-- password 'test':
--
-- INSERT insert into users (`name`,`password`,`group`,`enabled`) values ('...','MD5:098F6BCD4621D373CADE4E832627B4F6',1,1) ;
--
-- MD5:098F6BCD4621D373CADE4E832627B4F6
-- SHA224:90A3ED9E32B2AAF4C61C410EB925426119E1A9DC53D4286ADE99A809
-- SHA256:9F86D081884C7D659A2FEAA0C55AD015A3BF4F1B2B0B822CD15D6C15B0F00A08
-- SHA384:768412320F7B0AA5812FCE428DC4706B3CAE50E02A64CAA16A782249BFE8EFC4B7EF1CCB126255D196047DFEDF17A0A9
-- SHA512:EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF

-- old values (a name can have utf and a password a long hash):
--
-- name 80, fullname 80, password 50

local template = [[
    CREATE TABLE `users` (
        `id`       int(11)      NOT NULL AUTO_INCREMENT,
        `name`     varchar(100) NOT NULL,
        `fullname` varchar(100) NOT NULL,
        `password` varchar(200) DEFAULT NULL,
        `group`    int(11)      NOT NULL,
        `enabled`  int(11)      DEFAULT '1',
        `email`    varchar(80)  DEFAULT NULL,
        `address`  varchar(256) DEFAULT NULL,
        `theme`    varchar(50)  DEFAULT NULL,
        `data`     longtext,
        PRIMARY KEY (`id`),
        UNIQUE KEY `name_unique` (`name`)
    ) DEFAULT CHARSET = utf8 ;
]]

local sqlite_template = [[
    CREATE TABLE `users` (
        `id`       INTEGER PRIMARY KEY AUTOINCREMENT,
        `name`     TEXT NOT NULL,
        `fullname` TEXT NOT NULL,
        `password` TEXT DEFAULT NULL,
        `group`    INTEGER NOT NULL,
        `enabled`  INTEGER DEFAULT '1',
        `email`    TEXT DEFAULT NULL,
        `address`  TEXT DEFAULT NULL,
        `theme`    TEXT DEFAULT NULL,
        `data`     TEXT DEFAULT NULL
    ) ;
]]

local converter, fields = sql.makeconverter {
    { name = "id",       type = "number"      },
    { name = "name",     type = "string"      },
    { name = "fullname", type = "string"      },
    { name = "password", type = "string"      },
    { name = "group",    type = groupnames    },
    { name = "enabled",  type = "boolean"     },
    { name = "email",    type = "string"      },
    { name = "address",  type = "string"      },
    { name = "theme",    type = "string"      },
    { name = "data",     type = "deserialize" },
}

function users.createdb(presets,datatable)

    local db = checkeddb(presets,datatable)

    db.execute {
        template  = db.usedmethod == "sqlite" and sqlite_template or template,
        variables = {
            basename = db.basename,
        },
    }

    report("datatable %a created in %a",db.name,db.base)

    return db

end

local template =[[
    SELECT
        %fields%
    FROM
        %basename%
    WHERE
        `name` = '%[name]%'
    AND
        `password` = '%[password]%'
    ;
]]

local template =[[
    SELECT
        %fields%
    FROM
        %basename%
    WHERE
        `name` = '%[name]%'
    ;
]]

function users.valid(db,username,password,address)

    local data = db.execute {
        template  = template,
        converter = converter,
        variables = {
            basename = db.basename,
            fields   = fields,
            name     = username,
        },
    }
    local data = data and data[1]
    if not data then
        return false, "unknown user"
    elseif not data.enabled then
        return false, "disabled user"
    elseif data.password ~= encryptpassword(password) then
        return false, "wrong password"
    elseif not validaddress(address,data.address) then
        return false, "invalid address"
    else
        data.password = nil
        return data, "okay"
    end

end

local template =[[
    INSERT INTO %basename% (
        `name`,
        `fullname`,
        `password`,
        `group`,
        `enabled`,
        `email`,
        `address`,
        `theme`,
        `data`
    ) VALUES (
        '%[name]%',
        '%[fullname]%',
        '%[password]%',
        '%[group]%',
        '%[enabled]%',
        '%[email]%',
        '%[address]%',
        '%[theme]%',
        '%[data]%'
    ) ;
]]

function users.add(db,specification)

    local name = specification.username or specification.name

    if not name or name == "" then
        return
    end

    local data = specification.data

    db.execute {
        template  = template,
        variables = {
            basename = db.basename,
            name     = name,
            fullname = name or fullname,
            password = encryptpassword(specification.password or ""),
            group    = groupnumbers[specification.group] or groupnumbers.guest,
            enabled  = toboolean(specification.enabled) and "1" or "0",
            email    = specification.email,
            address  = specification.address,
            theme    = specification.theme,
            data     = type(data) == "table" and db.serialize(data,"return") or "",
        },
    }

end

local template =[[
    SELECT
        %fields%
    FROM
        %basename%
    WHERE
        `name` = '%[name]%' ;
]]

function users.getbyname(db,name)

    local data = db.execute {
        template  = template,
        converter = converter,
        variables = {
            basename = db.basename,
            fields   = fields,
            name     = name,
        },
    }

    return data and data[1] or nil

end

local template =[[
    SELECT
        %fields%
    FROM
        %basename%
    WHERE
        `id` = '%id%' ;
]]

local function getbyid(db,id)

    local data = db.execute {
        template  = template,
        converter = converter,
        variables = {
            basename = db.basename,
            fields   = fields,
            id       = id,
        },
    }

    return data and data[1] or nil

end

users.getbyid = getbyid

local template =[[
    UPDATE
        %basename%
    SET
        `fullname` = '%[fullname]%',
        `password` = '%[password]%',
        `group`    = '%[group]%',
        `enabled`  = '%[enabled]%',
        `email`    = '%[email]%',
        `address`  = '%[address]%',
        `theme`    = '%[theme]%',
        `data`     = '%[data]%'
    WHERE
        `id` = '%id%'
    ;
]]

function users.save(db,id,specification)

    id = tonumber(id)

    if not id then
        return
    end

    local user = getbyid(db,id)

    if tonumber(user.id) ~= id then
        return
    end

    local fullname = specification.fullname == nil and user.fulname   or specification.fullname
    local password = specification.password == nil and user.password  or specification.password
    local group    = specification.group    == nil and user.group     or specification.group
    local enabled  = specification.enabled  == nil and user.enabled   or specification.enabled
    local email    = specification.email    == nil and user.email     or specification.email
    local address  = specification.address  == nil and user.address   or specification.address
    local theme    = specification.theme    == nil and user.theme     or specification.theme
    local data     = specification.data     == nil and user.data      or specification.data

    db.execute {
        template  = template,
        variables = {
            basename = db.basename,
            id       = id,
            fullname = fullname,
            password = encryptpassword(password),
            group    = groupnumbers[group],
            enabled  = toboolean(enabled) and "1" or "0",
            email    = email,
            address  = address,
            theme    = theme,
            data     = type(data) == "table" and db.serialize(data,"return") or "",
        },
    }

    return getbyid(db,id)

end

local template =[[
    DELETE FROM
        %basename%
    WHERE
        `id` = '%id%' ;
]]

function users.remove(db,id)

    db.execute {
        template  = template,
        variables = {
            basename = db.basename,
            id       = id,
        },
    }

end

local template =[[
    SELECT
        %fields%
    FROM
        %basename%
    ORDER BY
        `name` ;
]]

function users.collect(db) -- maybe also an id/name only variant

    local records, keys = db.execute {
        template  = template,
        converter = converter,
        variables = {
            basename = db.basename,
            fields   = fields,
        },
    }

    return records, keys

end