This project does not seem to handle request data directly as such no vulnerable execution paths were found.
include
, or for example
via PHP's auto-loading mechanism.
These results are based on our legacy PHP analysis, consider migrating to our new PHP analysis engine instead. Learn more
1 | <?php |
||
2 | |||
3 | namespace SilverStripe\MSSQL; |
||
4 | |||
5 | use SilverStripe\Core\Config\Configurable; |
||
6 | use SilverStripe\Core\Injector\Injectable; |
||
7 | use SilverStripe\Core\ClassInfo; |
||
8 | use SilverStripe\ORM\ArrayList; |
||
9 | use SilverStripe\ORM\Connect\Database; |
||
10 | use SilverStripe\ORM\DataList; |
||
11 | use SilverStripe\ORM\DB; |
||
12 | use SilverStripe\ORM\DataObject; |
||
13 | use SilverStripe\ORM\PaginatedList; |
||
14 | use SilverStripe\ORM\Queries\SQLSelect; |
||
15 | |||
16 | /** |
||
17 | * Microsoft SQL Server 2008+ connector class. |
||
18 | * |
||
19 | * <h2>Connecting using Windows</h2> |
||
20 | * |
||
21 | * If you've got your website running on Windows, it's highly recommended you |
||
22 | * use Microsoft SQL Server Driver for PHP "sqlsrv". |
||
23 | * |
||
24 | * A complete guide to installing a Windows IIS + PHP + SQL Server web stack can be |
||
25 | * found here: http://doc.silverstripe.org/installation-on-windows-server-manual-iis |
||
26 | * |
||
27 | * @see http://sqlsrvphp.codeplex.com/ |
||
28 | * |
||
29 | * <h2>Connecting using Linux or Mac OS X</h2> |
||
30 | * |
||
31 | * The following commands assume you used the default package manager |
||
32 | * to install PHP with the operating system. |
||
33 | * |
||
34 | * Debian, and Ubuntu: |
||
35 | * <code>apt-get install php5-sybase</code> |
||
36 | * |
||
37 | * Fedora, CentOS and RedHat: |
||
38 | * <code>yum install php-mssql</code> |
||
39 | * |
||
40 | * Mac OS X (MacPorts): |
||
41 | * <code>port install php5-mssql</code> |
||
42 | * |
||
43 | * These packages will install the mssql extension for PHP, as well |
||
44 | * as FreeTDS, which will let you connect to SQL Server. |
||
45 | * |
||
46 | * More information available in the SilverStripe developer wiki: |
||
47 | * @see http://doc.silverstripe.org/modules:mssql |
||
48 | * @see http://doc.silverstripe.org/installation-on-windows-server-manual-iis |
||
49 | * |
||
50 | * References: |
||
51 | * @see http://freetds.org |
||
52 | */ |
||
53 | class MSSQLDatabase extends Database |
||
54 | { |
||
55 | use Configurable; |
||
56 | use Injectable; |
||
57 | |||
58 | /** |
||
59 | * Words that will trigger an error if passed to a SQL Server fulltext search |
||
60 | */ |
||
61 | public static $noiseWords = array('about', '1', 'after', '2', 'all', 'also', '3', 'an', '4', 'and', '5', 'another', '6', 'any', '7', 'are', '8', 'as', '9', 'at', '0', 'be', '$', 'because', 'been', 'before', 'being', 'between', 'both', 'but', 'by', 'came', 'can', 'come', 'could', 'did', 'do', 'does', 'each', 'else', 'for', 'from', 'get', 'got', 'has', 'had', 'he', 'have', 'her', 'here', 'him', 'himself', 'his', 'how', 'if', 'in', 'into', 'is', 'it', 'its', 'just', 'like', 'make', 'many', 'me', 'might', 'more', 'most', 'much', 'must', 'my', 'never', 'no', 'now', 'of', 'on', 'only', 'or', 'other', 'our', 'out', 'over', 're', 'said', 'same', 'see', 'should', 'since', 'so', 'some', 'still', 'such', 'take', 'than', 'that', 'the', 'their', 'them', 'then', 'there', 'these', 'they', 'this', 'those', 'through', 'to', 'too', 'under', 'up', 'use', 'very', 'want', 'was', 'way', 'we', 'well', 'were', 'what', 'when', 'where', 'which', 'while', 'who', 'will', 'with', 'would', 'you', 'your', 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z'); |
||
62 | |||
63 | /** |
||
64 | * Transactions will work with FreeTDS, but not entirely with sqlsrv driver on Windows with MARS enabled. |
||
65 | * TODO: |
||
66 | * - after the test fails with open transaction, the transaction should be rolled back, |
||
67 | * otherwise other tests will break claiming that transaction is still open. |
||
68 | * - figure out SAVEPOINTS |
||
69 | * - READ ONLY transactions |
||
70 | */ |
||
71 | protected $supportsTransactions = true; |
||
72 | |||
73 | /** |
||
74 | * Cached flag to determine if full-text is enabled. This is set by |
||
75 | * {@link MSSQLDatabase::fullTextEnabled()} |
||
76 | * |
||
77 | * @var boolean |
||
78 | */ |
||
79 | protected $fullTextEnabled = null; |
||
80 | |||
81 | /** |
||
82 | * @var bool |
||
83 | */ |
||
84 | protected $transactionNesting = 0; |
||
85 | |||
86 | /** |
||
87 | * Set the default collation of the MSSQL nvarchar fields that we create. |
||
88 | * We don't apply this to the database as a whole, so that we can use unicode collations. |
||
89 | * |
||
90 | * @param string $collation |
||
91 | */ |
||
92 | public static function set_collation($collation) |
||
93 | { |
||
94 | static::config()->set('collation', $collation); |
||
95 | } |
||
96 | |||
97 | /** |
||
98 | * The default collation of the MSSQL nvarchar fields that we create. |
||
99 | * We don't apply this to the database as a whole, so that we can use |
||
100 | * unicode collations. |
||
101 | * |
||
102 | * @return string |
||
103 | */ |
||
104 | public static function get_collation() |
||
105 | { |
||
106 | return static::config()->get('collation'); |
||
107 | } |
||
108 | |||
109 | /** |
||
110 | * Connect to a MS SQL database. |
||
111 | * @param array $parameters An map of parameters, which should include: |
||
112 | * - server: The server, eg, localhost |
||
113 | * - username: The username to log on with |
||
114 | * - password: The password to log on with |
||
115 | * - database: The database to connect to |
||
116 | * - windowsauthentication: Set to true to use windows authentication |
||
117 | * instead of username/password |
||
118 | */ |
||
119 | public function connect($parameters) |
||
120 | { |
||
121 | parent::connect($parameters); |
||
122 | |||
123 | // Configure the connection |
||
124 | $this->query('SET QUOTED_IDENTIFIER ON'); |
||
125 | $this->query('SET TEXTSIZE 2147483647'); |
||
126 | } |
||
127 | |||
128 | /** |
||
129 | * Checks whether the current SQL Server version has full-text |
||
130 | * support installed and full-text is enabled for this database. |
||
131 | * |
||
132 | * @return boolean |
||
133 | */ |
||
134 | public function fullTextEnabled() |
||
135 | { |
||
136 | if ($this->fullTextEnabled === null) { |
||
137 | $this->fullTextEnabled = $this->updateFullTextEnabled(); |
||
138 | } |
||
139 | return $this->fullTextEnabled; |
||
140 | } |
||
141 | |||
142 | /** |
||
143 | * Checks whether the current SQL Server version has full-text |
||
144 | * support installed and full-text is enabled for this database. |
||
145 | * |
||
146 | * @return boolean |
||
147 | */ |
||
148 | protected function updateFullTextEnabled() |
||
149 | { |
||
150 | // Check if installed |
||
151 | $isInstalled = $this->query("SELECT fulltextserviceproperty('isfulltextinstalled')")->value(); |
||
152 | if (!$isInstalled) { |
||
153 | return false; |
||
154 | } |
||
155 | |||
156 | // Check if current database is enabled |
||
157 | $database = $this->getSelectedDatabase(); |
||
158 | $enabledForDb = $this->preparedQuery( |
||
159 | "SELECT is_fulltext_enabled FROM sys.databases WHERE name = ?", |
||
160 | array($database) |
||
161 | )->value(); |
||
162 | return $enabledForDb; |
||
163 | } |
||
164 | |||
165 | public function supportsCollations() |
||
166 | { |
||
167 | return true; |
||
168 | } |
||
169 | |||
170 | public function supportsTimezoneOverride() |
||
171 | { |
||
172 | return true; |
||
173 | } |
||
174 | |||
175 | public function getDatabaseServer() |
||
176 | { |
||
177 | return "sqlsrv"; |
||
178 | } |
||
179 | |||
180 | public function selectDatabase($name, $create = false, $errorLevel = E_USER_ERROR) |
||
181 | { |
||
182 | $this->fullTextEnabled = null; |
||
183 | |||
184 | return parent::selectDatabase($name, $create, $errorLevel); |
||
185 | } |
||
186 | |||
187 | public function clearTable($table) |
||
188 | { |
||
189 | $this->query("TRUNCATE TABLE \"$table\""); |
||
190 | } |
||
191 | |||
192 | /** |
||
193 | * SQL Server uses CURRENT_TIMESTAMP for the current date/time. |
||
194 | */ |
||
195 | public function now() |
||
196 | { |
||
197 | return 'CURRENT_TIMESTAMP'; |
||
198 | } |
||
199 | |||
200 | /** |
||
201 | * Returns the database-specific version of the random() function |
||
202 | */ |
||
203 | public function random() |
||
204 | { |
||
205 | return 'RAND()'; |
||
206 | } |
||
207 | |||
208 | /** |
||
209 | * The core search engine configuration. |
||
210 | * Picks up the fulltext-indexed tables from the database and executes search on all of them. |
||
211 | * Results are obtained as ID-ClassName pairs which is later used to reconstruct the DataObjectSet. |
||
212 | * |
||
213 | * @param array $classesToSearch computes all descendants and includes them. Check is done via WHERE clause. |
||
214 | * @param string $keywords Keywords as a space separated string |
||
215 | * @param int $start |
||
216 | * @param int $pageLength |
||
217 | * @param string $sortBy |
||
218 | * @param string $extraFilter |
||
219 | * @param bool $booleanSearch |
||
220 | * @param string $alternativeFileFilter |
||
221 | * @param bool $invertedMatch |
||
222 | * @return PaginatedList DataObjectSet of result pages |
||
223 | */ |
||
224 | public function searchEngine($classesToSearch, $keywords, $start, $pageLength, $sortBy = "Relevance DESC", $extraFilter = "", $booleanSearch = false, $alternativeFileFilter = "", $invertedMatch = false) |
||
225 | { |
||
226 | $start = (int)$start; |
||
227 | $pageLength = (int)$pageLength; |
||
228 | $results = new ArrayList(); |
||
229 | |||
230 | if (!$this->fullTextEnabled()) { |
||
231 | return new PaginatedList($results); |
||
232 | } |
||
233 | if (!in_array(substr($sortBy, 0, 9), array('"Relevanc', 'Relevance'))) { |
||
234 | user_error("Non-relevance sort not supported.", E_USER_ERROR); |
||
235 | } |
||
236 | |||
237 | $allClassesToSearch = array(); |
||
238 | foreach ($classesToSearch as $class) { |
||
239 | $allClassesToSearch = array_merge($allClassesToSearch, array_values(ClassInfo::dataClassesFor($class))); |
||
240 | } |
||
241 | $allClassesToSearch = array_unique($allClassesToSearch); |
||
242 | |||
243 | //Get a list of all the tables and columns we'll be searching on: |
||
244 | $fulltextColumns = $this->query('EXEC sp_help_fulltext_columns'); |
||
245 | $queries = array(); |
||
246 | |||
247 | // Sort the columns back into tables. |
||
248 | $tables = array(); |
||
249 | foreach ($fulltextColumns as $column) { |
||
250 | // Skip extension tables. |
||
251 | if (substr($column['TABLE_NAME'], -5) == '_Live' || substr($column['TABLE_NAME'], -9) == '_versions') { |
||
252 | continue; |
||
253 | } |
||
254 | |||
255 | // Add the column to table. |
||
256 | $table = &$tables[$column['TABLE_NAME']]; |
||
257 | if (!$table) { |
||
258 | $table = array($column['FULLTEXT_COLUMN_NAME']); |
||
259 | } else { |
||
260 | array_push($table, $column['FULLTEXT_COLUMN_NAME']); |
||
261 | } |
||
262 | } |
||
263 | |||
264 | // Create one query per each table, $columns not used. We want just the ID and the ClassName of the object from this query. |
||
265 | foreach ($tables as $tableName => $columns) { |
||
266 | $class = DataObject::getSchema()->tableClass($tableName); |
||
267 | $join = $this->fullTextSearchMSSQL($tableName, $keywords); |
||
268 | if (!$join) { |
||
269 | return new PaginatedList($results); |
||
270 | } // avoid "Null or empty full-text predicate" |
||
271 | |||
272 | // Check if we need to add ShowInSearch |
||
273 | $where = null; |
||
274 | if ($class === 'SilverStripe\\CMS\\Model\\SiteTree') { |
||
275 | $where = array("\"$tableName\".\"ShowInSearch\"!=0"); |
||
276 | } elseif ($class === 'SilverStripe\\Assets\\File') { |
||
277 | // File.ShowInSearch was added later, keep the database driver backwards compatible |
||
278 | // by checking for its existence first |
||
279 | $fields = $this->getSchemaManager()->fieldList($tableName); |
||
280 | if (array_key_exists('ShowInSearch', $fields)) { |
||
281 | $where = array("\"$tableName\".\"ShowInSearch\"!=0"); |
||
282 | } |
||
283 | } |
||
284 | |||
285 | $queries[$tableName] = DataList::create($class)->where($where)->dataQuery()->query(); |
||
0 ignored issues
–
show
|
|||
286 | $queries[$tableName]->setOrderBy(array()); |
||
287 | |||
288 | // Join with CONTAINSTABLE, a full text searcher that includes relevance factor |
||
289 | $queries[$tableName]->setFrom(array("\"$tableName\" INNER JOIN $join AS \"ft\" ON \"$tableName\".\"ID\"=\"ft\".\"KEY\"")); |
||
290 | // Join with the base class if needed, as we want to test agains the ClassName |
||
291 | if ($tableName != $tableName) { |
||
292 | $queries[$tableName]->setFrom("INNER JOIN \"$tableName\" ON \"$tableName\".\"ID\"=\"$tableName\".\"ID\""); |
||
293 | } |
||
294 | |||
295 | $queries[$tableName]->setSelect(array("\"$tableName\".\"ID\"")); |
||
296 | $queries[$tableName]->selectField("'$tableName'", 'Source'); |
||
297 | $queries[$tableName]->selectField('Rank', 'Relevance'); |
||
298 | if ($extraFilter) { |
||
299 | $queries[$tableName]->addWhere($extraFilter); |
||
300 | } |
||
301 | if (count($allClassesToSearch)) { |
||
302 | $classesPlaceholder = DB::placeholders($allClassesToSearch); |
||
303 | $queries[$tableName]->addWhere(array( |
||
304 | "\"$tableName\".\"ClassName\" IN ($classesPlaceholder)" => |
||
305 | $allClassesToSearch |
||
306 | )); |
||
307 | } |
||
308 | // Reset the parameters that would get in the way |
||
309 | } |
||
310 | |||
311 | // Generate SQL |
||
312 | $querySQLs = array(); |
||
313 | $queryParameters = array(); |
||
314 | foreach ($queries as $query) { |
||
315 | /** @var SQLSelect $query */ |
||
316 | $querySQLs[] = $query->sql($parameters); |
||
317 | $queryParameters = array_merge($queryParameters, $parameters); |
||
318 | } |
||
319 | |||
320 | // Unite the SQL |
||
321 | $fullQuery = implode(" UNION ", $querySQLs) . " ORDER BY $sortBy"; |
||
322 | |||
323 | // Perform the search |
||
324 | $result = $this->preparedQuery($fullQuery, $queryParameters); |
||
325 | |||
326 | // Regenerate DataObjectSet - watch out, numRecords doesn't work on sqlsrv driver on Windows. |
||
327 | $current = -1; |
||
328 | $objects = array(); |
||
329 | foreach ($result as $row) { |
||
330 | $current++; |
||
331 | |||
332 | // Select a subset for paging |
||
333 | if ($current >= $start && $current < $start + $pageLength) { |
||
334 | $objects[] = DataObject::get_by_id($row['Source'], $row['ID']); |
||
335 | } |
||
336 | } |
||
337 | |||
338 | if (isset($objects)) { |
||
339 | $results = new ArrayList($objects); |
||
340 | } else { |
||
341 | $results = new ArrayList(); |
||
342 | } |
||
343 | $list = new PaginatedList($results); |
||
344 | $list->setPageStart($start); |
||
345 | $list->setPageLength($pageLength); |
||
346 | $list->setTotalItems($current+1); |
||
347 | return $list; |
||
348 | } |
||
349 | |||
350 | /** |
||
351 | * Allow auto-increment primary key editing on the given table. |
||
352 | * Some databases need to enable this specially. |
||
353 | * |
||
354 | * @param string $table The name of the table to have PK editing allowed on |
||
355 | * @param bool $allow True to start, false to finish |
||
356 | */ |
||
357 | public function allowPrimaryKeyEditing($table, $allow = true) |
||
358 | { |
||
359 | $this->query("SET IDENTITY_INSERT \"$table\" " . ($allow ? "ON" : "OFF")); |
||
360 | } |
||
361 | |||
362 | /** |
||
363 | * Returns a SQL fragment for querying a fulltext search index |
||
364 | * |
||
365 | * @param string $tableName specific - table name |
||
366 | * @param string $keywords The search query |
||
367 | * @param array $fields The list of field names to search on, or null to include all |
||
368 | * @return string Clause, or null if keyword set is empty or the string with JOIN clause to be added to SQL query |
||
369 | */ |
||
370 | public function fullTextSearchMSSQL($tableName, $keywords, $fields = null) |
||
371 | { |
||
372 | // Make sure we are getting an array of fields |
||
373 | if (isset($fields) && !is_array($fields)) { |
||
374 | $fields = array($fields); |
||
375 | } |
||
376 | |||
377 | // Strip unfriendly characters, SQLServer "CONTAINS" predicate will crash on & and | and ignore others anyway. |
||
378 | if (function_exists('mb_ereg_replace')) { |
||
379 | $keywords = mb_ereg_replace('[^\w\s]', '', trim($keywords)); |
||
380 | } else { |
||
381 | $keywords = $this->escapeString(str_replace(array('&', '|', '!', '"', '\''), '', trim($keywords))); |
||
382 | } |
||
383 | |||
384 | // Remove stopwords, concat with ANDs |
||
385 | $keywordList = explode(' ', $keywords); |
||
386 | $keywordList = $this->removeStopwords($keywordList); |
||
387 | |||
388 | // remove any empty values from the array |
||
389 | $keywordList = array_filter($keywordList); |
||
390 | if (empty($keywordList)) { |
||
391 | return null; |
||
392 | } |
||
393 | |||
394 | $keywords = implode(' AND ', $keywordList); |
||
395 | if ($fields) { |
||
396 | $fieldNames = '"' . implode('", "', $fields) . '"'; |
||
397 | } else { |
||
398 | $fieldNames = "*"; |
||
399 | } |
||
400 | |||
401 | return "CONTAINSTABLE(\"$tableName\", ($fieldNames), '$keywords')"; |
||
402 | } |
||
403 | |||
404 | /** |
||
405 | * Remove stopwords that would kill a MSSQL full-text query |
||
406 | * |
||
407 | * @param array $keywords |
||
408 | * |
||
409 | * @return array $keywords with stopwords removed |
||
410 | */ |
||
411 | public function removeStopwords($keywords) |
||
412 | { |
||
413 | $goodKeywords = array(); |
||
414 | foreach ($keywords as $keyword) { |
||
415 | if (in_array($keyword, self::$noiseWords)) { |
||
416 | continue; |
||
417 | } |
||
418 | $goodKeywords[] = trim($keyword); |
||
419 | } |
||
420 | return $goodKeywords; |
||
421 | } |
||
422 | |||
423 | /** |
||
424 | * Does this database support transactions? |
||
425 | */ |
||
426 | public function supportsTransactions() |
||
427 | { |
||
428 | return $this->supportsTransactions; |
||
429 | } |
||
430 | |||
431 | /** |
||
432 | * This is a quick lookup to discover if the database supports particular extensions |
||
433 | * Currently, MSSQL supports no extensions |
||
434 | * |
||
435 | * @param array $extensions List of extensions to check for support of. The key of this array |
||
436 | * will be an extension name, and the value the configuration for that extension. This |
||
437 | * could be one of partitions, tablespaces, or clustering |
||
438 | * @return boolean Flag indicating support for all of the above |
||
439 | */ |
||
440 | public function supportsExtensions($extensions = array('partitions', 'tablespaces', 'clustering')) |
||
441 | { |
||
442 | if (isset($extensions['partitions'])) { |
||
443 | return false; |
||
444 | } elseif (isset($extensions['tablespaces'])) { |
||
445 | return false; |
||
446 | } elseif (isset($extensions['clustering'])) { |
||
447 | return false; |
||
448 | } else { |
||
449 | return false; |
||
450 | } |
||
451 | } |
||
452 | |||
453 | /** |
||
454 | * Start transaction. READ ONLY not supported. |
||
455 | * |
||
456 | * @param bool $transactionMode |
||
457 | * @param bool $sessionCharacteristics |
||
458 | */ |
||
459 | public function transactionStart($transactionMode = false, $sessionCharacteristics = false) |
||
460 | { |
||
461 | View Code Duplication | if ($this->transactionNesting > 0) { |
|
462 | $this->transactionSavepoint('NESTEDTRANSACTION' . $this->transactionNesting); |
||
463 | } elseif ($this->connector instanceof SQLServerConnector) { |
||
464 | $this->connector->transactionStart(); |
||
465 | } else { |
||
466 | $this->query('BEGIN TRANSACTION'); |
||
467 | } |
||
468 | ++$this->transactionNesting; |
||
469 | } |
||
470 | |||
471 | public function transactionSavepoint($savepoint) |
||
472 | { |
||
473 | $this->query("SAVE TRANSACTION \"$savepoint\""); |
||
474 | } |
||
475 | |||
476 | public function transactionRollback($savepoint = false) |
||
477 | { |
||
478 | // Named transaction |
||
479 | if ($savepoint) { |
||
480 | $this->query("ROLLBACK TRANSACTION \"$savepoint\""); |
||
481 | return true; |
||
482 | } |
||
483 | |||
484 | // Fail if transaction isn't available |
||
485 | if (!$this->transactionNesting) { |
||
486 | return false; |
||
487 | } |
||
488 | --$this->transactionNesting; |
||
489 | View Code Duplication | if ($this->transactionNesting > 0) { |
|
490 | $this->transactionRollback('NESTEDTRANSACTION' . $this->transactionNesting); |
||
491 | } elseif ($this->connector instanceof SQLServerConnector) { |
||
492 | $this->connector->transactionRollback(); |
||
493 | } else { |
||
494 | $this->query('ROLLBACK TRANSACTION'); |
||
495 | } |
||
496 | return true; |
||
497 | } |
||
498 | |||
499 | public function transactionEnd($chain = false) |
||
500 | { |
||
501 | // Fail if transaction isn't available |
||
502 | if (!$this->transactionNesting) { |
||
503 | return false; |
||
504 | } |
||
505 | --$this->transactionNesting; |
||
506 | View Code Duplication | if ($this->transactionNesting <= 0) { |
|
507 | $this->transactionNesting = 0; |
||
508 | if ($this->connector instanceof SQLServerConnector) { |
||
509 | $this->connector->transactionEnd(); |
||
510 | } else { |
||
511 | $this->query('COMMIT TRANSACTION'); |
||
512 | } |
||
513 | } |
||
514 | return true; |
||
515 | } |
||
516 | |||
517 | /** |
||
518 | * In error condition, set transactionNesting to zero |
||
519 | */ |
||
520 | protected function resetTransactionNesting() |
||
521 | { |
||
522 | $this->transactionNesting = 0; |
||
523 | } |
||
524 | |||
525 | public function query($sql, $errorLevel = E_USER_ERROR) |
||
526 | { |
||
527 | $this->inspectQuery($sql); |
||
528 | return parent::query($sql, $errorLevel); |
||
529 | } |
||
530 | |||
531 | public function preparedQuery($sql, $parameters, $errorLevel = E_USER_ERROR) |
||
532 | { |
||
533 | $this->inspectQuery($sql); |
||
534 | return parent::preparedQuery($sql, $parameters, $errorLevel); |
||
535 | } |
||
536 | |||
537 | protected function inspectQuery($sql) |
||
538 | { |
||
539 | // Any DDL discards transactions. |
||
540 | $isDDL = $this->getConnector()->isQueryDDL($sql); |
||
541 | if ($isDDL) { |
||
542 | $this->resetTransactionNesting(); |
||
543 | } |
||
544 | } |
||
545 | |||
546 | public function comparisonClause($field, $value, $exact = false, $negate = false, $caseSensitive = null, $parameterised = false) |
||
547 | { |
||
548 | if ($exact) { |
||
549 | $comp = ($negate) ? '!=' : '='; |
||
550 | } else { |
||
551 | $comp = 'LIKE'; |
||
552 | if ($negate) { |
||
553 | $comp = 'NOT ' . $comp; |
||
554 | } |
||
555 | } |
||
556 | |||
557 | // Field definitions are case insensitive by default, |
||
558 | // change used collation for case sensitive searches. |
||
559 | $collateClause = ''; |
||
560 | if ($caseSensitive === true) { |
||
561 | if (self::get_collation()) { |
||
562 | $collation = preg_replace('/_CI_/', '_CS_', self::get_collation()); |
||
563 | } else { |
||
564 | $collation = 'Latin1_General_CS_AS'; |
||
565 | } |
||
566 | $collateClause = ' COLLATE ' . $collation; |
||
567 | } elseif ($caseSensitive === false) { |
||
568 | if (self::get_collation()) { |
||
569 | $collation = preg_replace('/_CS_/', '_CI_', self::get_collation()); |
||
570 | } else { |
||
571 | $collation = 'Latin1_General_CI_AS'; |
||
572 | } |
||
573 | $collateClause = ' COLLATE ' . $collation; |
||
574 | } |
||
575 | |||
576 | $clause = sprintf("%s %s %s", $field, $comp, $parameterised ? '?' : "'$value'"); |
||
577 | if ($collateClause) { |
||
578 | $clause .= $collateClause; |
||
579 | } |
||
580 | |||
581 | return $clause; |
||
582 | } |
||
583 | |||
584 | /** |
||
585 | * Function to return an SQL datetime expression for MSSQL |
||
586 | * used for querying a datetime in a certain format |
||
587 | * |
||
588 | * @param string $date to be formated, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"' |
||
589 | * @param string $format to be used, supported specifiers: |
||
590 | * %Y = Year (four digits) |
||
591 | * %m = Month (01..12) |
||
592 | * %d = Day (01..31) |
||
593 | * %H = Hour (00..23) |
||
594 | * %i = Minutes (00..59) |
||
595 | * %s = Seconds (00..59) |
||
596 | * %U = unix timestamp, can only be used on it's own |
||
597 | * @return string SQL datetime expression to query for a formatted datetime |
||
598 | */ |
||
599 | public function formattedDatetimeClause($date, $format) |
||
600 | { |
||
601 | preg_match_all('/%(.)/', $format, $matches); |
||
602 | foreach ($matches[1] as $match) { |
||
603 | if (array_search($match, array('Y', 'm', 'd', 'H', 'i', 's', 'U')) === false) { |
||
604 | user_error('formattedDatetimeClause(): unsupported format character %' . $match, E_USER_WARNING); |
||
605 | } |
||
606 | } |
||
607 | |||
608 | View Code Duplication | if (preg_match('/^now$/i', $date)) { |
|
609 | $date = "CURRENT_TIMESTAMP"; |
||
610 | } elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date)) { |
||
611 | $date = "'$date.000'"; |
||
612 | } |
||
613 | |||
614 | if ($format == '%U') { |
||
615 | return "DATEDIFF(s, '1970-01-01 00:00:00', DATEADD(hour, DATEDIFF(hour, GETDATE(), GETUTCDATE()), $date))"; |
||
616 | } |
||
617 | |||
618 | $trans = array( |
||
619 | 'Y' => 'yy', |
||
620 | 'm' => 'mm', |
||
621 | 'd' => 'dd', |
||
622 | 'H' => 'hh', |
||
623 | 'i' => 'mi', |
||
624 | 's' => 'ss', |
||
625 | ); |
||
626 | |||
627 | $strings = array(); |
||
628 | $buffer = $format; |
||
629 | while (strlen($buffer)) { |
||
630 | if (substr($buffer, 0, 1) == '%') { |
||
631 | $f = substr($buffer, 1, 1); |
||
632 | $flen = $f == 'Y' ? 4 : 2; |
||
633 | $strings[] = "RIGHT('0' + CAST(DATEPART({$trans[$f]},$date) AS VARCHAR), $flen)"; |
||
634 | $buffer = substr($buffer, 2); |
||
635 | } else { |
||
636 | $pos = strpos($buffer, '%'); |
||
637 | if ($pos === false) { |
||
638 | $strings[] = $buffer; |
||
639 | $buffer = ''; |
||
640 | } else { |
||
641 | $strings[] = "'".substr($buffer, 0, $pos)."'"; |
||
642 | $buffer = substr($buffer, $pos); |
||
643 | } |
||
644 | } |
||
645 | } |
||
646 | |||
647 | return '(' . implode(' + ', $strings) . ')'; |
||
648 | } |
||
649 | |||
650 | /** |
||
651 | * Function to return an SQL datetime expression for MSSQL. |
||
652 | * used for querying a datetime addition |
||
653 | * |
||
654 | * @param string $date, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"' |
||
655 | * @param string $interval to be added, use the format [sign][integer] [qualifier], e.g. -1 Day, +15 minutes, +1 YEAR |
||
656 | * supported qualifiers: |
||
657 | * - years |
||
658 | * - months |
||
659 | * - days |
||
660 | * - hours |
||
661 | * - minutes |
||
662 | * - seconds |
||
663 | * This includes the singular forms as well |
||
664 | * @return string SQL datetime expression to query for a datetime (YYYY-MM-DD hh:mm:ss) which is the result of the addition |
||
665 | */ |
||
666 | public function datetimeIntervalClause($date, $interval) |
||
667 | { |
||
668 | $trans = array( |
||
669 | 'year' => 'yy', |
||
670 | 'month' => 'mm', |
||
671 | 'day' => 'dd', |
||
672 | 'hour' => 'hh', |
||
673 | 'minute' => 'mi', |
||
674 | 'second' => 'ss', |
||
675 | ); |
||
676 | |||
677 | $singularinterval = preg_replace('/(year|month|day|hour|minute|second)s/i', '$1', $interval); |
||
678 | |||
679 | if ( |
||
680 | !($params = preg_match('/([-+]\d+) (\w+)/i', $singularinterval, $matches)) || |
||
681 | !isset($trans[strtolower($matches[2])]) |
||
682 | ) { |
||
683 | user_error('datetimeIntervalClause(): invalid interval ' . $interval, E_USER_WARNING); |
||
684 | } |
||
685 | |||
686 | View Code Duplication | if (preg_match('/^now$/i', $date)) { |
|
687 | $date = "CURRENT_TIMESTAMP"; |
||
688 | } elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date)) { |
||
689 | $date = "'$date'"; |
||
690 | } |
||
691 | |||
692 | return "CONVERT(VARCHAR, DATEADD(" . $trans[strtolower($matches[2])] . ", " . (int)$matches[1] . ", $date), 120)"; |
||
693 | } |
||
694 | |||
695 | /** |
||
696 | * Function to return an SQL datetime expression for MSSQL. |
||
697 | * used for querying a datetime substraction |
||
698 | * |
||
699 | * @param string $date1, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"' |
||
700 | * @param string $date2 to be substracted of $date1, can be either 'now', literal datetime like '1973-10-14 10:30:00' or field name, e.g. '"SiteTree"."Created"' |
||
701 | * @return string SQL datetime expression to query for the interval between $date1 and $date2 in seconds which is the result of the substraction |
||
702 | */ |
||
703 | public function datetimeDifferenceClause($date1, $date2) |
||
704 | { |
||
705 | View Code Duplication | if (preg_match('/^now$/i', $date1)) { |
|
706 | $date1 = "CURRENT_TIMESTAMP"; |
||
707 | } elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date1)) { |
||
708 | $date1 = "'$date1'"; |
||
709 | } |
||
710 | |||
711 | View Code Duplication | if (preg_match('/^now$/i', $date2)) { |
|
712 | $date2 = "CURRENT_TIMESTAMP"; |
||
713 | } elseif (preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/i', $date2)) { |
||
714 | $date2 = "'$date2'"; |
||
715 | } |
||
716 | |||
717 | return "DATEDIFF(s, $date2, $date1)"; |
||
718 | } |
||
719 | } |
||
720 |
If a method or function can return multiple different values and unless you are sure that you only can receive a single value in this context, we recommend to add an additional type check:
If this a common case that PHP Analyzer should handle natively, please let us know by opening an issue.