<?php if (!defined('PmWiki')) exit();
#
# PROGRAM: useradmin-dbase.php
# AUTHOR: Peter Bowers
# DATE: 2015-04-29
# PURPOSE: Complimentary script to UserAdmin recipe for pmwiki. Allows users and groups
# to be stored in a database.
#
/* === UserAdmin-Dbase ===
* Copyright 2015 Peter Bowers <plbowers@gmail.com>
*
* AuthUser account self-registration and management using database (mysqli)
*
* For more information, please see the online documentation at
* http://www.pmwiki.org/wiki/Cookbook/UserAdmin
*
* This script is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This script is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
$RecipeInfo['UserAdmin']['Version'] = '2015-05-16';
#echo "DEBUG: useradmin-dbase: Being included<br />\n";
SDV($AuthUserFunctions['useradmindb'], 'AuthUserDbase');
function AuthUserDbase($pagename, $id, $pw, $pwlist, &$authlist)
{
global $UserAdmin, $AuthUser;
static $first = true;
echo "AUD(pagename=$pagename, id=$id, pw=$pw, pwlist=$pwlist): Entering.<br />\n";
#echo "DEBUG: authlist=<pre>".print_r($authlist,true)."</pre><br />\n";
#echo "DEBUG: UserAdmin=<pre>".print_r($UserAdmin,true)."</pre><br />\n";
$row = $UserAdmin->ReadUser($id);
#echo "DEBUG: row=<pre>".print_r($row,true)."</pre><br />\n";
#echo "DEBUG: crypt($pw, $row[userpwhash])="._crypt($pw, $row['userpwhash'])."<br />\n";
if (!empty($row['userpwhash']) && (_crypt($pw, $row['userpwhash']) == $row['userpwhash'])) {
$groups = $UserAdmin->ReadGroups($id, true);
#echo "DEBUG: AUD: groups=<pre>".print_r($groups,true)."</pre><br />\n";
foreach ($groups as $g)
$authlist[$g] = 1;
#echo "DEBUG: AUD: authlist=<pre>".print_r($authlist,true)."</pre><br />\n";
#exit; //DEBUG:
return true;
}
#echo "DEBUG: AUD: Returning FALSE<br />\n";
return false;
}
# If this is uncommented then we can't log in
#if (strncmp($action, 'user', 4)) return;
require_once('useradmin-core.php');
class UserAdminDbase extends UserAdmin {
var $EnableNestedGroups = true; // allow nested groups
var $pdo; // database connection
var $dbprefix = '';
function __construct($dsn, $user, $pass, $opts=null, $prefix='')
{
try {
$this->pdo = new PDO($dsn, $user, $pass, $opts);
} catch (PDOException $e) {
$this->dblog( "Connection failed: ".$e->getMessage(), true);
}
if ($prefix)
$this->dbprefix = $prefix;
}
function dblog($msg, $fatal=true)
{
echo $msg;
if ($fatal) exit;
}
/* check that the db structure has been created */
/*
$sql = "SHOW TABLES LIKE '{$this->dbconfig['prefix']}uadb'";
$result = $conn->query($sql);
if ($result && $result->num_rows > 0) {
$this->conn = $conn;
return $conn;
}
echo "<h1>ERROR: Database is not yet set up for UserAdmin Dbase.</h1>\n";
exit(1);
*/
function dbtablename($tbl)
{
if ($this->dbprefix)
$tbl = $this->dbprefix.$tbl;
return $tbl;
}
function dbprepare($sql)
{
if (!$result = $this->pdo->prepare($sql)) {
$errmsg = "DB ERROR: Cannot prepare. SQL=<$sql> error({$this->conn->errno})=$this->conn->error\n";
$this->dberror($errmsg);
}
return $result;
}
function dbquery($sql)
{
if (!$this->dbconnect())
exit; // this shouldn't ever really happen
if (!($stmt = $this->conn->query($sql)) || $this->conn->errno) {
$errmsg = "DB ERROR: Cannot query. SQL=<$sql> error({$this->conn->errno})=$this->conn->error\n";
$this->dberror($errmsg);
}
return $stmt;
}
function dbfetch($result)
{
$rtn = mysqli_fetch_assoc($result);
if ($this->conn->errno) {
$errmsg = "DB ERROR: Cannot fetch. error({$this->conn->errno})=$this->conn->error\n";
$this->dberror($errmsg);
}
return $rtn;
}
function dberror($msg)
{
# This would be a good place for logging
die($msg);
}
function ReadUser($username, $readgroup=false)
{
#echo "DEBUG: ReadUser($username, $readgroup): Entering<br />\n";
$sql = "SELECT *
FROM ".$this->dbtablename('users')."
WHERE username = :username ";
try {
$stmt = $this->pdo->prepare($sql);
$stmt->execute(array(':username'=>$username));
} catch (PDOException $e) {
$this->dblog("ReadUser(): Error: SQL=$sql ERROR=".$e->getMessage());
}
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($readgroup) $row['usergroups'] = $this->ReadGroups($username,true);
#echo "DEBUG: ReadUser: row=<pre>".print_r($row,true)."</pre><br />\n";
# If I do this "union" of results then WriteUser() doesn't know where the read came from
#if (!$row) $row = parent::ReadUser($username, $readgroup);
return $row;
}
function WriteUser($username, $data, $csum='', $auth='read')
{
$row = $this->ReadUser($username);
#echo "DEBUG: PRE: row=<pre>".print_r($row,true)."</pre><br />\n";
if ($data) {
if ($row) {
# UPDATE
$sql = "UPDATE ".$this->dbtablename('users')." SET ";
$sep = '';
$bindvars = array();
foreach ($data as $k=>$v) {
if ($k == 'username') continue;
$sql .= "$sep$k=:$k";
$bindvars[":$k"] = $v;
$sep = ',';
}
$sql .= " WHERE username = :username ";
$bindvars[':username'] = $username; // needed for the WHERE clause
} else {
# INSERT
$sql = "INSERT INTO ".$this->dbtablename('users')." (username";
$vals= "VALUES (:username";
$bindvars=array(':username'=>$username);
foreach ($data as $k=>$v) {
if ($k == 'username') continue; // already put in above
$sql .= ", $k";
$vals.= ", :$k";
$bindvars[":$k"] = $v;
}
$sql .= ") ".$vals.")";
}
try {
$stmt = $this->pdo->prepare($sql);
$stmt->execute($bindvars);
} catch (PDOException $e) {
$this->dblog("WriteUser(): Error SQL=$sql ERROR=".$e->getMessage());
}
}
return true;
}
function ListGroups($pat=NULL)
{
$sql = "SELECT groupname FROM ".$this->dbtablename('groups');
if ($pat)
$sql .= " WHERE groupname = :groupname";
try {
$stmt = $this->pdo->prepare($sql);
if ($pat) $stmt->execute(array(':groupname'=>$pat)); // do we really need a "pattern" or is groupname enuf?
else $stmt->execute();
} catch (PDOException $e) {
$this->dblog("ListGroups(): Error SQL=$sql ERROR=".$e->getMessage());
}
$rtn = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
$rtn[] = $row['groupname'];
#echo "DEBUG: ListGroups(): rtn=<pre>".print_r($rtn,true)."</pre><br />\n";
return $rtn;
}
function ListUsers($pat=NULL)
{
$sql = "SELECT *
FROM ".$this->dbtablename('users');
if ($pat)
$sql .= " WHERE username = :username";
try {
$stmt = $this->pdo->prepare($sql);
if ($pat) $stmt->execute(array(':username'=>$pat)); // do we really need a "pattern" or is username enuf?
else $stmt->execute();
} catch (PDOException $e) {
$this->dblog("ListUsers(): Error SQL=$sql ERROR=".$e->getMessage());
}
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
#echo "DEBUG: ListUsers($pat): rows=<pre>".print_r($rows,true)."</pre><br />\n";
$rtn = array();
foreach ($rows as $row)
$rtn[] = $row['username'];
return $rtn;
}
############################################################################
## ReadGroups()
## with username: returns array('@groupA', '@groupB', ...)
## w/o username: returns array('@groupA' => array('userA', 'userB', ...), '@groupB' => ...)
############################################################################
function ReadGroups($username=NULL, $recursive=false)
{
#echo "DEBUG: ReadGroups($username, $recursive): Entering.<br />\n";
if ($username) {
$sql = "SELECT groupname
FROM ".$this->dbtablename('group_members')."
WHERE username = :username";
try {
$stmt = $this->pdo->prepare($sql);
$stmt->execute(array(':username'=>$username));
} catch (PDOException $e) {
$this->dblog("ReadGroups(): Error SQL=$sql ERROR=".$e->getMessage());
}
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$grouplist = array();
foreach($rows as $row) {
if (!in_array($row['groupname'], $grouplist)) {
$grouplist[] = $row['groupname'];
if ($recursive)
$this->NestedGroupParents($row['groupname'], $grouplist);
}
}
#echo "DEBUG: ReadGroups(): Returning grouplist=<pre>".print_r($grouplist,true)."</pre><br />\n";
return $grouplist;
} else {
$sql = "SELECT groupname
FROM ".$this->dbtablename('groups');
$rtn = array();
foreach ($this->pdo->query($sql) as $row) {
$members = array();
$rtn[$row['groupname']] = $this->NestedGroupUsers($row['groupname'], $members, $recursive);
}
#echo "DEBUG: ReadGroups(): Returning rtn=<pre>".print_r($rtn,true)."</pre><br />\n";
return $rtn;
}
}
function NestedGroupUsers($groupname, &$grouplist, $recursive=false)
{
#echo "DEBUG: NestedGroupUsers($groupname): Entering<br />\n";
$sql = "SELECT username, user_is_group
FROM ".$this->dbtablename('group_members')."
WHERE groupname = :groupname";
try {
$stmt = $this->pdo->prepare($sql);
$stmt->execute(array(':groupname'=>$groupname));
} catch (PDOException $e) {
$this->dblog("NestedGroupUsers(): Error SQL=$sql ERROR=".$e->getMessage());
}
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
#echo "DEBUG: NGU($groupname): rows=<pre>".print_r($rows,true)."</pre><br />\n";
$rtn = array();
foreach($rows as $row) {
if ($recursive) {
if ($row['user_is_group']) {
if (!in_array($row['username'], $grouplist)) {
$grouplist[] = $row['username'];
$rtn = array_merge($rtn, $this->NestedGroupUsers($row['username'], $grouplist, $recursive));
}
} else {
if (!in_array($row['username'], $rtn))
$rtn[] = $row['username'];
}
} else {
if (!in_array($row['username'], $rtn))
$rtn[] = $row['username'];
}
}
#echo "DEBUG: recursive=<pre>".print_r($recursive,true)."</pre><br />\n";
#echo "DEBUG: NGU($groupname): Returning <pre>".print_r($rtn,true)."</pre><br />\n";
return $rtn;
}
function NestedGroupParents($groupname, &$grouplist)
{
$sql = "SELECT groupname
FROM ".$this->dbtablename('group_members')."
WHERE username = :groupname
AND user_is_group = 1";
try {
$stmt = $this->pdo->prepare($sql);
$stmt->execute(array(':groupname'=>$groupname));
} catch (PDOException $e) {
$this->dblog("NestedGroupParents(): Error SQL=$sql ERROR=".$e->getMessage());
}
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($rows as $row) {
if (!in_array($row['groupname'], $grouplist)) {
$grouplist[] = $row['groupname'];
$this->NestedGroupParents($row['groupname'], $grouplist);
}
}
}
## $add is an array of members to add to group
## $del is an array of members to remove from group
function WriteGroup($groupname, $add, $del, $csum='', $auth='read')
{
$rtn = true;
#echo "DEBUG: add=<pre>".print_r($add,true)."</pre><br />\n";
#echo "DEBUG: del=<pre>".print_r($del,true)."</pre><br />\n";
#
# First check to make sure the group actually exists and, if not, insert it
#
$sql = "SELECT * FROM ".$this->dbtablename('groups')."
WHERE groupname = :groupname";
try {
$stmt = $this->pdo->prepare($sql);
$stmt->execute(array(':groupname'=>$groupname));
$groupexists = $stmt->fetch(PDO::FETCH_ASSOC);
$stmt->closeCursor();
} catch (PDOException $e) {
$this->dblog("WriteGroup(chkexist): Error SQL=$sql ERROR=".$e->getMessage());
$rtn = false;
}
if ($rtn && !$groupexists) {
$sql = "INSERT INTO ".$this->dbtablename('groups')."
(groupname) VALUES (:groupname)";
try {
$stmt = $this->pdo->prepare($sql);
$stmt->execute(array(':groupname'=>$groupname));
} catch (PDOException $e) {
$this->dblog("WriteGroup(addgroup): Error SQL=$sql ERROR=".$e->getMessage());
$rtn = false;
}
}
#
# Now delete from and/or insert into group_members as appropriate
#
if ($del) {
$sql = "DELETE FROM ".$this->dbtablename('group_members')."
WHERE groupname = :groupname
AND username = :username";
try {
$stmt = $this->pdo->prepare($sql);
foreach ($del as $d) {
#echo "DEBUG: Deleting groupname=$groupname, username=$d<br />\n";
$stmt->execute(array(':groupname'=>$groupname, ':username'=>$d));
}
} catch (PDOException $e) {
$this->dblog("WriteGroup(del): Error SQL=$sql ERROR=".$e->getMessage());
$rtn = false;
}
}
if ($add) {
$sql = "INSERT INTO ".$this->dbtablename('group_members')."
( groupname, username, user_is_group )
VALUES (:groupname, :username, IF(SUBSTR(:username2,1,1)='@', 1, 0))";
try {
$stmt = $this->pdo->prepare($sql);
foreach ($add as $a) {
#echo "DEBUG: Adding groupname=$groupname, username=$d<br />\n";
$stmt->execute(array(':groupname'=>$groupname, ':username'=>$a, ':username2'=>$a));
}
} catch (PDOException $e) {
$this->dblog("WriteGroup(add): Error SQL=$sql ERROR=".$e->getMessage());
$rtn = false;
}
}
return $rtn;
}
function HandleInstall($pagename)
{
if (!$this->Superuser($pagename)) {
echo "<h1>You do not have the necessary privileges. Aborting.</h1>\n";
exit(1);
}
/*************************
* DATABASE STRUCTURE:
*************************/
$prefix = $this->dbprefix;
$db_create_queries = array(
"CREATE TABLE `${prefix}users` (
`username` varchar(50) NOT NULL,
`userpwhash` varchar(100) NOT NULL,
`useremail` varchar(250) NOT NULL,
`userrealname` varchar(250) DEFAULT NULL,
`userfname` varchar(150) DEFAULT NULL,
`userlname` varchar(150) DEFAULT NULL,
`userkey` varchar(100) DEFAULT NULL,
`userkeyreason` varchar(100) DEFAULT NULL,
`userkeytime` int(15) DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8",
"CREATE TABLE `${prefix}groups` (
`groupname` varchar(50) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`groupname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8",
"CREATE TABLE `${prefix}group_members` (
`groupname` varchar(50) NOT NULL,
`username` varchar(50) NOT NULL,
`admin` tinyint(1) NOT NULL DEFAULT '0',
`user_is_group` tinyint(1) NOT NULL DEFAULT '0',
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`groupname`,`username`),
KEY `groupname` (`groupname`),
KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8",
"CREATE TABLE `${prefix}uadb` (
`do_I_exist` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8",
);
try {
foreach ($db_create_queries as $sql)
$this->pdo->exec($sql);
} catch (PDOException $e) {
$this->dblog("HandleInstall(): ERROR: SQL=$sql ".$e->getMessage());
}
echo "<h2>Success. Database Tables Created</h2>\n";
}
}
# This should be called from config.php (or farmconfig.php) AFTER including this file
#$UserAdmin = new UserAdminDbase('dbn', 'user', 'pass', array(options...), 'table_name_prefix');