xref: /plugin/dbquery/helper.php (revision 6adcc9ad882f6bbeb8ab7f02858dfa793487e1a2) 
1<?php
2
3/**
4 * DokuWiki Plugin dbquery (Helper Component)
5 *
6 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
7 * @author  Andreas Gohr <dokuwiki@cosmocode.de>
8 */
9class helper_plugin_dbquery extends dokuwiki\Extension\Plugin
10{
11
12    /**
13     * @param string $name Page name of the query
14     * @return array
15     * @throws \Exception
16     */
17    public function loadCodeBlocksFromPage($name)
18    {
19
20        $name = cleanID($name);
21        $id = $this->getConf('namespace') . ':' . $name;
22        if (!page_exists($id)) throw new \Exception("No query named '$name' found");
23
24        $doc = p_cached_output(wikiFN($id), 'dbquery');
25
26        return json_decode($doc, true);
27    }
28
29    /**
30     * Opens a database connection, executes the query and returns the result
31     *
32     * @param string $query
33     * @return array
34     * @throws \PDOException
35     * @todo should we keep the DB connection around for subsequent queries?
36     * @todo should we allow SELECT queries only for additional security?
37     */
38    public function executeQuery($query)
39    {
40
41        $opts = [
42            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // always fetch as array
43            PDO::ATTR_EMULATE_PREPARES => true, // emulating prepares allows us to reuse param names
44            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // we want exceptions, not error codes
45        ];
46        $pdo = new PDO(
47            $this->getConf('dsn'),
48            $this->getConf('user'),
49            conf_decodeString($this->getConf('pass')),
50            $opts
51        );
52
53        $params = $this->gatherVariables();
54        $sth = $this->prepareStatement($pdo, $query, $params);
55        $sth->execute();
56        $data = $sth->fetchAll(PDO::FETCH_ASSOC);
57        $sth->closeCursor();
58
59        return $data;
60    }
61
62    /**
63     * Generate a prepared statement with bound parameters
64     *
65     * @param PDO $pdo
66     * @param string $sql
67     * @param string[] $parameters
68     * @return PDOStatement
69     */
70    public function prepareStatement(\PDO $pdo, $sql, $parameters)
71    {
72        $sth = $pdo->prepare($sql);
73
74        foreach ($parameters as $key => $val) {
75            if (is_array($val)) continue;
76            if (is_object($val)) continue;
77            if (strpos($sql, $key) === false) continue; // skip if parameter is missing
78
79            if (is_int($val)) {
80                $sth->bindValue($key, $val, PDO::PARAM_INT);
81            } else {
82                $sth->bindValue($key, $val);
83            }
84        }
85
86        return $sth;
87    }
88
89    /**
90     * Get the standard replacement variables
91     *
92     * @return array
93     */
94    public function gatherVariables()
95    {
96        global $USERINFO;
97        global $INFO;
98        global $INPUT;
99
100        // add leading colon
101        $id = ':' . $INFO['id'];
102        return [
103            ':user' => $INPUT->server->str('REMOTE_USER'),
104            ':mail' => $USERINFO['mail'] ?: '',
105            ':groups' => $USERINFO['grps'] ? join(',', $USERINFO['grps']) : '', //FIXME escaping correct???
106            ':id' => $id,
107            ':page' => noNS($id),
108            ':ns' => getNS($id), //FIXME check that leading colon exists
109        ];
110    }
111}
112