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    /** @var PDO[] do not access directly, use getPDO instead */
12    protected $pdo = [];
13
14    /**
15     * @param string $name Page name of the query
16     * @return array
17     * @throws \Exception
18     */
19    public function loadDataFromPage($name)
20    {
21
22        $name = cleanID($name);
23        $id = $this->getConf('namespace') . ':' . $name;
24        if (!page_exists($id)) throw new \Exception("No query named '$name' found");
25
26        $doc = p_cached_output(wikiFN($id), 'dbquery');
27
28        return json_decode($doc, true);
29    }
30
31    /**
32     * Return the PDO object and cache it for the request
33     *
34     * Connections data can be null to use the info from the config
35     *
36     * @param string|null $dsn
37     * @param string|null $user
38     * @param string|null $pass
39     * @return PDO
40     */
41    public function getPDO($dsn = null, $user = null, $pass = null)
42    {
43        $dsn = $dsn ?: $this->getConf('dsn');
44        $user = $user ?: $this->getConf('user');
45        $pass = $pass ?: conf_decodeString($this->getConf('pass'));
46        $conid = md5($dsn . $user . $pass);
47
48        if (isset($this->pdo[$conid])) return $this->pdo[$conid];
49
50        $opts = [
51            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // always fetch as array
52            PDO::ATTR_EMULATE_PREPARES => true, // emulating prepares allows us to reuse param names
53            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // we want exceptions, not error codes
54        ];
55
56        $this->pdo[$conid] = new PDO($dsn, $user, $pass, $opts);
57        return $this->pdo[$conid];
58    }
59
60    /**
61     * Opens a database connection, executes the query and returns the result
62     *
63     * @param string $query
64     * @return array
65     * @throws \PDOException
66     * @throws Exception
67     */
68    public function executeQuery($query)
69    {
70        if (!preg_match('/^select /i', trim($query))) {
71            throw new \Exception('For security reasons only SELECT statements are allowed in dbquery');
72        }
73
74        $pdo = $this->getPDO();
75        $params = $this->gatherVariables();
76        $sth = $this->prepareStatement($pdo, $query, $params);
77        $sth->execute();
78        $data = $sth->fetchAll(PDO::FETCH_ASSOC);
79        $sth->closeCursor();
80
81        return $data;
82    }
83
84    /**
85     * Generate a prepared statement with bound parameters
86     *
87     * @param PDO $pdo
88     * @param string $sql
89     * @param array $parameters
90     * @return PDOStatement
91     */
92    public function prepareStatement(\PDO $pdo, $sql, $parameters)
93    {
94        // prepare the groups
95        $cnt = 0;
96        $groupids = [];
97        foreach ($parameters[':groups'] as $group) {
98            $id = 'group' . $cnt++;
99            $parameters[$id] = $group;
100            $groupids[] = ":$id";
101        }
102        unset($parameters[':groups']);
103        $sql = str_replace(':groups', join(',', $groupids), $sql);
104
105        $sth = $pdo->prepare($sql);
106        foreach ($parameters as $key => $val) {
107            if (is_array($val)) continue;
108            if (is_object($val)) continue;
109            if (strpos($sql, $key) === false) continue; // skip if parameter is missing
110
111            if (is_int($val)) {
112                $sth->bindValue($key, $val, PDO::PARAM_INT);
113            } else {
114                $sth->bindValue($key, $val);
115            }
116        }
117
118        return $sth;
119    }
120
121    /**
122     * Get the standard replacement variables
123     *
124     * @return array
125     */
126    public function gatherVariables()
127    {
128        global $USERINFO;
129        global $INFO;
130        global $INPUT;
131
132        return [
133            ':user' => $INPUT->server->str('REMOTE_USER'),
134            ':mail' => $USERINFO['mail'] ?? '',
135            ':groups' => $USERINFO['grps'] ?? [],
136            ':id' => ':' . $INFO['id'],
137            ':page' => noNS($INFO['id']),
138            ':ns' => ':' . getNS($INFO['id']),
139        ];
140    }
141}
142