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{ 112e564e06SAndreas Gohr 122e564e06SAndreas Gohr /** 132e564e06SAndreas Gohr * @param string $name Page name of the query 146adcc9adSAndreas Gohr * @return array 152e564e06SAndreas Gohr * @throws \Exception 162e564e06SAndreas Gohr */ 176adcc9adSAndreas Gohr public function loadCodeBlocksFromPage($name) 182e564e06SAndreas Gohr { 192e564e06SAndreas Gohr 202e564e06SAndreas Gohr $name = cleanID($name); 212e564e06SAndreas Gohr $id = $this->getConf('namespace') . ':' . $name; 222e564e06SAndreas Gohr if (!page_exists($id)) throw new \Exception("No query named '$name' found"); 232e564e06SAndreas Gohr 242e564e06SAndreas Gohr $doc = p_cached_output(wikiFN($id), 'dbquery'); 252e564e06SAndreas Gohr 266adcc9adSAndreas Gohr return json_decode($doc, true); 272e564e06SAndreas Gohr } 282e564e06SAndreas Gohr 292e564e06SAndreas Gohr /** 30*6648d9d9SAndreas Gohr * @param string $dsn 31*6648d9d9SAndreas Gohr * @param string $user 32*6648d9d9SAndreas Gohr * @param string $pass 33*6648d9d9SAndreas Gohr * @return PDO 34*6648d9d9SAndreas Gohr * @throws \PDOException 35*6648d9d9SAndreas Gohr */ 36*6648d9d9SAndreas Gohr public function getPDO($dsn, $user, $pass) 37*6648d9d9SAndreas Gohr { 38*6648d9d9SAndreas Gohr $opts = [ 39*6648d9d9SAndreas Gohr PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // always fetch as array 40*6648d9d9SAndreas Gohr PDO::ATTR_EMULATE_PREPARES => true, // emulating prepares allows us to reuse param names 41*6648d9d9SAndreas Gohr PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // we want exceptions, not error codes 42*6648d9d9SAndreas Gohr ]; 43*6648d9d9SAndreas Gohr $pdo = new PDO($dsn, $user, $pass, $opts); 44*6648d9d9SAndreas Gohr 45*6648d9d9SAndreas Gohr return $pdo; 46*6648d9d9SAndreas Gohr } 47*6648d9d9SAndreas Gohr 48*6648d9d9SAndreas Gohr /** 492e564e06SAndreas Gohr * Opens a database connection, executes the query and returns the result 502e564e06SAndreas Gohr * 512e564e06SAndreas Gohr * @param string $query 522e564e06SAndreas Gohr * @return array 532e564e06SAndreas Gohr * @throws \PDOException 542e564e06SAndreas Gohr * @todo should we keep the DB connection around for subsequent queries? 552e564e06SAndreas Gohr * @todo should we allow SELECT queries only for additional security? 562e564e06SAndreas Gohr */ 57be0b6729SAndreas Gohr public function executeQuery($query) 582e564e06SAndreas Gohr { 59*6648d9d9SAndreas Gohr $pdo = $this->getPDO( 60be0b6729SAndreas Gohr $this->getConf('dsn'), 61be0b6729SAndreas Gohr $this->getConf('user'), 62*6648d9d9SAndreas Gohr conf_decodeString($this->getConf('pass')) 63be0b6729SAndreas Gohr ); 64be0b6729SAndreas Gohr 65be0b6729SAndreas Gohr $params = $this->gatherVariables(); 66be0b6729SAndreas Gohr $sth = $this->prepareStatement($pdo, $query, $params); 67be0b6729SAndreas Gohr $sth->execute(); 682e564e06SAndreas Gohr $data = $sth->fetchAll(PDO::FETCH_ASSOC); 692e564e06SAndreas Gohr $sth->closeCursor(); 702e564e06SAndreas Gohr 712e564e06SAndreas Gohr return $data; 722e564e06SAndreas Gohr } 73be0b6729SAndreas Gohr 74be0b6729SAndreas Gohr /** 75be0b6729SAndreas Gohr * Generate a prepared statement with bound parameters 76be0b6729SAndreas Gohr * 77be0b6729SAndreas Gohr * @param PDO $pdo 78be0b6729SAndreas Gohr * @param string $sql 79*6648d9d9SAndreas Gohr * @param array $parameters 80be0b6729SAndreas Gohr * @return PDOStatement 81be0b6729SAndreas Gohr */ 82be0b6729SAndreas Gohr public function prepareStatement(\PDO $pdo, $sql, $parameters) 83be0b6729SAndreas Gohr { 84*6648d9d9SAndreas Gohr // prepare the groups 85*6648d9d9SAndreas Gohr $cnt = 0; 86*6648d9d9SAndreas Gohr $groupids = []; 87*6648d9d9SAndreas Gohr foreach ($parameters[':groups'] as $group) { 88*6648d9d9SAndreas Gohr $id = 'group' . $cnt++; 89*6648d9d9SAndreas Gohr $parameters[$id] = $group; 90*6648d9d9SAndreas Gohr $groupids[] = ":$id"; 91*6648d9d9SAndreas Gohr } 92*6648d9d9SAndreas Gohr unset($parameters[':groups']); 93*6648d9d9SAndreas Gohr $sql = str_replace(':groups', join(',', $groupids), $sql); 94be0b6729SAndreas Gohr 95*6648d9d9SAndreas Gohr $sth = $pdo->prepare($sql); 96be0b6729SAndreas Gohr foreach ($parameters as $key => $val) { 97be0b6729SAndreas Gohr if (is_array($val)) continue; 98be0b6729SAndreas Gohr if (is_object($val)) continue; 99be0b6729SAndreas Gohr if (strpos($sql, $key) === false) continue; // skip if parameter is missing 100be0b6729SAndreas Gohr 101be0b6729SAndreas Gohr if (is_int($val)) { 102be0b6729SAndreas Gohr $sth->bindValue($key, $val, PDO::PARAM_INT); 103be0b6729SAndreas Gohr } else { 104be0b6729SAndreas Gohr $sth->bindValue($key, $val); 105be0b6729SAndreas Gohr } 106be0b6729SAndreas Gohr } 107be0b6729SAndreas Gohr 108be0b6729SAndreas Gohr return $sth; 109be0b6729SAndreas Gohr } 110be0b6729SAndreas Gohr 111be0b6729SAndreas Gohr /** 112be0b6729SAndreas Gohr * Get the standard replacement variables 113be0b6729SAndreas Gohr * 114be0b6729SAndreas Gohr * @return array 115be0b6729SAndreas Gohr */ 116be0b6729SAndreas Gohr public function gatherVariables() 117be0b6729SAndreas Gohr { 118be0b6729SAndreas Gohr global $USERINFO; 119be0b6729SAndreas Gohr global $INFO; 120be0b6729SAndreas Gohr global $INPUT; 121be0b6729SAndreas Gohr 122be0b6729SAndreas Gohr return [ 123be0b6729SAndreas Gohr ':user' => $INPUT->server->str('REMOTE_USER'), 124be0b6729SAndreas Gohr ':mail' => $USERINFO['mail'] ?: '', 125*6648d9d9SAndreas Gohr ':groups' => $USERINFO['grps'] ?: [], 126*6648d9d9SAndreas Gohr ':id' => ':' . $INFO['id'], 127*6648d9d9SAndreas Gohr ':page' => noNS($INFO['id']), 128*6648d9d9SAndreas Gohr ':ns' => ':' . getNS($INFO['id']), 129be0b6729SAndreas Gohr ]; 130be0b6729SAndreas Gohr } 1312e564e06SAndreas Gohr} 132