1<?php 2 3/** 4 * DokuWiki Plugin dbquery (Helper Component) 5 * 6 * @license GPL 2 http://www.gnu.org/licenses/gpl-2.0.html 7 * @author Andreas Gohr <dokuwiki@cosmocode.de> 8 */ 9class helper_plugin_dbquery extends dokuwiki\Extension\Plugin 10{ 11 /** @var PDO[] do not access directly, use getPDO instead */ 12 protected $pdo = []; 13 14 /** 15 * @param string $name Page name of the query 16 * @return array 17 * @throws \Exception 18 */ 19 public function loadCodeBlocksFromPage($name) 20 { 21 22 $name = cleanID($name); 23 $id = $this->getConf('namespace') . ':' . $name; 24 if (!page_exists($id)) throw new \Exception("No query named '$name' found"); 25 26 $doc = p_cached_output(wikiFN($id), 'dbquery'); 27 28 return json_decode($doc, true); 29 } 30 31 /** 32 * Return the PDO object and cache it for the request 33 * 34 * Connections data can be null to use the info from the config 35 * 36 * @param string|null $dsn 37 * @param string|null $user 38 * @param string|null $pass 39 * @return PDO 40 */ 41 public function getPDO($dsn = null, $user = null, $pass = null) 42 { 43 $dsn = $dsn ?: $this->getConf('dsn'); 44 $user = $user ?: $this->getConf('user'); 45 $pass = $pass ?: conf_decodeString($this->getConf('pass')); 46 $conid = md5($dsn . $user . $pass); 47 48 if (isset($this->pdo[$conid])) return $this->pdo[$conid]; 49 50 $opts = [ 51 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // always fetch as array 52 PDO::ATTR_EMULATE_PREPARES => true, // emulating prepares allows us to reuse param names 53 PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // we want exceptions, not error codes 54 ]; 55 56 $this->pdo[$conid] = new PDO($dsn, $user, $pass, $opts); 57 return $this->pdo[$conid]; 58 } 59 60 /** 61 * Opens a database connection, executes the query and returns the result 62 * 63 * @param string $query 64 * @return array 65 * @throws \PDOException 66 * @todo should we allow SELECT queries only for additional security? 67 */ 68 public function executeQuery($query) 69 { 70 $pdo = $this->getPDO(); 71 $params = $this->gatherVariables(); 72 $sth = $this->prepareStatement($pdo, $query, $params); 73 $sth->execute(); 74 $data = $sth->fetchAll(PDO::FETCH_ASSOC); 75 $sth->closeCursor(); 76 77 return $data; 78 } 79 80 /** 81 * Generate a prepared statement with bound parameters 82 * 83 * @param PDO $pdo 84 * @param string $sql 85 * @param array $parameters 86 * @return PDOStatement 87 */ 88 public function prepareStatement(\PDO $pdo, $sql, $parameters) 89 { 90 // prepare the groups 91 $cnt = 0; 92 $groupids = []; 93 foreach ($parameters[':groups'] as $group) { 94 $id = 'group' . $cnt++; 95 $parameters[$id] = $group; 96 $groupids[] = ":$id"; 97 } 98 unset($parameters[':groups']); 99 $sql = str_replace(':groups', join(',', $groupids), $sql); 100 101 $sth = $pdo->prepare($sql); 102 foreach ($parameters as $key => $val) { 103 if (is_array($val)) continue; 104 if (is_object($val)) continue; 105 if (strpos($sql, $key) === false) continue; // skip if parameter is missing 106 107 if (is_int($val)) { 108 $sth->bindValue($key, $val, PDO::PARAM_INT); 109 } else { 110 $sth->bindValue($key, $val); 111 } 112 } 113 114 return $sth; 115 } 116 117 /** 118 * Get the standard replacement variables 119 * 120 * @return array 121 */ 122 public function gatherVariables() 123 { 124 global $USERINFO; 125 global $INFO; 126 global $INPUT; 127 128 return [ 129 ':user' => $INPUT->server->str('REMOTE_USER'), 130 ':mail' => $USERINFO['mail'] ?: '', 131 ':groups' => $USERINFO['grps'] ?: [], 132 ':id' => ':' . $INFO['id'], 133 ':page' => noNS($INFO['id']), 134 ':ns' => ':' . getNS($INFO['id']), 135 ]; 136 } 137} 138