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{ 11*1a014136SAndreas Gohr /** @var PDO[] do not access directly, use getPDO instead */ 12*1a014136SAndreas 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 */ 196adcc9adSAndreas Gohr public function loadCodeBlocksFromPage($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 /** 32*1a014136SAndreas Gohr * Return the PDO object and cache it for the request 33*1a014136SAndreas Gohr * 34*1a014136SAndreas Gohr * Connections data can be null to use the info from the config 35*1a014136SAndreas Gohr * 36*1a014136SAndreas Gohr * @param string|null $dsn 37*1a014136SAndreas Gohr * @param string|null $user 38*1a014136SAndreas Gohr * @param string|null $pass 396648d9d9SAndreas Gohr * @return PDO 406648d9d9SAndreas Gohr */ 41*1a014136SAndreas Gohr public function getPDO($dsn = null, $user = null, $pass = null) 426648d9d9SAndreas Gohr { 43*1a014136SAndreas Gohr $dsn = $dsn ?: $this->getConf('dsn'); 44*1a014136SAndreas Gohr $user = $user ?: $this->getConf('user'); 45*1a014136SAndreas Gohr $pass = $pass ?: conf_decodeString($this->getConf('pass')); 46*1a014136SAndreas Gohr $conid = md5($dsn . $user . $pass); 47*1a014136SAndreas Gohr 48*1a014136SAndreas Gohr if (isset($this->pdo[$conid])) return $this->pdo[$conid]; 49*1a014136SAndreas 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 56*1a014136SAndreas Gohr $this->pdo[$conid] = new PDO($dsn, $user, $pass, $opts); 57*1a014136SAndreas 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 662e564e06SAndreas Gohr * @todo should we allow SELECT queries only for additional security? 672e564e06SAndreas Gohr */ 68be0b6729SAndreas Gohr public function executeQuery($query) 692e564e06SAndreas Gohr { 70*1a014136SAndreas Gohr $pdo = $this->getPDO(); 71be0b6729SAndreas Gohr $params = $this->gatherVariables(); 72be0b6729SAndreas Gohr $sth = $this->prepareStatement($pdo, $query, $params); 73be0b6729SAndreas Gohr $sth->execute(); 742e564e06SAndreas Gohr $data = $sth->fetchAll(PDO::FETCH_ASSOC); 752e564e06SAndreas Gohr $sth->closeCursor(); 762e564e06SAndreas Gohr 772e564e06SAndreas Gohr return $data; 782e564e06SAndreas Gohr } 79be0b6729SAndreas Gohr 80be0b6729SAndreas Gohr /** 81be0b6729SAndreas Gohr * Generate a prepared statement with bound parameters 82be0b6729SAndreas Gohr * 83be0b6729SAndreas Gohr * @param PDO $pdo 84be0b6729SAndreas Gohr * @param string $sql 856648d9d9SAndreas Gohr * @param array $parameters 86be0b6729SAndreas Gohr * @return PDOStatement 87be0b6729SAndreas Gohr */ 88be0b6729SAndreas Gohr public function prepareStatement(\PDO $pdo, $sql, $parameters) 89be0b6729SAndreas Gohr { 906648d9d9SAndreas Gohr // prepare the groups 916648d9d9SAndreas Gohr $cnt = 0; 926648d9d9SAndreas Gohr $groupids = []; 936648d9d9SAndreas Gohr foreach ($parameters[':groups'] as $group) { 946648d9d9SAndreas Gohr $id = 'group' . $cnt++; 956648d9d9SAndreas Gohr $parameters[$id] = $group; 966648d9d9SAndreas Gohr $groupids[] = ":$id"; 976648d9d9SAndreas Gohr } 986648d9d9SAndreas Gohr unset($parameters[':groups']); 996648d9d9SAndreas Gohr $sql = str_replace(':groups', join(',', $groupids), $sql); 100be0b6729SAndreas Gohr 1016648d9d9SAndreas Gohr $sth = $pdo->prepare($sql); 102be0b6729SAndreas Gohr foreach ($parameters as $key => $val) { 103be0b6729SAndreas Gohr if (is_array($val)) continue; 104be0b6729SAndreas Gohr if (is_object($val)) continue; 105be0b6729SAndreas Gohr if (strpos($sql, $key) === false) continue; // skip if parameter is missing 106be0b6729SAndreas Gohr 107be0b6729SAndreas Gohr if (is_int($val)) { 108be0b6729SAndreas Gohr $sth->bindValue($key, $val, PDO::PARAM_INT); 109be0b6729SAndreas Gohr } else { 110be0b6729SAndreas Gohr $sth->bindValue($key, $val); 111be0b6729SAndreas Gohr } 112be0b6729SAndreas Gohr } 113be0b6729SAndreas Gohr 114be0b6729SAndreas Gohr return $sth; 115be0b6729SAndreas Gohr } 116be0b6729SAndreas Gohr 117be0b6729SAndreas Gohr /** 118be0b6729SAndreas Gohr * Get the standard replacement variables 119be0b6729SAndreas Gohr * 120be0b6729SAndreas Gohr * @return array 121be0b6729SAndreas Gohr */ 122be0b6729SAndreas Gohr public function gatherVariables() 123be0b6729SAndreas Gohr { 124be0b6729SAndreas Gohr global $USERINFO; 125be0b6729SAndreas Gohr global $INFO; 126be0b6729SAndreas Gohr global $INPUT; 127be0b6729SAndreas Gohr 128be0b6729SAndreas Gohr return [ 129be0b6729SAndreas Gohr ':user' => $INPUT->server->str('REMOTE_USER'), 130be0b6729SAndreas Gohr ':mail' => $USERINFO['mail'] ?: '', 1316648d9d9SAndreas Gohr ':groups' => $USERINFO['grps'] ?: [], 1326648d9d9SAndreas Gohr ':id' => ':' . $INFO['id'], 1336648d9d9SAndreas Gohr ':page' => noNS($INFO['id']), 1346648d9d9SAndreas Gohr ':ns' => ':' . getNS($INFO['id']), 135be0b6729SAndreas Gohr ]; 136be0b6729SAndreas Gohr } 1372e564e06SAndreas Gohr} 138