1 | <?php |
||||
2 | /* vim: set expandtab sw=4 ts=4 sts=4: */ |
||||
3 | /** |
||||
4 | * Handles Database Search |
||||
5 | * |
||||
6 | * @package PhpMyAdmin |
||||
7 | */ |
||||
8 | declare(strict_types=1); |
||||
9 | |||||
10 | namespace PhpMyAdmin\Database; |
||||
11 | |||||
12 | use PhpMyAdmin\DatabaseInterface; |
||||
13 | use PhpMyAdmin\Template; |
||||
14 | use PhpMyAdmin\Util; |
||||
15 | |||||
16 | /** |
||||
17 | * Class to handle database search |
||||
18 | * |
||||
19 | * @package PhpMyAdmin |
||||
20 | */ |
||||
21 | class Search |
||||
22 | { |
||||
23 | /** |
||||
24 | * Database name |
||||
25 | * |
||||
26 | * @access private |
||||
27 | * @var string |
||||
28 | */ |
||||
29 | private $db; |
||||
30 | |||||
31 | /** |
||||
32 | * Table Names |
||||
33 | * |
||||
34 | * @access private |
||||
35 | * @var array |
||||
36 | */ |
||||
37 | private $tablesNamesOnly; |
||||
38 | |||||
39 | /** |
||||
40 | * Type of search |
||||
41 | * |
||||
42 | * @access private |
||||
43 | * @var array |
||||
44 | */ |
||||
45 | private $searchTypes; |
||||
46 | |||||
47 | /** |
||||
48 | * Already set search type |
||||
49 | * |
||||
50 | * @access private |
||||
51 | * @var integer |
||||
52 | */ |
||||
53 | private $criteriaSearchType; |
||||
54 | |||||
55 | /** |
||||
56 | * Already set search type's description |
||||
57 | * |
||||
58 | * @access private |
||||
59 | * @var string |
||||
60 | */ |
||||
61 | private $searchTypeDescription; |
||||
62 | |||||
63 | /** |
||||
64 | * Search string/regexp |
||||
65 | * |
||||
66 | * @access private |
||||
67 | * @var string |
||||
68 | */ |
||||
69 | private $criteriaSearchString; |
||||
70 | |||||
71 | /** |
||||
72 | * Criteria Tables to search in |
||||
73 | * |
||||
74 | * @access private |
||||
75 | * @var array |
||||
76 | */ |
||||
77 | private $criteriaTables; |
||||
78 | |||||
79 | /** |
||||
80 | * Restrict the search to this column |
||||
81 | * |
||||
82 | * @access private |
||||
83 | * @var string |
||||
84 | */ |
||||
85 | private $criteriaColumnName; |
||||
86 | |||||
87 | /** |
||||
88 | * @var DatabaseInterface |
||||
89 | */ |
||||
90 | private $dbi; |
||||
91 | |||||
92 | /** |
||||
93 | * @var Template |
||||
94 | */ |
||||
95 | public $template; |
||||
96 | |||||
97 | /** |
||||
98 | * Public Constructor |
||||
99 | * |
||||
100 | * @param DatabaseInterface $dbi DatabaseInterface object |
||||
101 | * @param string $db Database name |
||||
102 | */ |
||||
103 | public function __construct(DatabaseInterface $dbi, $db) |
||||
104 | { |
||||
105 | $this->db = $db; |
||||
106 | $this->dbi = $dbi; |
||||
107 | $this->searchTypes = [ |
||||
108 | '1' => __('at least one of the words'), |
||||
109 | '2' => __('all of the words'), |
||||
110 | '3' => __('the exact phrase as substring'), |
||||
111 | '4' => __('the exact phrase as whole field'), |
||||
112 | '5' => __('as regular expression'), |
||||
113 | ]; |
||||
114 | $this->template = new Template(); |
||||
115 | // Sets criteria parameters |
||||
116 | $this->setSearchParams(); |
||||
117 | } |
||||
118 | |||||
119 | /** |
||||
120 | * Sets search parameters |
||||
121 | * |
||||
122 | * @return void |
||||
123 | */ |
||||
124 | private function setSearchParams() |
||||
125 | { |
||||
126 | $this->tablesNamesOnly = $this->dbi->getTables($this->db); |
||||
127 | |||||
128 | if (empty($_REQUEST['criteriaSearchType']) |
||||
129 | || ! is_string($_REQUEST['criteriaSearchType']) |
||||
130 | || ! array_key_exists( |
||||
131 | $_REQUEST['criteriaSearchType'], |
||||
132 | $this->searchTypes |
||||
133 | ) |
||||
134 | ) { |
||||
135 | $this->criteriaSearchType = 1; |
||||
136 | unset($_REQUEST['submit_search']); |
||||
137 | } else { |
||||
138 | $this->criteriaSearchType = (int) $_REQUEST['criteriaSearchType']; |
||||
139 | $this->searchTypeDescription |
||||
140 | = $this->searchTypes[$_REQUEST['criteriaSearchType']]; |
||||
141 | } |
||||
142 | |||||
143 | if (empty($_REQUEST['criteriaSearchString']) |
||||
144 | || ! is_string($_REQUEST['criteriaSearchString']) |
||||
145 | ) { |
||||
146 | $this->criteriaSearchString = ''; |
||||
147 | unset($_REQUEST['submit_search']); |
||||
148 | } else { |
||||
149 | $this->criteriaSearchString = $_REQUEST['criteriaSearchString']; |
||||
150 | } |
||||
151 | |||||
152 | $this->criteriaTables = []; |
||||
153 | if (empty($_REQUEST['criteriaTables']) |
||||
154 | || ! is_array($_REQUEST['criteriaTables']) |
||||
155 | ) { |
||||
156 | unset($_REQUEST['submit_search']); |
||||
157 | } else { |
||||
158 | $this->criteriaTables = array_intersect( |
||||
159 | $_REQUEST['criteriaTables'], |
||||
160 | $this->tablesNamesOnly |
||||
161 | ); |
||||
162 | } |
||||
163 | |||||
164 | if (empty($_REQUEST['criteriaColumnName']) |
||||
165 | || ! is_string($_REQUEST['criteriaColumnName']) |
||||
166 | ) { |
||||
167 | unset($this->criteriaColumnName); |
||||
168 | } else { |
||||
169 | $this->criteriaColumnName = $this->dbi->escapeString( |
||||
170 | $_REQUEST['criteriaColumnName'] |
||||
171 | ); |
||||
172 | } |
||||
173 | } |
||||
174 | |||||
175 | /** |
||||
176 | * Builds the SQL search query |
||||
177 | * |
||||
178 | * @param string $table The table name |
||||
179 | * |
||||
180 | * @return array 3 SQL queries (for count, display and delete results) |
||||
181 | * |
||||
182 | * @todo can we make use of fulltextsearch IN BOOLEAN MODE for this? |
||||
183 | * PMA_backquote |
||||
184 | * DatabaseInterface::freeResult |
||||
185 | * DatabaseInterface::fetchAssoc |
||||
186 | * $GLOBALS['db'] |
||||
187 | * explode |
||||
188 | * count |
||||
189 | * strlen |
||||
190 | */ |
||||
191 | private function getSearchSqls($table) |
||||
192 | { |
||||
193 | // Statement types |
||||
194 | $sqlstr_select = 'SELECT'; |
||||
195 | $sqlstr_delete = 'DELETE'; |
||||
196 | // Table to use |
||||
197 | $sqlstr_from = ' FROM ' |
||||
198 | . Util::backquote($GLOBALS['db']) . '.' |
||||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||||
199 | . Util::backquote($table); |
||||
200 | // Gets where clause for the query |
||||
201 | $where_clause = $this->getWhereClause($table); |
||||
202 | // Builds complete queries |
||||
203 | $sql = []; |
||||
204 | $sql['select_columns'] = $sqlstr_select . ' * ' . $sqlstr_from |
||||
205 | . $where_clause; |
||||
206 | // here, I think we need to still use the COUNT clause, even for |
||||
207 | // VIEWs, anyway we have a WHERE clause that should limit results |
||||
208 | $sql['select_count'] = $sqlstr_select . ' COUNT(*) AS `count`' |
||||
209 | . $sqlstr_from . $where_clause; |
||||
210 | $sql['delete'] = $sqlstr_delete . $sqlstr_from . $where_clause; |
||||
211 | |||||
212 | return $sql; |
||||
213 | } |
||||
214 | |||||
215 | /** |
||||
216 | * Provides where clause for building SQL query |
||||
217 | * |
||||
218 | * @param string $table The table name |
||||
219 | * |
||||
220 | * @return string The generated where clause |
||||
221 | */ |
||||
222 | private function getWhereClause($table) |
||||
223 | { |
||||
224 | // Columns to select |
||||
225 | $allColumns = $this->dbi->getColumns($GLOBALS['db'], $table); |
||||
226 | $likeClauses = []; |
||||
227 | // Based on search type, decide like/regex & '%'/'' |
||||
228 | $like_or_regex = (($this->criteriaSearchType == 5) ? 'REGEXP' : 'LIKE'); |
||||
229 | $automatic_wildcard = (($this->criteriaSearchType < 4) ? '%' : ''); |
||||
230 | // For "as regular expression" (search option 5), LIKE won't be used |
||||
231 | // Usage example: If user is searching for a literal $ in a regexp search, |
||||
232 | // he should enter \$ as the value. |
||||
233 | $criteriaSearchStringEscaped = $this->dbi->escapeString( |
||||
234 | $this->criteriaSearchString |
||||
235 | ); |
||||
236 | // Extract search words or pattern |
||||
237 | $search_words = (($this->criteriaSearchType > 2) |
||||
238 | ? [$criteriaSearchStringEscaped] |
||||
239 | : explode(' ', $criteriaSearchStringEscaped)); |
||||
240 | |||||
241 | foreach ($search_words as $search_word) { |
||||
242 | // Eliminates empty values |
||||
243 | if (strlen($search_word) === 0) { |
||||
244 | continue; |
||||
245 | } |
||||
246 | $likeClausesPerColumn = []; |
||||
247 | // for each column in the table |
||||
248 | foreach ($allColumns as $column) { |
||||
249 | if (! isset($this->criteriaColumnName) |
||||
250 | || strlen($this->criteriaColumnName) === 0 |
||||
251 | || $column['Field'] == $this->criteriaColumnName |
||||
252 | ) { |
||||
253 | $column = 'CONVERT(' . Util::backquote($column['Field']) |
||||
0 ignored issues
–
show
Are you sure
PhpMyAdmin\Util::backquote($column['Field']) of type array|mixed|string can be used in concatenation ?
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
254 | . ' USING utf8)'; |
||||
255 | $likeClausesPerColumn[] = $column . ' ' . $like_or_regex . ' ' |
||||
256 | . "'" |
||||
257 | . $automatic_wildcard . $search_word . $automatic_wildcard |
||||
258 | . "'"; |
||||
259 | } |
||||
260 | } // end for |
||||
261 | if (count($likeClausesPerColumn) > 0) { |
||||
262 | $likeClauses[] = implode(' OR ', $likeClausesPerColumn); |
||||
263 | } |
||||
264 | } // end for |
||||
265 | // Use 'OR' if 'at least one word' is to be searched, else use 'AND' |
||||
266 | $implode_str = ($this->criteriaSearchType == 1 ? ' OR ' : ' AND '); |
||||
267 | if (empty($likeClauses)) { |
||||
268 | // this could happen when the "inside column" does not exist |
||||
269 | // in any selected tables |
||||
270 | $where_clause = ' WHERE FALSE'; |
||||
271 | } else { |
||||
272 | $where_clause = ' WHERE (' |
||||
273 | . implode(') ' . $implode_str . ' (', $likeClauses) |
||||
274 | . ')'; |
||||
275 | } |
||||
276 | return $where_clause; |
||||
277 | } |
||||
278 | |||||
279 | /** |
||||
280 | * Displays database search results |
||||
281 | * |
||||
282 | * @return string HTML for search results |
||||
283 | */ |
||||
284 | public function getSearchResults() |
||||
285 | { |
||||
286 | $resultTotal = 0; |
||||
287 | $rows = []; |
||||
288 | // For each table selected as search criteria |
||||
289 | foreach ($this->criteriaTables as $eachTable) { |
||||
290 | // Gets the SQL statements |
||||
291 | $newSearchSqls = $this->getSearchSqls($eachTable); |
||||
292 | // Executes the "COUNT" statement |
||||
293 | $resultCount = intval($this->dbi->fetchValue( |
||||
294 | $newSearchSqls['select_count'] |
||||
295 | )); |
||||
296 | $resultTotal += $resultCount; |
||||
297 | // Gets the result row's HTML for a table |
||||
298 | $rows[] = [ |
||||
299 | 'table' => htmlspecialchars($eachTable), |
||||
300 | 'new_search_sqls' => $newSearchSqls, |
||||
301 | 'result_count' => $resultCount, |
||||
302 | ]; |
||||
303 | } |
||||
304 | |||||
305 | return $this->template->render('database/search/results', [ |
||||
306 | 'db' => $this->db, |
||||
307 | 'rows' => $rows, |
||||
308 | 'result_total' => $resultTotal, |
||||
309 | 'criteria_tables' => $this->criteriaTables, |
||||
310 | 'criteria_search_string' => htmlspecialchars($this->criteriaSearchString), |
||||
311 | 'search_type_description' => $this->searchTypeDescription, |
||||
312 | ]); |
||||
313 | } |
||||
314 | |||||
315 | /** |
||||
316 | * Provides the main search form's html |
||||
317 | * |
||||
318 | * @return string HTML for selection form |
||||
319 | */ |
||||
320 | public function getMainHtml() |
||||
321 | { |
||||
322 | $choices = [ |
||||
323 | '1' => $this->searchTypes[1] . ' ' |
||||
324 | . Util::showHint( |
||||
325 | __('Words are separated by a space character (" ").') |
||||
326 | ), |
||||
327 | '2' => $this->searchTypes[2] . ' ' |
||||
328 | . Util::showHint( |
||||
329 | __('Words are separated by a space character (" ").') |
||||
330 | ), |
||||
331 | '3' => $this->searchTypes[3], |
||||
332 | '4' => $this->searchTypes[4], |
||||
333 | '5' => $this->searchTypes[5] . ' ' . Util::showMySQLDocu('Regexp') |
||||
334 | ]; |
||||
335 | return $this->template->render('database/search/main', [ |
||||
336 | 'db' => $this->db, |
||||
337 | 'choices' => $choices, |
||||
338 | 'criteria_search_string' => $this->criteriaSearchString, |
||||
339 | 'criteria_search_type' => $this->criteriaSearchType, |
||||
340 | 'criteria_tables' => $this->criteriaTables, |
||||
341 | 'tables_names_only' => $this->tablesNamesOnly, |
||||
342 | 'criteria_column_name' => isset($this->criteriaColumnName) |
||||
343 | ? $this->criteriaColumnName : null, |
||||
344 | ]); |
||||
345 | } |
||||
346 | } |
||||
347 |