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 142e564e06SAndreas Gohr * @throws \Exception 152e564e06SAndreas Gohr */ 162e564e06SAndreas Gohr public function loadQueryFromPage($name) 172e564e06SAndreas Gohr { 182e564e06SAndreas Gohr 192e564e06SAndreas Gohr $name = cleanID($name); 202e564e06SAndreas Gohr $id = $this->getConf('namespace') . ':' . $name; 212e564e06SAndreas Gohr if (!page_exists($id)) throw new \Exception("No query named '$name' found"); 222e564e06SAndreas Gohr 232e564e06SAndreas Gohr $doc = p_cached_output(wikiFN($id), 'dbquery'); 242e564e06SAndreas Gohr // FIXME handle additional stuff later 252e564e06SAndreas Gohr 262e564e06SAndreas Gohr return trim($doc); 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 */ 38*be0b6729SAndreas Gohr public function executeQuery($query) 392e564e06SAndreas Gohr { 402e564e06SAndreas Gohr 41*be0b6729SAndreas Gohr $opts = [ 42*be0b6729SAndreas Gohr PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // always fetch as array 43*be0b6729SAndreas Gohr PDO::ATTR_EMULATE_PREPARES => true, // emulating prepares allows us to reuse param names 44*be0b6729SAndreas Gohr PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // we want exceptions, not error codes 45*be0b6729SAndreas Gohr ]; 46*be0b6729SAndreas Gohr $pdo = new PDO( 47*be0b6729SAndreas Gohr $this->getConf('dsn'), 48*be0b6729SAndreas Gohr $this->getConf('user'), 49*be0b6729SAndreas Gohr conf_decodeString($this->getConf('pass')), 50*be0b6729SAndreas Gohr $opts 51*be0b6729SAndreas Gohr ); 52*be0b6729SAndreas Gohr 53*be0b6729SAndreas Gohr $params = $this->gatherVariables(); 54*be0b6729SAndreas Gohr $sth = $this->prepareStatement($pdo, $query, $params); 55*be0b6729SAndreas Gohr $sth->execute(); 562e564e06SAndreas Gohr $data = $sth->fetchAll(PDO::FETCH_ASSOC); 572e564e06SAndreas Gohr $sth->closeCursor(); 582e564e06SAndreas Gohr 592e564e06SAndreas Gohr return $data; 602e564e06SAndreas Gohr } 61*be0b6729SAndreas Gohr 62*be0b6729SAndreas Gohr /** 63*be0b6729SAndreas Gohr * Generate a prepared statement with bound parameters 64*be0b6729SAndreas Gohr * 65*be0b6729SAndreas Gohr * @param PDO $pdo 66*be0b6729SAndreas Gohr * @param string $sql 67*be0b6729SAndreas Gohr * @param string[] $parameters 68*be0b6729SAndreas Gohr * @return PDOStatement 69*be0b6729SAndreas Gohr */ 70*be0b6729SAndreas Gohr public function prepareStatement(\PDO $pdo, $sql, $parameters) 71*be0b6729SAndreas Gohr { 72*be0b6729SAndreas Gohr $sth = $pdo->prepare($sql); 73*be0b6729SAndreas Gohr 74*be0b6729SAndreas Gohr foreach ($parameters as $key => $val) { 75*be0b6729SAndreas Gohr if (is_array($val)) continue; 76*be0b6729SAndreas Gohr if (is_object($val)) continue; 77*be0b6729SAndreas Gohr if (strpos($sql, $key) === false) continue; // skip if parameter is missing 78*be0b6729SAndreas Gohr 79*be0b6729SAndreas Gohr if (is_int($val)) { 80*be0b6729SAndreas Gohr $sth->bindValue($key, $val, PDO::PARAM_INT); 81*be0b6729SAndreas Gohr } else { 82*be0b6729SAndreas Gohr $sth->bindValue($key, $val); 83*be0b6729SAndreas Gohr } 84*be0b6729SAndreas Gohr } 85*be0b6729SAndreas Gohr 86*be0b6729SAndreas Gohr return $sth; 87*be0b6729SAndreas Gohr } 88*be0b6729SAndreas Gohr 89*be0b6729SAndreas Gohr /** 90*be0b6729SAndreas Gohr * Get the standard replacement variables 91*be0b6729SAndreas Gohr * 92*be0b6729SAndreas Gohr * @return array 93*be0b6729SAndreas Gohr */ 94*be0b6729SAndreas Gohr public function gatherVariables() 95*be0b6729SAndreas Gohr { 96*be0b6729SAndreas Gohr global $USERINFO; 97*be0b6729SAndreas Gohr global $INFO; 98*be0b6729SAndreas Gohr global $INPUT; 99*be0b6729SAndreas Gohr 100*be0b6729SAndreas Gohr // add leading colon 101*be0b6729SAndreas Gohr $id = ':' . $INFO['id']; 102*be0b6729SAndreas Gohr return [ 103*be0b6729SAndreas Gohr ':user' => $INPUT->server->str('REMOTE_USER'), 104*be0b6729SAndreas Gohr ':mail' => $USERINFO['mail'] ?: '', 105*be0b6729SAndreas Gohr ':groups' => $USERINFO['grps'] ? join(',', $USERINFO['grps']) : '', //FIXME escaping correct??? 106*be0b6729SAndreas Gohr ':id' => $id, 107*be0b6729SAndreas Gohr ':page' => noNS($id), 108*be0b6729SAndreas Gohr ':ns' => getNS($id), //FIXME check that leading colon exists 109*be0b6729SAndreas Gohr ]; 110*be0b6729SAndreas Gohr } 1112e564e06SAndreas Gohr} 112