1<?php
2// must be run within Dokuwiki
3if(!defined('DOKU_INC')) die();
4
5/**
6 * SQLite authentication backend
7 *
8 * This plugin is more or less authpgsql with the serial numbers filed
9 * off and SQLite functions used instead of PostgreSQL
10 *
11 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
12 * @author     Clay Dowling <clay@lazarusid.com>
13 * ----------- Authors of the PgSQL and MySQL originals ------------
14 * @author     Andreas Gohr <andi@splitbrain.org>
15 * @author     Chris Smith <chris@jalakai.co.uk>
16 * @author     Matthias Grimm <matthias.grimmm@sourceforge.net>
17 * @author     Jan Schumann <js@schumann-it.com>
18 */
19class auth_plugin_authsqlite extends auth_plugin_authmysql {
20
21    /**
22     * Constructor
23     *
24     * checks if the sqlite interface is available, otherwise it will
25     * set the variable $success of the basis class to false
26     *
27     * @author Clay Dowling <clay@lazarusid.com>
28     * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
29     * @author Andreas Gohr <andi@splitbrain.org>
30     */
31    public function __construct() {
32        // we don't want the stuff the MySQL constructor does, but the grandparent might do something
33        DokuWiki_Auth_Plugin::__construct();
34
35        $this->loadConfig();
36
37        // set capabilities based upon config strings set
38	if(empty($this->conf['database'])) {
39	    echo "Insufficient Config!";
40            $this->_debug("SQLite err: insufficient configuration.", -1, __LINE__, __FILE__);
41            $this->success = false;
42            return;
43        }
44
45        $this->cando['addUser']   = $this->_chkcnf(
46            array(
47                 'getUserInfo',
48                 'getGroups',
49                 'addUser',
50                 'getUserID',
51                 'getGroupID',
52                 'addGroup',
53                 'addUserGroup'
54            )
55        );
56        $this->cando['delUser']   = $this->_chkcnf(
57            array(
58                 'getUserID',
59                 'delUser',
60                 'delUserRefs'
61            )
62        );
63        $this->cando['modLogin']  = $this->_chkcnf(
64            array(
65                 'getUserID',
66                 'updateUser',
67                 'UpdateTarget'
68            )
69        );
70        $this->cando['modPass']   = $this->cando['modLogin'];
71        $this->cando['modName']   = $this->cando['modLogin'];
72        $this->cando['modMail']   = $this->cando['modLogin'];
73        $this->cando['modGroups'] = $this->_chkcnf(
74            array(
75                 'getUserID',
76                 'getGroups',
77                 'getGroupID',
78                 'addGroup',
79                 'addUserGroup',
80                 'delGroup',
81                 'getGroupID',
82                 'delUserGroup'
83            )
84        );
85        /* getGroups is not yet supported
86           $this->cando['getGroups']    = $this->_chkcnf(array('getGroups',
87           'getGroupID')); */
88        $this->cando['getUsers']     = $this->_chkcnf(
89            array(
90                 'getUsers',
91                 'getUserInfo',
92                 'getGroups'
93            )
94        );
95        $this->cando['getUserCount'] = $this->_chkcnf(array('getUsers'));
96	$this->success = true;
97    }
98
99    /**
100     * Check if the given config strings are set
101     *
102     * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
103     *
104     * @param   string[] $keys
105     * @param   bool  $wop
106     * @return  bool
107     */
108    protected function _chkcnf($keys, $wop = false) {
109        foreach($keys as $key) {
110            if(empty($this->conf[$key])) return false;
111        }
112        return true;
113    }
114
115    /**
116     * Counts users which meet certain $filter criteria.
117     *
118     * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
119     *
120     * @param  array  $filter  filter criteria in item/pattern pairs
121     * @return int count of found users.
122     */
123    public function getUserCount($filter = array()) {
124        $rc = 0;
125
126        if($this->_openDB()) {
127            $sql = $this->_createSQLFilter($this->conf['getUsers'], $filter);
128
129            // no equivalent of SQL_CALC_FOUND_ROWS in pgsql?
130            if(($result = $this->_queryDB($sql))) {
131                $rc = count($result);
132            }
133            $this->_closeDB();
134        }
135        return $rc;
136    }
137
138    /**
139     * Bulk retrieval of user data
140     *
141     * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
142     *
143     * @param   int   $first     index of first user to be returned
144     * @param   int   $limit     max number of users to be returned
145     * @param   array $filter    array of field/pattern pairs
146     * @return  array userinfo (refer getUserData for internal userinfo details)
147     */
148    public function retrieveUsers($first = 0, $limit = 0, $filter = array()) {
149        $out = array();
150
151        if($this->_openDB()) {
152            $this->_lockTables("READ");
153            $sql = $this->_createSQLFilter($this->conf['getUsers'], $filter);
154            $sql .= " ".$this->conf['SortOrder'];
155            if($limit) $sql .= " LIMIT $limit";
156            if($first) $sql .= " OFFSET $first";
157            $result = $this->_queryDB($sql);
158
159            foreach($result as $user) {
160                if(($info = $this->_getUserInfo($user['user']))) {
161                    $out[$user['user']] = $info;
162                }
163            }
164
165            $this->_unlockTables();
166            $this->_closeDB();
167        }
168        return $out;
169    }
170
171    // @inherit function joinGroup($user, $group)
172    // @inherit function leaveGroup($user, $group) {
173
174    /**
175     * Adds a user to a group.
176     *
177     * If $force is set to true non existing groups would be created.
178     *
179     * The database connection must already be established. Otherwise
180     * this function does nothing and returns 'false'.
181     *
182     * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
183     * @author Andreas Gohr   <andi@splitbrain.org>
184     *
185     * @param   string $user    user to add to a group
186     * @param   string $group   name of the group
187     * @param   bool   $force   create missing groups
188     * @return  bool   true on success, false on error
189     */
190    protected function _addUserToGroup($user, $group, $force = false) {
191        $newgroup = 0;
192
193        if(($this->dbcon) && ($user)) {
194            $gid = $this->_getGroupID($group);
195            if(!$gid) {
196                if($force) { // create missing groups
197                    $sql = str_replace('%{group}', addslashes($group), $this->conf['addGroup']);
198                    $this->_modifyDB($sql);
199                    //group should now exists try again to fetch it
200                    $gid      = $this->_getGroupID($group);
201                    $newgroup = 1; // group newly created
202                }
203            }
204            if(!$gid) return false; // group didn't exist and can't be created
205
206            $sql = $this->conf['addUserGroup'];
207            if(strpos($sql, '%{uid}') !== false) {
208                $uid = $this->_getUserID($user);
209                $sql = str_replace('%{uid}', addslashes($uid), $sql);
210	    }
211            $sql = str_replace('%{user}', addslashes($user), $sql);
212            $sql = str_replace('%{gid}', addslashes($gid), $sql);
213            $sql = str_replace('%{group}', addslashes($group), $sql);
214            if($this->_modifyDB($sql) !== false) {
215                $this->_flushUserInfoCache($user);
216                return true;
217            }
218
219            if($newgroup) { // remove previously created group on error
220                $sql = str_replace('%{gid}', addslashes($gid), $this->conf['delGroup']);
221                $sql = str_replace('%{group}', addslashes($group), $sql);
222                $this->_modifyDB($sql);
223            }
224        }
225        return false;
226    }
227
228    // @inherit function _delUserFromGroup($user $group)
229    // @inherit function _getGroups($user)
230    // @inherit function _getUserID($user)
231
232    /**
233     * Adds a new User to the database.
234     *
235     * The database connection must already be established
236     * for this function to work. Otherwise it will return
237     * 'false'.
238     *
239     * @param  string $user  login of the user
240     * @param  string $pwd   encrypted password
241     * @param  string $name  full name of the user
242     * @param  string $mail  email address
243     * @param  array  $grps  array of groups the user should become member of
244     * @return bool
245     *
246     * @author  Andreas Gohr <andi@splitbrain.org>
247     * @author  Chris Smith <chris@jalakai.co.uk>
248     * @author  Matthias Grimm <matthiasgrimm@users.sourceforge.net>
249     */
250    protected function _addUser($user, $pwd, $name, $mail, $grps) {
251        if($this->dbcon && is_array($grps)) {
252            $sql = str_replace('%{user}', addslashes($user), $this->conf['addUser']);
253            $sql = str_replace('%{pass}', addslashes($pwd), $sql);
254            $sql = str_replace('%{name}', addslashes($name), $sql);
255            $sql = str_replace('%{email}', addslashes($mail), $sql);
256            if($this->_modifyDB($sql)) {
257                $uid = $this->_getUserID($user);
258            } else {
259                return false;
260            }
261
262            $group = '';
263            $gid = false;
264
265            if($uid) {
266                foreach($grps as $group) {
267                    $gid = $this->_addUserToGroup($user, $group, true);
268                    if($gid === false) break;
269                }
270
271                if($gid !== false){
272                    $this->_flushUserInfoCache($user);
273                    return true;
274                } else {
275                    /* remove the new user and all group relations if a group can't
276                     * be assigned. Newly created groups will remain in the database
277                     * and won't be removed. This might create orphaned groups but
278                     * is not a big issue so we ignore this problem here.
279                     */
280                    $this->_delUser($user);
281                    $this->_debug("PgSQL err: Adding user '$user' to group '$group' failed.", -1, __LINE__, __FILE__);
282                }
283            }
284        }
285        return false;
286    }
287
288    /**
289     * Opens a connection to a database and saves the handle for further
290     * usage in the object. The successful call to this functions is
291     * essential for most functions in this object.
292     *
293     * @author Clay Dowling <clay@lazarusid.com>
294     * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
295     *
296     * @return bool
297     */
298    protected function _openDB() {
299        if(!$this->dbcon) {
300	    $errormsg = '';
301	    $con = new SQLite3($this->conf['database']);
302            if($con) {
303                $this->dbcon = $con;
304                return true; // connection and database successfully opened
305            } else {
306                $this->_debug($errormsg);
307            }
308            return false; // connection failed
309        }
310        return true; // connection already open
311    }
312
313    /**
314     * Closes a database connection.
315     *
316     * @author Clay Dowling <clay@lazarusid.com>
317     * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
318     */
319    protected function _closeDB() {
320        if($this->dbcon) {
321	    $this->dbcon->close();
322            $this->dbcon = 0;
323        }
324    }
325
326    /**
327     * Substitue any %{animal} parameters in the SQL, so that users
328     * and groups can be animal specific in a farm configuration
329     */
330    protected function _substituteAnimal($query)
331    {
332	if (isset($GLOBALS['FARMCORE'])) {
333	    $query = str_replace('%{animal}', addslashes($GLOBALS['FARMCORE']->getAnimal()), $query);
334	}
335        else if(defined('DOKU_FARM') && strpos($query, '%{animal}') !== false) {
336	    $parts = split('/', DOKU_CONF);
337	    $animal = '';
338	    $len = count($parts);
339	    for ($i=$len - 1; $i > 0; $i--) {
340	        if ($parts[$i] == 'conf' && $i > 0) {
341		    $animal = $parts[$i - 1];
342	        }
343	    }
344	    $query = str_replace('%{animal}', addslashes($animal), $query);
345        }
346	return $query;
347    }
348
349    /**
350     * Sends a SQL query to the database and transforms the result into
351     * an associative array.
352     *
353     * This function is only able to handle queries that returns a
354     * table such as SELECT.
355     *
356     * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
357     *
358     * @param  string $query  SQL string that contains the query
359     * @return array|false the result table
360     */
361    protected function _queryDB($query) {
362        $resultarray = array();
363        if($this->dbcon) {
364	    $query = $this->_substituteAnimal($query);
365            $result = $this->dbcon->query($query);
366            if($result) {
367                while(($t = $result->fetchArray(SQLITE3_ASSOC)) !== false) {
368                    $resultarray[] = $t;
369		}
370                return $resultarray;
371            } else{
372                $this->_debug('SQLite err: '. $this->dbcon->lastErrorMsg(), -1, __LINE__, __FILE__);
373            }
374	}
375        return false;
376    }
377
378    /**
379     * Executes an update or insert query. This differs from the
380     * MySQL one because it does NOT return the last insertID
381     *
382     * @author Clay Dowling <clay@lazarusid.com>
383     * @author Andreas Gohr <andi@splitbrain.org>
384     *
385     * @param string $query
386     * @return bool
387     */
388    protected function _modifyDB($query) {
389        if($this->dbcon) {
390	    $query = $this->_substituteAnimal($query);
391            $result = $this->dbcon->exec($query);
392            if($result) {
393                return true;
394            }
395            $this->_debug('SQLite err: '. $this->dbcon->lastErrorMsg(), -1, __LINE__, __FILE__);
396        }
397        return false;
398    }
399
400    /**
401     * Start a transaction
402     *
403     * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
404     *
405     * @param string $mode  could be 'READ' or 'WRITE'
406     * @return bool
407     */
408    protected function _lockTables($mode) {
409        if($this->dbcon) {
410            $this->_modifyDB('BEGIN');
411            return true;
412        }
413        return false;
414    }
415
416    /**
417     * Commit a transaction
418     *
419     * @author Matthias Grimm <matthiasgrimm@users.sourceforge.net>
420     *
421     * @return bool
422     */
423    protected function _unlockTables() {
424        if($this->dbcon) {
425            $this->_modifyDB('COMMIT');
426            return true;
427        }
428        return false;
429    }
430
431    /**
432     * Escape a string for insertion into the database
433     *
434     * @author Andreas Gohr <andi@splitbrain.org>
435     *
436     * @param  string  $string The string to escape
437     * @param  bool    $like   Escape wildcard chars as well?
438     * @return string
439     */
440    protected function _escape($string, $like = false) {
441        $string = $this->dbcon->escapeString($string);
442        if($like) {
443            $string = addcslashes($string, '%_');
444        }
445        return $string;
446    }
447}
448