1<?php 2 3/** 4 * The phpMyFAQ\Db_Sqlsrv class provides methods and functions for SQL Server Driver 5 * for PHP from Microsoft for Microsoft SQL Server 2012 or later. 6 * 7 * This Source Code Form is subject to the terms of the Mozilla Public License, 8 * v. 2.0. If a copy of the MPL was not distributed with this file, You can 9 * obtain one at http://mozilla.org/MPL/2.0/. 10 * 11 * @package phpMyFAQ 12 * @author Thorsten Rinne <thorsten@phpmyfaq.de> 13 * @copyright 2009-2020 phpMyFAQ Team 14 * @license http://www.mozilla.org/MPL/2.0/ Mozilla Public License Version 2.0 15 * @link https://www.phpmyfaq.de 16 * @since 2009-02-18 17 */ 18 19namespace phpMyFAQ\Database; 20 21use Exception; 22use phpMyFAQ\Database; 23use phpMyFAQ\Utils; 24 25/** 26 * Class Sqlsrv 27 * 28 * @package phpMyFAQ\Database 29 */ 30class Sqlsrv implements DatabaseDriver 31{ 32 /** 33 * Tables. 34 * 35 * @var array 36 */ 37 public $tableNames = []; 38 39 /** 40 * @var resource 41 */ 42 private $conn = false; 43 /** 44 * The query log string. 45 * 46 * @var string 47 */ 48 private $sqllog = ''; 49 /** 50 * Connection options array. 51 * 52 * @var array 53 */ 54 private $connectionOptions = []; 55 56 /** 57 * Connects to the database. 58 * 59 * This function connects to a MySQL database 60 * 61 * @param string $host A string specifying the name of the server to which a connection is being established 62 * @param string $user Specifies the User ID to be used when connecting with SQL Server Authentication 63 * @param string $password Specifies the password associated with the User ID to be used when connecting with 64 * SQL Server Authentication 65 * @param string $database Specifies the name of the database in use for the connection being established 66 * @param int|null $port 67 * @return bool true, if connected, otherwise false 68 */ 69 public function connect(string $host, string $user, string $password, $database = '', $port = 1433): ?bool 70 { 71 $this->setConnectionOptions($user, $password, $database); 72 73 $this->conn = sqlsrv_connect($host . ', ' . $port, $this->connectionOptions); 74 if (!$this->conn) { 75 Database::errorPage((string)sqlsrv_errors()); 76 die(); 77 } 78 79 return true; 80 } 81 82 /** 83 * Sets the connection options. 84 * 85 * @param string $user Specifies the User ID to be used when connecting with SQL Server Authentication 86 * @param string $password Specifies the password associated with the User ID to be used when connecting with 87 * SQL Server Authentication 88 * @param string $database Specifies the name of the database in use for the connection being established 89 */ 90 private function setConnectionOptions(string $user, string $password, string $database) 91 { 92 $this->connectionOptions = [ 93 'UID' => $user, 94 'PWD' => $password, 95 'Database' => $database, 96 'CharacterSet' => 'UTF-8', 97 ]; 98 } 99 100 /** 101 * Escapes a string for use in a query. 102 * 103 * @param string $string String 104 * 105 * @return string 106 */ 107 public function escape($string): string 108 { 109 return str_replace("'", "''", $string); 110 } 111 112 /** 113 * Fetch a result row as an assoc array. 114 * 115 * @param resource $result Resultset 116 * 117 * @return array 118 */ 119 public function fetchArray($result): array 120 { 121 return sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC); 122 } 123 124 /** 125 * Fetches a complete result as an object. 126 * 127 * @param resource $result Resultset 128 * 129 * @return array 130 * @throws Exception 131 */ 132 public function fetchAll($result): array 133 { 134 $ret = []; 135 if (false === $result) { 136 throw new Exception('Error while fetching result: ' . $this->error()); 137 } 138 139 while ($row = $this->fetchObject($result)) { 140 $ret[] = $row; 141 } 142 143 return $ret; 144 } 145 146 /** 147 * Returns the error string. 148 * 149 * @return string 150 */ 151 public function error(): string 152 { 153 $errors = sqlsrv_errors(); 154 155 if (null !== $errors) { 156 return $errors[0]['SQLSTATE'] . ': ' . $errors[0]['message']; 157 } 158 } 159 160 /** 161 * Fetch a result row as an object. 162 * 163 * @param resource $result Resultset 164 * 165 * @return resource 166 */ 167 public function fetchObject($result) 168 { 169 return sqlsrv_fetch_object($result); 170 } 171 172 /** 173 * Number of rows in a result. 174 * 175 * @param resource $result Resultset 176 * 177 * @return int 178 */ 179 public function numRows($result): int 180 { 181 return sqlsrv_num_rows($result); 182 } 183 184 /** 185 * Logs the queries. 186 * 187 * @return string 188 */ 189 public function log(): string 190 { 191 return $this->sqllog; 192 } 193 194 /** 195 * This function returns the table status. 196 * 197 * @param string $prefix Table prefix 198 * 199 * @return array 200 */ 201 public function getTableStatus($prefix = ''): array 202 { 203 $tables = []; 204 $query = " 205 SELECT 206 obj.name AS table_name, 207 idx.rows AS table_rows 208 FROM 209 sysobjects obj, sysindexes idx 210 WHERE 211 idx.id = OBJECT_ID(obj.name) 212 AND idx.indid < 2 213 AND obj.xtype = 'U' 214 ORDER BY obj.name"; 215 $result = $this->query($query); 216 217 while ($row = $this->fetchObject($result)) { 218 $tables[$row->table_name] = $row->table_rows; 219 } 220 221 return $tables; 222 } 223 224 /** 225 * This function sends a query to the database. 226 * 227 * @param string $query 228 * @param int $offset 229 * @param int $rowcount 230 * 231 * @return mixed $result 232 */ 233 public function query(string $query, $offset = 0, $rowcount = 0) 234 { 235 if (DEBUG) { 236 $this->sqllog .= Utils::debug($query); 237 } 238 239 $options = array('Scrollable' => SQLSRV_CURSOR_KEYSET); 240 241 if (0 < $rowcount) { 242 $query .= sprintf(' OFFSET %d ROWS FETCH NEXT %d ROWS ONLY', $offset, $rowcount); 243 } 244 245 $result = sqlsrv_query($this->conn, $query, [], $options); 246 247 if (!$result) { 248 $this->sqllog .= $this->error(); 249 } 250 251 return $result; 252 } 253 254 /** 255 * Returns the next ID of a table. 256 * 257 * @param string $table the name of the table 258 * @param string $id the name of the ID column 259 * 260 * @return int 261 */ 262 public function nextID($table, $id): int 263 { 264 $select = sprintf( 265 ' 266 SELECT 267 max(%s) as current_id 268 FROM 269 %s', 270 $id, 271 $table 272 ); 273 274 $result = $this->query($select); 275 sqlsrv_fetch($result); 276 277 return (sqlsrv_get_field($result, 0) + 1); 278 } 279 280 /** 281 * Returns the library version string. 282 * 283 * @return string 284 */ 285 public function clientVersion(): string 286 { 287 $client_info = sqlsrv_client_info($this->conn); 288 289 return $client_info['DriverODBCVer'] . ' ' . $client_info['DriverVer']; 290 } 291 292 /** 293 * Returns the library version string. 294 * 295 * @return string 296 */ 297 public function serverVersion(): string 298 { 299 $server_info = sqlsrv_server_info($this->conn); 300 301 return $server_info['SQLServerVersion']; 302 } 303 304 /** 305 * Returns an array with all table names. 306 * 307 * @todo Have to be refactored because of https://github.com/thorsten/phpMyFAQ/issues/965 308 * 309 * @param string $prefix Table prefix 310 * 311 * @return array 312 */ 313 public function getTableNames($prefix = ''): array 314 { 315 return $this->tableNames = [ 316 $prefix . 'faqadminlog', 317 $prefix . 'faqattachment', 318 $prefix . 'faqattachment_file', 319 $prefix . 'faqcaptcha', 320 $prefix . 'faqcategories', 321 $prefix . 'faqcategory_group', 322 $prefix . 'faqcategory_news', 323 $prefix . 'faqcategory_user', 324 $prefix . 'faqcategoryrelations', 325 $prefix . 'faqchanges', 326 $prefix . 'faqcomments', 327 $prefix . 'faqconfig', 328 $prefix . 'faqdata', 329 $prefix . 'faqdata_group', 330 $prefix . 'faqdata_revisions', 331 $prefix . 'faqdata_tags', 332 $prefix . 'faqdata_user', 333 $prefix . 'faqglossary', 334 $prefix . 'faqgroup', 335 $prefix . 'faqgroup_right', 336 $prefix . 'faqinstances', 337 $prefix . 'faqinstances_config', 338 $prefix . 'faqmeta', 339 $prefix . 'faqnews', 340 $prefix . 'faqquestions', 341 $prefix . 'faqright', 342 $prefix . 'faqsearches', 343 $prefix . 'faqsections', 344 $prefix . 'faqsection_group', 345 $prefix . 'faqsection_news', 346 $prefix . 'faqsessions', 347 $prefix . 'faqstopwords', 348 $prefix . 'faqtags', 349 $prefix . 'faquser', 350 $prefix . 'faquser_group', 351 $prefix . 'faquser_right', 352 $prefix . 'faquserdata', 353 $prefix . 'faquserlogin', 354 $prefix . 'faqvisits', 355 $prefix . 'faqvoting', 356 ]; 357 } 358 359 /** 360 * Closes the connection to the database. 361 */ 362 public function close() 363 { 364 sqlsrv_close($this->conn); 365 } 366 367 /** 368 * @return string 369 */ 370 public function now(): string 371 { 372 return 'GETDATE()'; 373 } 374} 375