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 14*6adcc9adSAndreas Gohr * @return array 152e564e06SAndreas Gohr * @throws \Exception 162e564e06SAndreas Gohr */ 17*6adcc9adSAndreas 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 26*6adcc9adSAndreas Gohr return json_decode($doc, true); 272e564e06SAndreas Gohr } 282e564e06SAndreas Gohr 292e564e06SAndreas Gohr /** 302e564e06SAndreas Gohr * Opens a database connection, executes the query and returns the result 312e564e06SAndreas Gohr * 322e564e06SAndreas Gohr * @param string $query 332e564e06SAndreas Gohr * @return array 342e564e06SAndreas Gohr * @throws \PDOException 352e564e06SAndreas Gohr * @todo should we keep the DB connection around for subsequent queries? 362e564e06SAndreas Gohr * @todo should we allow SELECT queries only for additional security? 372e564e06SAndreas Gohr */ 38be0b6729SAndreas Gohr public function executeQuery($query) 392e564e06SAndreas Gohr { 402e564e06SAndreas Gohr 41be0b6729SAndreas Gohr $opts = [ 42be0b6729SAndreas Gohr PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // always fetch as array 43be0b6729SAndreas Gohr PDO::ATTR_EMULATE_PREPARES => true, // emulating prepares allows us to reuse param names 44be0b6729SAndreas Gohr PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // we want exceptions, not error codes 45be0b6729SAndreas Gohr ]; 46be0b6729SAndreas Gohr $pdo = new PDO( 47be0b6729SAndreas Gohr $this->getConf('dsn'), 48be0b6729SAndreas Gohr $this->getConf('user'), 49be0b6729SAndreas Gohr conf_decodeString($this->getConf('pass')), 50be0b6729SAndreas Gohr $opts 51be0b6729SAndreas Gohr ); 52be0b6729SAndreas Gohr 53be0b6729SAndreas Gohr $params = $this->gatherVariables(); 54be0b6729SAndreas Gohr $sth = $this->prepareStatement($pdo, $query, $params); 55be0b6729SAndreas Gohr $sth->execute(); 562e564e06SAndreas Gohr $data = $sth->fetchAll(PDO::FETCH_ASSOC); 572e564e06SAndreas Gohr $sth->closeCursor(); 582e564e06SAndreas Gohr 592e564e06SAndreas Gohr return $data; 602e564e06SAndreas Gohr } 61be0b6729SAndreas Gohr 62be0b6729SAndreas Gohr /** 63be0b6729SAndreas Gohr * Generate a prepared statement with bound parameters 64be0b6729SAndreas Gohr * 65be0b6729SAndreas Gohr * @param PDO $pdo 66be0b6729SAndreas Gohr * @param string $sql 67be0b6729SAndreas Gohr * @param string[] $parameters 68be0b6729SAndreas Gohr * @return PDOStatement 69be0b6729SAndreas Gohr */ 70be0b6729SAndreas Gohr public function prepareStatement(\PDO $pdo, $sql, $parameters) 71be0b6729SAndreas Gohr { 72be0b6729SAndreas Gohr $sth = $pdo->prepare($sql); 73be0b6729SAndreas Gohr 74be0b6729SAndreas Gohr foreach ($parameters as $key => $val) { 75be0b6729SAndreas Gohr if (is_array($val)) continue; 76be0b6729SAndreas Gohr if (is_object($val)) continue; 77be0b6729SAndreas Gohr if (strpos($sql, $key) === false) continue; // skip if parameter is missing 78be0b6729SAndreas Gohr 79be0b6729SAndreas Gohr if (is_int($val)) { 80be0b6729SAndreas Gohr $sth->bindValue($key, $val, PDO::PARAM_INT); 81be0b6729SAndreas Gohr } else { 82be0b6729SAndreas Gohr $sth->bindValue($key, $val); 83be0b6729SAndreas Gohr } 84be0b6729SAndreas Gohr } 85be0b6729SAndreas Gohr 86be0b6729SAndreas Gohr return $sth; 87be0b6729SAndreas Gohr } 88be0b6729SAndreas Gohr 89be0b6729SAndreas Gohr /** 90be0b6729SAndreas Gohr * Get the standard replacement variables 91be0b6729SAndreas Gohr * 92be0b6729SAndreas Gohr * @return array 93be0b6729SAndreas Gohr */ 94be0b6729SAndreas Gohr public function gatherVariables() 95be0b6729SAndreas Gohr { 96be0b6729SAndreas Gohr global $USERINFO; 97be0b6729SAndreas Gohr global $INFO; 98be0b6729SAndreas Gohr global $INPUT; 99be0b6729SAndreas Gohr 100be0b6729SAndreas Gohr // add leading colon 101be0b6729SAndreas Gohr $id = ':' . $INFO['id']; 102be0b6729SAndreas Gohr return [ 103be0b6729SAndreas Gohr ':user' => $INPUT->server->str('REMOTE_USER'), 104be0b6729SAndreas Gohr ':mail' => $USERINFO['mail'] ?: '', 105be0b6729SAndreas Gohr ':groups' => $USERINFO['grps'] ? join(',', $USERINFO['grps']) : '', //FIXME escaping correct??? 106be0b6729SAndreas Gohr ':id' => $id, 107be0b6729SAndreas Gohr ':page' => noNS($id), 108be0b6729SAndreas Gohr ':ns' => getNS($id), //FIXME check that leading colon exists 109be0b6729SAndreas Gohr ]; 110be0b6729SAndreas Gohr } 1112e564e06SAndreas Gohr} 112