12e564e06SAndreas Gohr<?php 22e564e06SAndreas Gohr 32e564e06SAndreas Gohr/** 42e564e06SAndreas Gohr * DokuWiki Plugin dbquery (Helper Component) 52e564e06SAndreas Gohr * 62e564e06SAndreas Gohr * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html 72e564e06SAndreas Gohr * @author Andreas Gohr <dokuwiki@cosmocode.de> 82e564e06SAndreas Gohr */ 92e564e06SAndreas Gohrclass helper_plugin_dbquery extends dokuwiki\Extension\Plugin 102e564e06SAndreas Gohr{ 111a014136SAndreas Gohr /** @var PDO[] do not access directly, use getPDO instead */ 121a014136SAndreas Gohr protected $pdo = []; 132e564e06SAndreas Gohr 142e564e06SAndreas Gohr /** 152e564e06SAndreas Gohr * @param string $name Page name of the query 166adcc9adSAndreas Gohr * @return array 172e564e06SAndreas Gohr * @throws \Exception 182e564e06SAndreas Gohr */ 19*c64c1748SAndreas Gohr public function loadDataFromPage($name) 202e564e06SAndreas Gohr { 212e564e06SAndreas Gohr 222e564e06SAndreas Gohr $name = cleanID($name); 232e564e06SAndreas Gohr $id = $this->getConf('namespace') . ':' . $name; 242e564e06SAndreas Gohr if (!page_exists($id)) throw new \Exception("No query named '$name' found"); 252e564e06SAndreas Gohr 262e564e06SAndreas Gohr $doc = p_cached_output(wikiFN($id), 'dbquery'); 272e564e06SAndreas Gohr 286adcc9adSAndreas Gohr return json_decode($doc, true); 292e564e06SAndreas Gohr } 302e564e06SAndreas Gohr 312e564e06SAndreas Gohr /** 321a014136SAndreas Gohr * Return the PDO object and cache it for the request 331a014136SAndreas Gohr * 341a014136SAndreas Gohr * Connections data can be null to use the info from the config 351a014136SAndreas Gohr * 361a014136SAndreas Gohr * @param string|null $dsn 371a014136SAndreas Gohr * @param string|null $user 381a014136SAndreas Gohr * @param string|null $pass 396648d9d9SAndreas Gohr * @return PDO 406648d9d9SAndreas Gohr */ 411a014136SAndreas Gohr public function getPDO($dsn = null, $user = null, $pass = null) 426648d9d9SAndreas Gohr { 431a014136SAndreas Gohr $dsn = $dsn ?: $this->getConf('dsn'); 441a014136SAndreas Gohr $user = $user ?: $this->getConf('user'); 451a014136SAndreas Gohr $pass = $pass ?: conf_decodeString($this->getConf('pass')); 461a014136SAndreas Gohr $conid = md5($dsn . $user . $pass); 471a014136SAndreas Gohr 481a014136SAndreas Gohr if (isset($this->pdo[$conid])) return $this->pdo[$conid]; 491a014136SAndreas Gohr 506648d9d9SAndreas Gohr $opts = [ 516648d9d9SAndreas Gohr PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // always fetch as array 526648d9d9SAndreas Gohr PDO::ATTR_EMULATE_PREPARES => true, // emulating prepares allows us to reuse param names 536648d9d9SAndreas Gohr PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // we want exceptions, not error codes 546648d9d9SAndreas Gohr ]; 556648d9d9SAndreas Gohr 561a014136SAndreas Gohr $this->pdo[$conid] = new PDO($dsn, $user, $pass, $opts); 571a014136SAndreas Gohr return $this->pdo[$conid]; 586648d9d9SAndreas Gohr } 596648d9d9SAndreas Gohr 606648d9d9SAndreas Gohr /** 612e564e06SAndreas Gohr * Opens a database connection, executes the query and returns the result 622e564e06SAndreas Gohr * 632e564e06SAndreas Gohr * @param string $query 642e564e06SAndreas Gohr * @return array 652e564e06SAndreas Gohr * @throws \PDOException 66584c7810SAndreas Gohr * @throws Exception 672e564e06SAndreas Gohr */ 68be0b6729SAndreas Gohr public function executeQuery($query) 692e564e06SAndreas Gohr { 70584c7810SAndreas Gohr if (!preg_match('/^select /i', trim($query))) { 71584c7810SAndreas Gohr throw new \Exception('For security reasons only SELECT statements are allowed in dbquery'); 72584c7810SAndreas Gohr } 73584c7810SAndreas Gohr 741a014136SAndreas Gohr $pdo = $this->getPDO(); 75be0b6729SAndreas Gohr $params = $this->gatherVariables(); 76be0b6729SAndreas Gohr $sth = $this->prepareStatement($pdo, $query, $params); 77be0b6729SAndreas Gohr $sth->execute(); 782e564e06SAndreas Gohr $data = $sth->fetchAll(PDO::FETCH_ASSOC); 792e564e06SAndreas Gohr $sth->closeCursor(); 802e564e06SAndreas Gohr 812e564e06SAndreas Gohr return $data; 822e564e06SAndreas Gohr } 83be0b6729SAndreas Gohr 84be0b6729SAndreas Gohr /** 85be0b6729SAndreas Gohr * Generate a prepared statement with bound parameters 86be0b6729SAndreas Gohr * 87be0b6729SAndreas Gohr * @param PDO $pdo 88be0b6729SAndreas Gohr * @param string $sql 896648d9d9SAndreas Gohr * @param array $parameters 90be0b6729SAndreas Gohr * @return PDOStatement 91be0b6729SAndreas Gohr */ 92be0b6729SAndreas Gohr public function prepareStatement(\PDO $pdo, $sql, $parameters) 93be0b6729SAndreas Gohr { 946648d9d9SAndreas Gohr // prepare the groups 956648d9d9SAndreas Gohr $cnt = 0; 966648d9d9SAndreas Gohr $groupids = []; 976648d9d9SAndreas Gohr foreach ($parameters[':groups'] as $group) { 986648d9d9SAndreas Gohr $id = 'group' . $cnt++; 996648d9d9SAndreas Gohr $parameters[$id] = $group; 1006648d9d9SAndreas Gohr $groupids[] = ":$id"; 1016648d9d9SAndreas Gohr } 1026648d9d9SAndreas Gohr unset($parameters[':groups']); 1036648d9d9SAndreas Gohr $sql = str_replace(':groups', join(',', $groupids), $sql); 104be0b6729SAndreas Gohr 1056648d9d9SAndreas Gohr $sth = $pdo->prepare($sql); 106be0b6729SAndreas Gohr foreach ($parameters as $key => $val) { 107be0b6729SAndreas Gohr if (is_array($val)) continue; 108be0b6729SAndreas Gohr if (is_object($val)) continue; 109be0b6729SAndreas Gohr if (strpos($sql, $key) === false) continue; // skip if parameter is missing 110be0b6729SAndreas Gohr 111be0b6729SAndreas Gohr if (is_int($val)) { 112be0b6729SAndreas Gohr $sth->bindValue($key, $val, PDO::PARAM_INT); 113be0b6729SAndreas Gohr } else { 114be0b6729SAndreas Gohr $sth->bindValue($key, $val); 115be0b6729SAndreas Gohr } 116be0b6729SAndreas Gohr } 117be0b6729SAndreas Gohr 118be0b6729SAndreas Gohr return $sth; 119be0b6729SAndreas Gohr } 120be0b6729SAndreas Gohr 121be0b6729SAndreas Gohr /** 122be0b6729SAndreas Gohr * Get the standard replacement variables 123be0b6729SAndreas Gohr * 124be0b6729SAndreas Gohr * @return array 125be0b6729SAndreas Gohr */ 126be0b6729SAndreas Gohr public function gatherVariables() 127be0b6729SAndreas Gohr { 128be0b6729SAndreas Gohr global $USERINFO; 129be0b6729SAndreas Gohr global $INFO; 130be0b6729SAndreas Gohr global $INPUT; 131be0b6729SAndreas Gohr 132be0b6729SAndreas Gohr return [ 133be0b6729SAndreas Gohr ':user' => $INPUT->server->str('REMOTE_USER'), 1344515310dSAndreas Gohr ':mail' => $USERINFO['mail'] ?? '', 1354515310dSAndreas Gohr ':groups' => $USERINFO['grps'] ?? [], 1366648d9d9SAndreas Gohr ':id' => ':' . $INFO['id'], 1376648d9d9SAndreas Gohr ':page' => noNS($INFO['id']), 1386648d9d9SAndreas Gohr ':ns' => ':' . getNS($INFO['id']), 139be0b6729SAndreas Gohr ]; 140be0b6729SAndreas Gohr } 1412e564e06SAndreas Gohr} 142