1<?php
2
3use dokuwiki\Extension\Plugin;
4
5/**
6 * DokuWiki Plugin dbquery (Helper Component)
7 *
8 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html
9 * @author  Andreas Gohr <dokuwiki@cosmocode.de>
10 */
11class helper_plugin_dbquery extends Plugin
12{
13    /** @var PDO[] do not access directly, use getPDO instead */
14    protected $pdo = [];
15
16    /**
17     * @param string $name Page name of the query
18     * @return array
19     * @throws \Exception
20     */
21    public function loadDataFromPage($name)
22    {
23
24        $name = cleanID($name);
25        $id = $this->getConf('namespace') . ':' . $name;
26        if (!page_exists($id)) throw new \Exception("No query named '$name' found");
27
28        $doc = p_cached_output(wikiFN($id), 'dbquery');
29
30        return json_decode($doc, true);
31    }
32
33    /**
34     * Return the PDO object and cache it for the request
35     *
36     * Connections data can be null to use the info from the config
37     *
38     * @param string|null $dsn
39     * @param string|null $user
40     * @param string|null $pass
41     * @return PDO
42     */
43    public function getPDO($dsn = null, $user = null, $pass = null)
44    {
45        $dsn = $dsn ?: $this->getConf('dsn');
46        $user = $user ?: $this->getConf('user');
47        $pass = $pass ?: conf_decodeString($this->getConf('pass'));
48        $conid = md5($dsn . $user . $pass);
49
50        if (isset($this->pdo[$conid])) return $this->pdo[$conid];
51
52        $opts = [
53            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // always fetch as array
54            PDO::ATTR_EMULATE_PREPARES => true, // emulating prepares allows us to reuse param names
55            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // we want exceptions, not error codes
56        ];
57
58        $this->pdo[$conid] = new PDO($dsn, $user, $pass, $opts);
59        return $this->pdo[$conid];
60    }
61
62    /**
63     * Opens a database connection, executes the query and returns the result
64     *
65     * @param string $query
66     * @return array
67     * @throws \PDOException
68     * @throws Exception
69     */
70    public function executeQuery($query, $dsnalias = null)
71    {
72        if (!preg_match('/^select /i', trim($query))) {
73            throw new \Exception('For security reasons only SELECT statements are allowed in dbquery');
74        }
75
76        [$dsn, $user, $pass] = $this->getDSN($dsnalias);
77        $pdo = $this->getPDO($dsn, $user, $pass);
78        $params = $this->gatherVariables();
79        $sth = $this->prepareStatement($pdo, $query, $params);
80        $sth->execute();
81
82        $data = $sth->fetchAll(PDO::FETCH_ASSOC);
83        $sth->closeCursor();
84
85        return $data;
86    }
87
88    /**
89     * Generate a prepared statement with bound parameters
90     *
91     * @param PDO $pdo
92     * @param string $sql
93     * @param array $parameters
94     * @return PDOStatement
95     */
96    public function prepareStatement(\PDO $pdo, $sql, $parameters)
97    {
98        // prepare the groups
99        $cnt = 0;
100        $groupids = [];
101        foreach ($parameters[':groups'] as $group) {
102            $id = 'group' . $cnt++;
103            $parameters[$id] = $group;
104            $groupids[] = ":$id";
105        }
106        unset($parameters[':groups']);
107        $sql = str_replace(':groups', implode(',', $groupids), $sql);
108
109        $sth = $pdo->prepare($sql);
110        foreach ($parameters as $key => $val) {
111            if (is_array($val)) continue;
112            if (is_object($val)) continue;
113            if (!str_contains($sql, $key)) continue; // skip if parameter is missing
114
115            if (is_int($val)) {
116                $sth->bindValue($key, $val, PDO::PARAM_INT);
117            } else {
118                $sth->bindValue($key, $val);
119            }
120        }
121
122        return $sth;
123    }
124
125    /**
126     * Get the standard replacement variables
127     *
128     * @return array
129     */
130    public function gatherVariables()
131    {
132        global $USERINFO;
133        global $INFO;
134        global $INPUT;
135
136        return [
137            ':user' => $INPUT->server->str('REMOTE_USER'),
138            ':mail' => $USERINFO['mail'] ?? '',
139            ':groups' => $USERINFO['grps'] ?? [],
140            ':id' => ':' . $INFO['id'],
141            ':page' => noNS($INFO['id']),
142            ':ns' => ':' . getNS($INFO['id']),
143        ];
144    }
145
146    /**
147     * Get the DSN, user and pass for a given alias
148     *
149     * @param string|null $alias null for default
150     * @return [string, string, string] DSN, user, pass
151     * @throws Exception
152     */
153    public function getDSN($alias = null)
154    {
155        static $aliases = null;
156        if ($aliases === null) {
157            $aliases = $this->getDsnAliases(
158                $this->getConf('dsn'),
159                $this->getConf('user'),
160                $this->getConf('pass')
161            );
162        }
163
164        if ($aliases === []) throw new \Exception('No DSN aliases defined');
165
166        if ($alias === null || !isset($aliases[$alias])) {
167            $alias = '_';
168        }
169
170        return [$aliases[$alias]['dsn'], $aliases[$alias]['user'], $aliases[$alias]['pass']];
171    }
172
173    /**
174     * Load and parse the DSN configuration
175     *
176     * @param string $config
177     * @param string $defaultuser
178     * @param string $defaultpass
179     * @return array
180     */
181    protected function getDsnAliases($config, $defaultuser, $defaultpass)
182    {
183        $aliases = [];
184        $lines = explode("\n", $config);
185        foreach ($lines as $line) {
186            $line = trim($line);
187            if (!$line) continue;
188            $parts = preg_split('/\s+/', $line, 4);
189
190            if (count($parts) > 1) {
191                $aliases[$parts[0]] = [
192                    'dsn' => $parts[1],
193                    'user' => $parts[2] ?? $defaultuser,
194                    'pass' => $parts[3] ?? $defaultpass
195                ];
196            } else {
197                $parts = ['', $parts[0]];
198            }
199
200            if (!isset($aliases['_'])) {
201                $aliases['_'] = [
202                    'dsn' => $parts[1],
203                    'user' => $parts[2] ?? $defaultuser,
204                    'pass' => $parts[3] ?? $defaultpass
205                ];
206            }
207        }
208        return $aliases;
209    }
210}
211