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        global $conf;
46
47        $dsn = $dsn ?: $this->getConf('dsn');
48        $user = $user ?: $this->getConf('user');
49        $pass = $pass ?: conf_decodeString($this->getConf('pass'));
50        // placeholders for use with sqlite
51        $dsn = str_replace(
52            ['%DATA_DIR%', '%META_DIR%'],
53            [fullpath($conf['savedir']), fullpath($conf['metadir'])],
54            $dsn
55        );
56        $conid = md5($dsn . $user . $pass);
57
58        if (isset($this->pdo[$conid])) return $this->pdo[$conid];
59
60        $opts = [
61            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // always fetch as array
62            PDO::ATTR_EMULATE_PREPARES => true, // emulating prepares allows us to reuse param names
63            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // we want exceptions, not error codes
64        ];
65
66        $this->pdo[$conid] = new PDO($dsn, $user, $pass, $opts);
67        return $this->pdo[$conid];
68    }
69
70    /**
71     * Opens a database connection, executes the query and returns the result
72     *
73     * @param string $query
74     * @return array
75     * @throws \PDOException
76     * @throws Exception
77     */
78    public function executeQuery($query, $dsnalias = null)
79    {
80        if (!preg_match('/^select(\s|$)/i', trim($query))) {
81            throw new \Exception('For security reasons only SELECT statements are allowed in dbquery');
82        }
83
84        [$dsn, $user, $pass] = $this->getDSN($dsnalias);
85        $pdo = $this->getPDO($dsn, $user, $pass);
86        $params = $this->gatherVariables();
87        $sth = $this->prepareStatement($pdo, $query, $params);
88        $sth->execute();
89
90        $data = $sth->fetchAll(PDO::FETCH_ASSOC);
91        $sth->closeCursor();
92
93        return $data;
94    }
95
96    /**
97     * Generate a prepared statement with bound parameters
98     *
99     * @param PDO $pdo
100     * @param string $sql
101     * @param array $parameters
102     * @return PDOStatement
103     */
104    public function prepareStatement(\PDO $pdo, $sql, $parameters)
105    {
106        // prepare the groups
107        $cnt = 0;
108        $groupids = [];
109        foreach ($parameters[':groups'] as $group) {
110            $id = 'group' . $cnt++;
111            $parameters[$id] = $group;
112            $groupids[] = ":$id";
113        }
114        unset($parameters[':groups']);
115        $sql = str_replace(':groups', implode(',', $groupids), $sql);
116
117        $sth = $pdo->prepare($sql);
118        foreach ($parameters as $key => $val) {
119            if (is_array($val)) continue;
120            if (is_object($val)) continue;
121            if (!str_contains($sql, $key)) continue; // skip if parameter is missing
122
123            if (is_int($val)) {
124                $sth->bindValue($key, $val, PDO::PARAM_INT);
125            } else {
126                $sth->bindValue($key, $val);
127            }
128        }
129
130        return $sth;
131    }
132
133    /**
134     * Get the standard replacement variables
135     *
136     * @return array
137     */
138    public function gatherVariables()
139    {
140        global $USERINFO;
141        global $INFO;
142        global $INPUT;
143
144        return [
145            ':user' => $INPUT->server->str('REMOTE_USER'),
146            ':mail' => $USERINFO['mail'] ?? '',
147            ':groups' => $USERINFO['grps'] ?? [],
148            ':id' => ':' . $INFO['id'],
149            ':page' => noNS($INFO['id']),
150            ':ns' => ':' . getNS($INFO['id']),
151        ];
152    }
153
154    /**
155     * Get the DSN, user and pass for a given alias
156     *
157     * @param string|null $alias null for default
158     * @return [string, string, string] DSN, user, pass
159     * @throws Exception
160     */
161    public function getDSN($alias = null)
162    {
163        static $aliases = null;
164        if ($aliases === null) {
165            $aliases = $this->getDsnAliases(
166                $this->getConf('dsn'),
167                $this->getConf('user'),
168                $this->getConf('pass')
169            );
170        }
171
172        if ($aliases === []) throw new \Exception('No DSN aliases defined');
173
174        if ($alias === null || !isset($aliases[$alias])) {
175            $alias = '_';
176        }
177
178        return [$aliases[$alias]['dsn'], $aliases[$alias]['user'], $aliases[$alias]['pass']];
179    }
180
181    /**
182     * Load and parse the DSN configuration
183     *
184     * @param string $config
185     * @param string $defaultuser
186     * @param string $defaultpass
187     * @return array
188     */
189    protected function getDsnAliases($config, $defaultuser, $defaultpass)
190    {
191        $aliases = [];
192        $lines = explode("\n", $config);
193        foreach ($lines as $line) {
194            $line = trim($line);
195            if (!$line) continue;
196            $parts = preg_split('/\s+/', $line, 4);
197
198            if (count($parts) > 1) {
199                $aliases[$parts[0]] = [
200                    'dsn' => $parts[1],
201                    'user' => $parts[2] ?? $defaultuser,
202                    'pass' => $parts[3] ?? $defaultpass
203                ];
204            } else {
205                $parts = ['', $parts[0]];
206            }
207
208            if (!isset($aliases['_'])) {
209                $aliases['_'] = [
210                    'dsn' => $parts[1],
211                    'user' => $parts[2] ?? $defaultuser,
212                    'pass' => $parts[3] ?? $defaultpass
213                ];
214            }
215        }
216        return $aliases;
217    }
218}
219