1<?php
2
3namespace Doctrine\DBAL\Driver\SQLSrv;
4
5use Doctrine\DBAL\Driver\FetchUtils;
6use Doctrine\DBAL\Driver\Result;
7use Doctrine\DBAL\Driver\SQLSrv\Exception\Error;
8use Doctrine\DBAL\Driver\Statement as StatementInterface;
9use Doctrine\DBAL\Driver\StatementIterator;
10use Doctrine\DBAL\FetchMode;
11use Doctrine\DBAL\ParameterType;
12use IteratorAggregate;
13use PDO;
14use ReturnTypeWillChange;
15
16use function array_key_exists;
17use function count;
18use function func_get_args;
19use function in_array;
20use function is_int;
21use function is_numeric;
22use function sqlsrv_errors;
23use function sqlsrv_execute;
24use function sqlsrv_fetch;
25use function sqlsrv_fetch_array;
26use function sqlsrv_fetch_object;
27use function sqlsrv_get_field;
28use function sqlsrv_next_result;
29use function sqlsrv_num_fields;
30use function SQLSRV_PHPTYPE_STREAM;
31use function SQLSRV_PHPTYPE_STRING;
32use function sqlsrv_prepare;
33use function sqlsrv_rows_affected;
34use function SQLSRV_SQLTYPE_VARBINARY;
35use function stripos;
36
37use const SQLSRV_ENC_BINARY;
38use const SQLSRV_ENC_CHAR;
39use const SQLSRV_ERR_ERRORS;
40use const SQLSRV_FETCH_ASSOC;
41use const SQLSRV_FETCH_BOTH;
42use const SQLSRV_FETCH_NUMERIC;
43use const SQLSRV_PARAM_IN;
44
45/**
46 * SQL Server Statement.
47 *
48 * @deprecated Use {@link Statement} instead
49 */
50class SQLSrvStatement implements IteratorAggregate, StatementInterface, Result
51{
52    /**
53     * The SQLSRV Resource.
54     *
55     * @var resource
56     */
57    private $conn;
58
59    /**
60     * The SQL statement to execute.
61     *
62     * @var string
63     */
64    private $sql;
65
66    /**
67     * The SQLSRV statement resource.
68     *
69     * @var resource|null
70     */
71    private $stmt;
72
73    /**
74     * References to the variables bound as statement parameters.
75     *
76     * @var mixed
77     */
78    private $variables = [];
79
80    /**
81     * Bound parameter types.
82     *
83     * @var int[]
84     */
85    private $types = [];
86
87    /**
88     * Translations.
89     *
90     * @var int[]
91     */
92    private static $fetchMap = [
93        FetchMode::MIXED       => SQLSRV_FETCH_BOTH,
94        FetchMode::ASSOCIATIVE => SQLSRV_FETCH_ASSOC,
95        FetchMode::NUMERIC     => SQLSRV_FETCH_NUMERIC,
96    ];
97
98    /**
99     * The name of the default class to instantiate when fetching class instances.
100     *
101     * @var string
102     */
103    private $defaultFetchClass = '\stdClass';
104
105    /**
106     * The constructor arguments for the default class to instantiate when fetching class instances.
107     *
108     * @var mixed[]
109     */
110    private $defaultFetchClassCtorArgs = [];
111
112    /**
113     * The fetch style.
114     *
115     * @var int
116     */
117    private $defaultFetchMode = FetchMode::MIXED;
118
119    /**
120     * The last insert ID.
121     *
122     * @var LastInsertId|null
123     */
124    private $lastInsertId;
125
126    /**
127     * Indicates whether the statement is in the state when fetching results is possible
128     *
129     * @var bool
130     */
131    private $result = false;
132
133    /**
134     * Append to any INSERT query to retrieve the last insert id.
135     *
136     * @deprecated This constant has been deprecated and will be made private in 3.0
137     */
138    public const LAST_INSERT_ID_SQL = ';SELECT SCOPE_IDENTITY() AS LastInsertId;';
139
140    /**
141     * @internal The statement can be only instantiated by its driver connection.
142     *
143     * @param resource $conn
144     * @param string   $sql
145     */
146    public function __construct($conn, $sql, ?LastInsertId $lastInsertId = null)
147    {
148        $this->conn = $conn;
149        $this->sql  = $sql;
150
151        if (stripos($sql, 'INSERT INTO ') !== 0) {
152            return;
153        }
154
155        $this->sql         .= self::LAST_INSERT_ID_SQL;
156        $this->lastInsertId = $lastInsertId;
157    }
158
159    /**
160     * {@inheritdoc}
161     */
162    public function bindValue($param, $value, $type = ParameterType::STRING)
163    {
164        if (! is_numeric($param)) {
165            throw new SQLSrvException(
166                'sqlsrv does not support named parameters to queries, use question mark (?) placeholders instead.'
167            );
168        }
169
170        $this->variables[$param] = $value;
171        $this->types[$param]     = $type;
172
173        return true;
174    }
175
176    /**
177     * {@inheritdoc}
178     */
179    public function bindParam($param, &$variable, $type = ParameterType::STRING, $length = null)
180    {
181        if (! is_numeric($param)) {
182            throw new SQLSrvException(
183                'sqlsrv does not support named parameters to queries, use question mark (?) placeholders instead.'
184            );
185        }
186
187        $this->variables[$param] =& $variable;
188        $this->types[$param]     = $type;
189
190        // unset the statement resource if it exists as the new one will need to be bound to the new variable
191        $this->stmt = null;
192
193        return true;
194    }
195
196    /**
197     * {@inheritdoc}
198     *
199     * @deprecated Use free() instead.
200     */
201    public function closeCursor()
202    {
203        $this->free();
204
205        return true;
206    }
207
208    /**
209     * {@inheritdoc}
210     */
211    public function columnCount()
212    {
213        if ($this->stmt === null) {
214            return 0;
215        }
216
217        return sqlsrv_num_fields($this->stmt) ?: 0;
218    }
219
220    /**
221     * {@inheritdoc}
222     *
223     * @deprecated The error information is available via exceptions.
224     */
225    public function errorCode()
226    {
227        $errors = sqlsrv_errors(SQLSRV_ERR_ERRORS);
228        if ($errors) {
229            return $errors[0]['code'];
230        }
231
232        return false;
233    }
234
235    /**
236     * {@inheritdoc}
237     *
238     * @deprecated The error information is available via exceptions.
239     */
240    public function errorInfo()
241    {
242        return (array) sqlsrv_errors(SQLSRV_ERR_ERRORS);
243    }
244
245    /**
246     * {@inheritdoc}
247     */
248    public function execute($params = null)
249    {
250        if ($params) {
251            $hasZeroIndex = array_key_exists(0, $params);
252
253            foreach ($params as $key => $val) {
254                if ($hasZeroIndex && is_int($key)) {
255                    $this->bindValue($key + 1, $val);
256                } else {
257                    $this->bindValue($key, $val);
258                }
259            }
260        }
261
262        if (! $this->stmt) {
263            $this->stmt = $this->prepare();
264        }
265
266        if (! sqlsrv_execute($this->stmt)) {
267            throw Error::new();
268        }
269
270        if ($this->lastInsertId) {
271            sqlsrv_next_result($this->stmt);
272            sqlsrv_fetch($this->stmt);
273            $this->lastInsertId->setId(sqlsrv_get_field($this->stmt, 0));
274        }
275
276        $this->result = true;
277
278        return true;
279    }
280
281    /**
282     * Prepares SQL Server statement resource
283     *
284     * @return resource
285     *
286     * @throws SQLSrvException
287     */
288    private function prepare()
289    {
290        $params = [];
291
292        foreach ($this->variables as $column => &$variable) {
293            switch ($this->types[$column]) {
294                case ParameterType::LARGE_OBJECT:
295                    $params[$column - 1] = [
296                        &$variable,
297                        SQLSRV_PARAM_IN,
298                        SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),
299                        SQLSRV_SQLTYPE_VARBINARY('max'),
300                    ];
301                    break;
302
303                case ParameterType::BINARY:
304                    $params[$column - 1] = [
305                        &$variable,
306                        SQLSRV_PARAM_IN,
307                        SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_BINARY),
308                    ];
309                    break;
310
311                case ParameterType::ASCII:
312                    $params[$column - 1] = [
313                        &$variable,
314                        SQLSRV_PARAM_IN,
315                        SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR),
316                    ];
317                    break;
318
319                default:
320                    $params[$column - 1] =& $variable;
321                    break;
322            }
323        }
324
325        $stmt = sqlsrv_prepare($this->conn, $this->sql, $params);
326
327        if (! $stmt) {
328            throw Error::new();
329        }
330
331        return $stmt;
332    }
333
334    /**
335     * {@inheritdoc}
336     *
337     * @deprecated Use one of the fetch- or iterate-related methods.
338     */
339    public function setFetchMode($fetchMode, $arg2 = null, $arg3 = null)
340    {
341        $this->defaultFetchMode          = $fetchMode;
342        $this->defaultFetchClass         = $arg2 ?: $this->defaultFetchClass;
343        $this->defaultFetchClassCtorArgs = $arg3 ? (array) $arg3 : $this->defaultFetchClassCtorArgs;
344
345        return true;
346    }
347
348    /**
349     * {@inheritdoc}
350     *
351     * @deprecated Use iterateNumeric(), iterateAssociative() or iterateColumn() instead.
352     */
353    #[ReturnTypeWillChange]
354    public function getIterator()
355    {
356        return new StatementIterator($this);
357    }
358
359    /**
360     * {@inheritdoc}
361     *
362     * @deprecated Use fetchNumeric(), fetchAssociative() or fetchOne() instead.
363     *
364     * @throws SQLSrvException
365     */
366    public function fetch($fetchMode = null, $cursorOrientation = PDO::FETCH_ORI_NEXT, $cursorOffset = 0)
367    {
368        // do not try fetching from the statement if it's not expected to contain result
369        // in order to prevent exceptional situation
370        if ($this->stmt === null || ! $this->result) {
371            return false;
372        }
373
374        $args      = func_get_args();
375        $fetchMode = $fetchMode ?: $this->defaultFetchMode;
376
377        if ($fetchMode === FetchMode::COLUMN) {
378            return $this->fetchColumn();
379        }
380
381        if (isset(self::$fetchMap[$fetchMode])) {
382            return sqlsrv_fetch_array($this->stmt, self::$fetchMap[$fetchMode]) ?: false;
383        }
384
385        if (in_array($fetchMode, [FetchMode::STANDARD_OBJECT, FetchMode::CUSTOM_OBJECT], true)) {
386            $className = $this->defaultFetchClass;
387            $ctorArgs  = $this->defaultFetchClassCtorArgs;
388
389            if (count($args) >= 2) {
390                $className = $args[1];
391                $ctorArgs  = $args[2] ?? [];
392            }
393
394            return sqlsrv_fetch_object($this->stmt, $className, $ctorArgs) ?: false;
395        }
396
397        throw new SQLSrvException('Fetch mode is not supported!');
398    }
399
400    /**
401     * {@inheritdoc}
402     *
403     * @deprecated Use fetchAllNumeric(), fetchAllAssociative() or fetchFirstColumn() instead.
404     */
405    public function fetchAll($fetchMode = null, $fetchArgument = null, $ctorArgs = null)
406    {
407        $rows = [];
408
409        switch ($fetchMode) {
410            case FetchMode::CUSTOM_OBJECT:
411                while (($row = $this->fetch(...func_get_args())) !== false) {
412                    $rows[] = $row;
413                }
414
415                break;
416
417            case FetchMode::COLUMN:
418                while (($row = $this->fetchColumn()) !== false) {
419                    $rows[] = $row;
420                }
421
422                break;
423
424            default:
425                while (($row = $this->fetch($fetchMode)) !== false) {
426                    $rows[] = $row;
427                }
428        }
429
430        return $rows;
431    }
432
433    /**
434     * {@inheritdoc}
435     *
436     * @deprecated Use fetchOne() instead.
437     */
438    public function fetchColumn($columnIndex = 0)
439    {
440        $row = $this->fetch(FetchMode::NUMERIC);
441
442        if ($row === false) {
443            return false;
444        }
445
446        return $row[$columnIndex] ?? null;
447    }
448
449    /**
450     * {@inheritdoc}
451     */
452    public function fetchNumeric()
453    {
454        return $this->doFetch(SQLSRV_FETCH_NUMERIC);
455    }
456
457    /**
458     * {@inheritdoc}
459     */
460    public function fetchAssociative()
461    {
462        return $this->doFetch(SQLSRV_FETCH_ASSOC);
463    }
464
465    /**
466     * {@inheritdoc}
467     */
468    public function fetchOne()
469    {
470        return FetchUtils::fetchOne($this);
471    }
472
473    /**
474     * {@inheritdoc}
475     */
476    public function fetchAllNumeric(): array
477    {
478        return FetchUtils::fetchAllNumeric($this);
479    }
480
481    /**
482     * {@inheritdoc}
483     */
484    public function fetchAllAssociative(): array
485    {
486        return FetchUtils::fetchAllAssociative($this);
487    }
488
489    /**
490     * {@inheritdoc}
491     */
492    public function fetchFirstColumn(): array
493    {
494        return FetchUtils::fetchFirstColumn($this);
495    }
496
497    /**
498     * {@inheritdoc}
499     */
500    public function rowCount()
501    {
502        if ($this->stmt === null) {
503            return 0;
504        }
505
506        return sqlsrv_rows_affected($this->stmt) ?: 0;
507    }
508
509    public function free(): void
510    {
511        // not having the result means there's nothing to close
512        if ($this->stmt === null || ! $this->result) {
513            return;
514        }
515
516        // emulate it by fetching and discarding rows, similarly to what PDO does in this case
517        // @link http://php.net/manual/en/pdostatement.closecursor.php
518        // @link https://github.com/php/php-src/blob/php-7.0.11/ext/pdo/pdo_stmt.c#L2075
519        // deliberately do not consider multiple result sets, since doctrine/dbal doesn't support them
520        while (sqlsrv_fetch($this->stmt)) {
521        }
522
523        $this->result = false;
524    }
525
526    /**
527     * @return mixed|false
528     */
529    private function doFetch(int $fetchType)
530    {
531        // do not try fetching from the statement if it's not expected to contain the result
532        // in order to prevent exceptional situation
533        if ($this->stmt === null || ! $this->result) {
534            return false;
535        }
536
537        return sqlsrv_fetch_array($this->stmt, $fetchType) ?? false;
538    }
539}
540