<?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-04-29';
SDV($AuthUserFunctions['useradmindb'], 'AuthUserDbase');
function AuthUserDbase($pagename, $id, $pw, $pwlist)
{
global $UserAdmin;
#echo "AUD(pagename=$pagename, id=$id, pw=$pw, pwlist=$pwlist): Entering.<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']))
return true;
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 $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)
{
$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 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);
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 ListUsers($pat=NULL)
{
$sql = "SELECT *
FROM ".$this->dbtablename('users');
if ($pat)
$sql .= " WHERE username = :username";
try {
$stmt = $this->pdo->prepare($sql);
if ($pat) $bindvals = array(':username'=>$pat); // do we really need a "pattern" or is username enuf?
else $bindvals = null;
$stmt->execute($bindvals);
} catch (PDOException $e) {
$this->dblog("ListUsers(): Error SQL=$sql ERROR=".$e->getMessage());
}
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $rows;
}
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 IF NOT EXISTS `${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 IF NOT EXISTS `${prefix}groups` (
`groupname` varchar(50) NOT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`groupname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8",
"CREATE TABLE IF NOT EXISTS `${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 IF NOT EXISTS `${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
#SDV($UserAdmin, new UserAdminDbase('host', 'user', 'pass', 'dbname'));