Search.php 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356
  1. <?php
  2. declare(strict_types=1);
  3. namespace PhpMyAdmin\Table;
  4. use PhpMyAdmin\DatabaseInterface;
  5. use PhpMyAdmin\Util;
  6. use PhpMyAdmin\Utils\Gis;
  7. use function count;
  8. use function explode;
  9. use function implode;
  10. use function in_array;
  11. use function is_array;
  12. use function mb_strpos;
  13. use function preg_match;
  14. use function str_contains;
  15. use function str_replace;
  16. use function strlen;
  17. use function strncasecmp;
  18. use function trim;
  19. final class Search
  20. {
  21. /** @var DatabaseInterface */
  22. private $dbi;
  23. public function __construct(DatabaseInterface $dbi)
  24. {
  25. $this->dbi = $dbi;
  26. }
  27. /**
  28. * Builds the sql search query from the post parameters
  29. *
  30. * @return string the generated SQL query
  31. */
  32. public function buildSqlQuery(): string
  33. {
  34. $sql_query = 'SELECT ';
  35. // If only distinct values are needed
  36. $is_distinct = isset($_POST['distinct']) ? 'true' : 'false';
  37. if ($is_distinct === 'true') {
  38. $sql_query .= 'DISTINCT ';
  39. }
  40. // if all column names were selected to display, we do a 'SELECT *'
  41. // (more efficient and this helps prevent a problem in IE
  42. // if one of the rows is edited and we come back to the Select results)
  43. if (isset($_POST['zoom_submit']) || ! empty($_POST['displayAllColumns'])) {
  44. $sql_query .= '*';
  45. } else {
  46. $columnsToDisplay = $_POST['columnsToDisplay'];
  47. $quotedColumns = [];
  48. foreach ($columnsToDisplay as $column) {
  49. $quotedColumns[] = Util::backquote($column);
  50. }
  51. $sql_query .= implode(', ', $quotedColumns);
  52. }
  53. $sql_query .= ' FROM '
  54. . Util::backquote($_POST['table']);
  55. $whereClause = $this->generateWhereClause();
  56. $sql_query .= $whereClause;
  57. // if the search results are to be ordered
  58. if (isset($_POST['orderByColumn']) && $_POST['orderByColumn'] !== '--nil--') {
  59. $sql_query .= ' ORDER BY '
  60. . Util::backquote($_POST['orderByColumn'])
  61. . ' ' . $_POST['order'];
  62. }
  63. return $sql_query;
  64. }
  65. /**
  66. * Generates the where clause for the SQL search query to be executed
  67. *
  68. * @return string the generated where clause
  69. */
  70. private function generateWhereClause(): string
  71. {
  72. if (isset($_POST['customWhereClause']) && trim($_POST['customWhereClause']) != '') {
  73. return ' WHERE ' . $_POST['customWhereClause'];
  74. }
  75. // If there are no search criteria set or no unary criteria operators,
  76. // return
  77. if (
  78. ! isset($_POST['criteriaColumnOperators'])
  79. || (
  80. ! isset($_POST['criteriaValues'])
  81. && ! isset($_POST['criteriaColumnOperators'])
  82. && ! isset($_POST['geom_func'])
  83. )
  84. ) {
  85. return '';
  86. }
  87. // else continue to form the where clause from column criteria values
  88. $fullWhereClause = [];
  89. foreach ($_POST['criteriaColumnOperators'] as $column_index => $operator) {
  90. $unaryFlag = $this->dbi->types->isUnaryOperator($operator);
  91. $tmp_geom_func = $_POST['geom_func'][$column_index] ?? null;
  92. $whereClause = $this->getWhereClause(
  93. $_POST['criteriaValues'][$column_index],
  94. $_POST['criteriaColumnNames'][$column_index],
  95. $_POST['criteriaColumnTypes'][$column_index],
  96. $operator,
  97. $unaryFlag,
  98. $tmp_geom_func
  99. );
  100. if (! $whereClause) {
  101. continue;
  102. }
  103. $fullWhereClause[] = $whereClause;
  104. }
  105. if (! empty($fullWhereClause)) {
  106. return ' WHERE ' . implode(' AND ', $fullWhereClause);
  107. }
  108. return '';
  109. }
  110. /**
  111. * Return the where clause for query generation based on the inputs provided.
  112. *
  113. * @param mixed $criteriaValues Search criteria input
  114. * @param string $names Name of the column on which search is submitted
  115. * @param string $types Type of the field
  116. * @param string $func_type Search function/operator
  117. * @param bool $unaryFlag Whether operator unary or not
  118. * @param string|null $geom_func Whether geometry functions should be applied
  119. *
  120. * @return string generated where clause.
  121. */
  122. private function getWhereClause(
  123. $criteriaValues,
  124. $names,
  125. $types,
  126. $func_type,
  127. $unaryFlag,
  128. $geom_func = null
  129. ): string {
  130. // If geometry function is set
  131. if (! empty($geom_func)) {
  132. return $this->getGeomWhereClause($criteriaValues, $names, $func_type, $types, $geom_func);
  133. }
  134. $backquoted_name = Util::backquote($names);
  135. $where = '';
  136. if ($unaryFlag) {
  137. $where = $backquoted_name . ' ' . $func_type;
  138. } elseif (strncasecmp($types, 'enum', 4) == 0 && ! empty($criteriaValues)) {
  139. $where = $backquoted_name;
  140. $where .= $this->getEnumWhereClause($criteriaValues, $func_type);
  141. } elseif ($criteriaValues != '') {
  142. // For these types we quote the value. Even if it's another type
  143. // (like INT), for a LIKE we always quote the value. MySQL converts
  144. // strings to numbers and numbers to strings as necessary
  145. // during the comparison
  146. if (
  147. preg_match('@char|binary|blob|text|set|date|time|year|uuid@i', $types)
  148. || mb_strpos(' ' . $func_type, 'LIKE')
  149. ) {
  150. $quot = '\'';
  151. } else {
  152. $quot = '';
  153. }
  154. // LIKE %...%
  155. if ($func_type === 'LIKE %...%') {
  156. $func_type = 'LIKE';
  157. $criteriaValues = '%' . $criteriaValues . '%';
  158. }
  159. if ($func_type === 'NOT LIKE %...%') {
  160. $func_type = 'NOT LIKE';
  161. $criteriaValues = '%' . $criteriaValues . '%';
  162. }
  163. if ($func_type === 'REGEXP ^...$') {
  164. $func_type = 'REGEXP';
  165. $criteriaValues = '^' . $criteriaValues . '$';
  166. }
  167. if (
  168. $func_type !== 'IN (...)'
  169. && $func_type !== 'NOT IN (...)'
  170. && $func_type !== 'BETWEEN'
  171. && $func_type !== 'NOT BETWEEN'
  172. ) {
  173. return $backquoted_name . ' ' . $func_type . ' ' . $quot
  174. . $this->dbi->escapeString($criteriaValues) . $quot;
  175. }
  176. $func_type = str_replace(' (...)', '', $func_type);
  177. //Don't explode if this is already an array
  178. //(Case for (NOT) IN/BETWEEN.)
  179. if (is_array($criteriaValues)) {
  180. $values = $criteriaValues;
  181. } else {
  182. $values = explode(',', $criteriaValues);
  183. }
  184. // quote values one by one
  185. $emptyKey = false;
  186. foreach ($values as $key => &$value) {
  187. if ($value === '') {
  188. $emptyKey = $key;
  189. $value = 'NULL';
  190. continue;
  191. }
  192. $value = $quot . $this->dbi->escapeString(trim($value))
  193. . $quot;
  194. }
  195. if ($func_type === 'BETWEEN' || $func_type === 'NOT BETWEEN') {
  196. $where = $backquoted_name . ' ' . $func_type . ' '
  197. . ($values[0] ?? '')
  198. . ' AND ' . ($values[1] ?? '');
  199. } else { //[NOT] IN
  200. if ($emptyKey !== false) {
  201. unset($values[$emptyKey]);
  202. }
  203. $wheres = [];
  204. if (! empty($values)) {
  205. $wheres[] = $backquoted_name . ' ' . $func_type
  206. . ' (' . implode(',', $values) . ')';
  207. }
  208. if ($emptyKey !== false) {
  209. $wheres[] = $backquoted_name . ' IS NULL';
  210. }
  211. $where = implode(' OR ', $wheres);
  212. if (1 < count($wheres)) {
  213. $where = '(' . $where . ')';
  214. }
  215. }
  216. }
  217. return $where;
  218. }
  219. /**
  220. * Return the where clause for a geometrical column.
  221. *
  222. * @param mixed $criteriaValues Search criteria input
  223. * @param string $names Name of the column on which search is submitted
  224. * @param string $func_type Search function/operator
  225. * @param string $types Type of the field
  226. * @param string|null $geom_func Whether geometry functions should be applied
  227. *
  228. * @return string part of where clause.
  229. */
  230. private function getGeomWhereClause(
  231. $criteriaValues,
  232. $names,
  233. $func_type,
  234. $types,
  235. $geom_func = null
  236. ): string {
  237. $geom_unary_functions = [
  238. 'IsEmpty' => 1,
  239. 'IsSimple' => 1,
  240. 'IsRing' => 1,
  241. 'IsClosed' => 1,
  242. ];
  243. $where = '';
  244. // Get details about the geometry functions
  245. $geom_funcs = Gis::getFunctions($types, true, false);
  246. // If the function takes multiple parameters
  247. if (str_contains($func_type, 'IS NULL') || str_contains($func_type, 'IS NOT NULL')) {
  248. return Util::backquote($names) . ' ' . $func_type;
  249. }
  250. if ($geom_funcs[$geom_func]['params'] > 1) {
  251. // create gis data from the criteria input
  252. $gis_data = Gis::createData($criteriaValues, $this->dbi->getVersion());
  253. return $geom_func . '(' . Util::backquote($names)
  254. . ', ' . $gis_data . ')';
  255. }
  256. // New output type is the output type of the function being applied
  257. $type = $geom_funcs[$geom_func]['type'];
  258. $geom_function_applied = $geom_func
  259. . '(' . Util::backquote($names) . ')';
  260. // If the where clause is something like 'IsEmpty(`spatial_col_name`)'
  261. if (isset($geom_unary_functions[$geom_func]) && trim($criteriaValues) == '') {
  262. $where = $geom_function_applied;
  263. } elseif (in_array($type, Gis::getDataTypes()) && ! empty($criteriaValues)) {
  264. // create gis data from the criteria input
  265. $gis_data = Gis::createData($criteriaValues, $this->dbi->getVersion());
  266. $where = $geom_function_applied . ' ' . $func_type . ' ' . $gis_data;
  267. } elseif (strlen($criteriaValues) > 0) {
  268. $where = $geom_function_applied . ' '
  269. . $func_type . " '" . $criteriaValues . "'";
  270. }
  271. return $where;
  272. }
  273. /**
  274. * Return the where clause in case column's type is ENUM.
  275. *
  276. * @param mixed $criteriaValues Search criteria input
  277. * @param string $func_type Search function/operator
  278. *
  279. * @return string part of where clause.
  280. */
  281. private function getEnumWhereClause($criteriaValues, $func_type): string
  282. {
  283. if (! is_array($criteriaValues)) {
  284. $criteriaValues = explode(',', $criteriaValues);
  285. }
  286. $enum_selected_count = count($criteriaValues);
  287. if ($func_type === '=' && $enum_selected_count > 1) {
  288. $func_type = 'IN';
  289. $parens_open = '(';
  290. $parens_close = ')';
  291. } elseif ($func_type === '!=' && $enum_selected_count > 1) {
  292. $func_type = 'NOT IN';
  293. $parens_open = '(';
  294. $parens_close = ')';
  295. } else {
  296. $parens_open = '';
  297. $parens_close = '';
  298. }
  299. $enum_where = '\''
  300. . $this->dbi->escapeString($criteriaValues[0]) . '\'';
  301. for ($e = 1; $e < $enum_selected_count; $e++) {
  302. $enum_where .= ', \''
  303. . $this->dbi->escapeString($criteriaValues[$e]) . '\'';
  304. }
  305. return ' ' . $func_type . ' ' . $parens_open
  306. . $enum_where . $parens_close;
  307. }
  308. }