xref: /plugin/dbquery/helper.php (revision 6648d9d9bf7f0c3c63f2d1b771cfac9eb978c2f7) 
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     * @param string $dsn
31     * @param string $user
32     * @param string $pass
33     * @return PDO
34     * @throws \PDOException
35     */
36    public function getPDO($dsn, $user, $pass)
37    {
38        $opts = [
39            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // always fetch as array
40            PDO::ATTR_EMULATE_PREPARES => true, // emulating prepares allows us to reuse param names
41            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // we want exceptions, not error codes
42        ];
43        $pdo = new PDO($dsn, $user, $pass, $opts);
44
45        return $pdo;
46    }
47
48    /**
49     * Opens a database connection, executes the query and returns the result
50     *
51     * @param string $query
52     * @return array
53     * @throws \PDOException
54     * @todo should we keep the DB connection around for subsequent queries?
55     * @todo should we allow SELECT queries only for additional security?
56     */
57    public function executeQuery($query)
58    {
59        $pdo = $this->getPDO(
60            $this->getConf('dsn'),
61            $this->getConf('user'),
62            conf_decodeString($this->getConf('pass'))
63        );
64
65        $params = $this->gatherVariables();
66        $sth = $this->prepareStatement($pdo, $query, $params);
67        $sth->execute();
68        $data = $sth->fetchAll(PDO::FETCH_ASSOC);
69        $sth->closeCursor();
70
71        return $data;
72    }
73
74    /**
75     * Generate a prepared statement with bound parameters
76     *
77     * @param PDO $pdo
78     * @param string $sql
79     * @param array $parameters
80     * @return PDOStatement
81     */
82    public function prepareStatement(\PDO $pdo, $sql, $parameters)
83    {
84        // prepare the groups
85        $cnt = 0;
86        $groupids = [];
87        foreach ($parameters[':groups'] as $group) {
88            $id = 'group' . $cnt++;
89            $parameters[$id] = $group;
90            $groupids[] = ":$id";
91        }
92        unset($parameters[':groups']);
93        $sql = str_replace(':groups', join(',', $groupids), $sql);
94
95        $sth = $pdo->prepare($sql);
96        foreach ($parameters as $key => $val) {
97            if (is_array($val)) continue;
98            if (is_object($val)) continue;
99            if (strpos($sql, $key) === false) continue; // skip if parameter is missing
100
101            if (is_int($val)) {
102                $sth->bindValue($key, $val, PDO::PARAM_INT);
103            } else {
104                $sth->bindValue($key, $val);
105            }
106        }
107
108        return $sth;
109    }
110
111    /**
112     * Get the standard replacement variables
113     *
114     * @return array
115     */
116    public function gatherVariables()
117    {
118        global $USERINFO;
119        global $INFO;
120        global $INPUT;
121
122        return [
123            ':user' => $INPUT->server->str('REMOTE_USER'),
124            ':mail' => $USERINFO['mail'] ?: '',
125            ':groups' => $USERINFO['grps'] ?: [],
126            ':id' => ':' . $INFO['id'],
127            ':page' => noNS($INFO['id']),
128            ':ns' => ':' . getNS($INFO['id']),
129        ];
130    }
131}
132