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