xref: /web-bugs/include/query.php (revision 2e6915b6) 
1<?php
2
3use App\Repository\PackageRepository;
4
5$errors = [];
6$warnings = [];
7$order_options = [
8    ''                => 'relevance',
9    'id'            => 'ID',
10    'ts1'            => 'date',
11    'ts2'            => 'last modified',
12    'package_name'    => 'package',
13    'bug_type'        => 'bug_type',
14    'status'        => 'status',
15    'php_version'    => 'php_version',
16    'php_os'        => 'os',
17    'sdesc'            => 'summary',
18    'assign'        => 'assignment',
19    'avg_score'        => 'avg. vote score',
20    'votes_count'    => 'number of votes',
21    'RAND()'    => 'random',
22];
23
24// Fetch pseudo packages
25$packageRepository = $container->get(PackageRepository::class);
26$pseudo_pkgs = $packageRepository->findAll();
27
28// Setup input variables..
29$boolean_search = isset($_GET['boolean']) ? (int) $_GET['boolean'] : 0;
30$status = !empty($_GET['status']) ? (string)$_GET['status'] : 'Open';
31$search_for = !empty($_GET['search_for']) ? trim((string)$_GET['search_for']) : '';
32$bug_type = (!empty($_GET['bug_type']) && $_GET['bug_type'] != 'All') ? (string)$_GET['bug_type'] : '';
33$bug_age = (int) (isset($_GET['bug_age']) ? $_GET['bug_age'] : 0);
34$bug_updated = (int) (isset($_GET['bug_updated']) ? $_GET['bug_updated'] : 0);
35$php_os = (!empty($_GET['php_os']) && is_string($_GET['php_os'])) ? $_GET['php_os'] : '';
36$php_os_not = !empty($_GET['php_os_not']) ? 'not' : '';
37$phpver = (!empty($_GET['phpver']) && is_string($_GET['phpver'])) ? $_GET['phpver'] : '';
38$cve_id = (!empty($_GET['cve_id']) && is_string($_GET['cve_id'])) ? $_GET['cve_id'] : '';
39$cve_id_not = !empty($_GET['cve_id_not']) ? 'not' : '';
40$patch = (!empty($_GET['patch']) && is_string($_GET['patch'])) ? $_GET['patch'] : '';
41$pull = (!empty($_GET['pull']) && is_string($_GET['pull'])) ? $_GET['pull'] : '';
42$private = (!empty($_GET['private']) && is_string($_GET['private'])) ? $_GET['private'] : '';
43$begin = (int) ((!empty($_GET['begin']) && $_GET['begin'] > 0) ? $_GET['begin'] : 0);
44$limit = (defined('MAX_BUGS_RETURN')) ? MAX_BUGS_RETURN : 30;
45$project = (!empty($_GET['project']) && $_GET['project'] != 'All') ? $_GET['project'] : '';
46if (!empty($_GET['limit'])) {
47    $limit = ($_GET['limit'] == 'All') ? 'All' : (($_GET['limit'] > 0) ? (int) $_GET['limit'] : $limit);
48}
49$direction = (!empty($_GET['direction']) && $_GET['direction'] != 'DESC') ? 'ASC' : 'DESC';
50$order_by = (!empty($_GET['order_by']) && array_key_exists($_GET['order_by'], $order_options)) ? $_GET['order_by'] : '';
51$reorder_by = (!empty($_GET['reorder_by']) && array_key_exists($_GET['reorder_by'], $order_options)) ? $_GET['reorder_by'] : '';
52$assign = (!empty($_GET['assign']) && is_string($_GET['assign'])) ? $_GET['assign'] : '';
53$author_email = !empty($_GET['author_email']) ? spam_protect($_GET['author_email'], 'reverse') : '';
54$package_name = (isset($_GET['package_name']) && is_array($_GET['package_name'])) ? $_GET['package_name'] : [];
55$package_nname = (isset($_GET['package_nname']) && is_array($_GET['package_nname'])) ? $_GET['package_nname'] : [];
56$commented_by = !empty($_GET['commented_by']) ? spam_protect($_GET['commented_by'], 'reverse') : '';
57
58if (isset($_GET['cmd']) && $_GET['cmd'] == 'display')
59{
60    $query = '
61        SELECT SQL_CALC_FOUND_ROWS
62        bugdb.*,
63        TO_DAYS(NOW())-TO_DAYS(bugdb.ts2) AS unchanged,
64        UNIX_TIMESTAMP(ts1) AS submitted,
65        UNIX_TIMESTAMP(ts2) AS modified
66        FROM bugdb
67    ';
68
69    if (in_array($order_by, ['votes_count', 'avg_score'])) {
70        $query .= 'LEFT JOIN bugdb_votes v ON bugdb.id = v.bug';
71    }
72
73    if ($commented_by != '') {
74        $query .= ' LEFT JOIN bugdb_comments c ON bugdb.id = c.bug';
75    }
76
77    $where_clause = ' WHERE 1 = 1 ';
78
79    if (isset($user_flags) && ($user_flags & (BUGS_SECURITY_DEV | BUGS_TRUSTED_DEV))) {
80        if ($private != '') {
81            $where_clause .= ' AND bugdb.private = "Y" ';
82        }
83    } else {
84        /* Non trusted developer should see the Security related bug report just when it is public */
85        $where_clause .= ' AND (bugdb.bug_type <> "Security" OR private = "N") ';
86    }
87
88    if (!empty($package_name)) {
89        $where_clause .= ' AND bugdb.package_name';
90        if (count($package_name) > 1) {
91            $items = array_map([$dbh, 'quote'], $package_name);
92            $where_clause .= " IN (" . join(", ", $items) . ")";
93        } else {
94            $where_clause .= ' = ' . $dbh->quote($package_name[0]);
95        }
96    }
97
98    if (!empty($package_nname)) {
99        $where_clause .= ' AND bugdb.package_name';
100        if (count($package_nname) > 1) {
101            $items = array_map([$dbh, 'quote'], $package_nname);
102            $where_clause .= " NOT IN (" . join(", ", $items) . ")";
103        } else {
104            $where_clause .= ' <> ' . $dbh->quote($package_nname[0]);
105        }
106    }
107
108    // Ensure status is valid and tweak search clause to treat assigned, analyzed, critical and verified bugs as open
109    switch ($status) {
110        case 'All':
111            break;
112        case 'Closed':
113        case 'Re-Opened':
114        case 'Duplicate':
115        case 'Critical':
116        case 'Assigned':
117        case 'Analyzed':
118        case 'Verified':
119        case 'Suspended':
120        case 'Wont fix':
121        case 'No Feedback':
122        case 'Feedback':
123        case 'Not a bug':
124            $where_clause .= "    AND bugdb.status='$status'";
125            break;
126        case 'Old Feedback':
127            $where_clause .= "    AND bugdb.status='Feedback'
128                                AND TO_DAYS(NOW())-TO_DAYS(bugdb.ts2) > 60";
129            break;
130        case 'Fresh':
131            $where_clause .= "    AND bugdb.status NOT IN ('Closed', 'Duplicate', 'Not a bug')
132                                AND TO_DAYS(NOW())-TO_DAYS(bugdb.ts2) < 30";
133            break;
134        case 'Stale':
135            $where_clause .= "    AND bugdb.status NOT IN ('Closed', 'Duplicate', 'Not a bug')
136                                AND TO_DAYS(NOW())-TO_DAYS(bugdb.ts2) > 30";
137            break;
138        case 'Not Assigned':
139            $where_clause .= " AND bugdb.status NOT IN ('Closed', 'Duplicate', 'Not a bug', 'Assigned', 'Wont Fix', 'Suspended')";
140            break;
141        case 'OpenFeedback':
142            $where_clause .= " AND bugdb.status IN ('Open', 'Re-Opened', 'Assigned','Analyzed', 'Critical', 'Verified', 'Feedback')";
143            break;
144        default:
145        case 'Open':
146            $where_clause .= " AND bugdb.status IN ('Open', 'Re-Opened', 'Assigned', 'Analyzed', 'Critical', 'Verified')";
147    }
148
149    if ($search_for != '') {
150        list($sql_search, $ignored) = format_search_string($search_for, $boolean_search);
151        $where_clause .= $sql_search;
152        if (count($ignored) > 0 ) {
153            $warnings[] = 'The following words were ignored: ' . implode(', ', array_unique($ignored));
154        }
155    }
156
157    if ($bug_type != '') {
158        if ($bug_type == 'Bugs') {
159            $where_clause .= ' AND (bugdb.bug_type = "Bug" OR bugdb.bug_type="Documentation Problem")';
160        } else {
161            $where_clause .= ' AND bugdb.bug_type = ' . $dbh->quote($bug_type);
162        }
163    }
164
165    if ($bug_age > 0) {
166        $where_clause .= " AND bugdb.ts1 >= DATE_SUB(NOW(), INTERVAL $bug_age DAY)";
167    }
168
169    if ($bug_updated > 0) {
170        $where_clause .= " AND bugdb.ts2 >= DATE_SUB(NOW(), INTERVAL $bug_updated DAY)";
171    }
172
173    if ($php_os != '') {
174        $where_clause .= " AND bugdb.php_os {$php_os_not} LIKE " . $dbh->quote('%'.$php_os.'%');
175    }
176
177    if ($phpver != '') {
178        $where_clause .= " AND bugdb.php_version LIKE " . $dbh->quote($phpver.'%');
179    }
180
181    if ($project != '') {
182        $where_clause .= " AND EXISTS (SELECT 1 FROM bugdb_pseudo_packages b WHERE b.name = bugdb.package_name AND  b.project = ". $dbh->quote($project) ." LIMIT 1)";
183    }
184
185    if ($cve_id != '') {
186        $where_clause .= " AND bugdb.cve_id {$cve_id_not} LIKE " . $dbh->quote($cve_id.'%');
187    }
188
189    /* A search for patch&pull should be (patch or pull) */
190    if ($patch != '' || $pull != '') {
191        $where_clause .= " AND (1=2";
192    }
193    if ($patch != '') {
194        $where_clause .= " OR EXISTS (SELECT 1 FROM bugdb_patchtracker WHERE bugdb_id = bugdb.id LIMIT 1)";
195    }
196    if ($pull != '') {
197        $where_clause .= " OR EXISTS (SELECT 1 FROM bugdb_pulls WHERE bugdb_id = bugdb.id LIMIT 1)";
198    }
199    if ($patch != '' || $pull != '') {
200        $where_clause .= ")";
201    }
202    if ($assign != '') {
203        $where_clause .= ' AND bugdb.assign = ' . $dbh->quote($assign);
204    }
205
206    if ($author_email != '') {
207        $where_clause .= ' AND bugdb.email = ' . $dbh->quote($author_email);
208    }
209    if ($commented_by != '') {
210        $where_clause .= ' AND c.email = ' . $dbh->quote($commented_by);
211    }
212
213    $where_clause .= ' AND (1=1';
214
215    if ($pseudo = array_intersect(array_keys($pseudo_pkgs), $package_name)) {
216        $where_clause .= " OR bugdb.package_name";
217        if (count($pseudo) > 1) {
218            $pseudo = array_map([$dbh, 'quote'], $pseudo);
219            $where_clause .= " IN (" . join(", ", $pseudo) . ")";
220        } else {
221            $where_clause .= " = " . $dbh->quote(reset($pseudo));
222        }
223    } else {
224        $items = array_map([$dbh, 'quote'], array_keys($pseudo_pkgs));
225        $where_clause .= " OR bugdb.package_name IN (" . join(", ", $items) . ")";
226    }
227
228    $query .= "$where_clause )";
229
230    if ($reorder_by != '') {
231        if ($order_by == $reorder_by) {
232            $direction = $direction == 'ASC' ? 'DESC' : 'ASC';
233        } else {
234            $direction = $reorder_by == 'ts2' ? 'DESC' : 'ASC';
235            $order_by = $reorder_by;
236        }
237    }
238
239    $order_by_clauses = [];
240    if (in_array($order_by, ['votes_count', 'avg_score'])) {
241        $query .= ' GROUP BY bugdb.id';
242
243        switch ($order_by) {
244            case 'avg_score':
245                $order_by_clauses = [
246                    "IFNULL(AVG(v.score), 0)+3 $direction",
247                    "COUNT(v.bug) DESC"
248                ];
249                break;
250            case 'votes_count':
251                $order_by_clauses = ["COUNT(v.bug) $direction"];
252                break;
253        }
254    } elseif ($order_by != '') {
255        $order_by_clauses = ["$order_by $direction"];
256    }
257
258    if ($status == 'Feedback') {
259        $order_by_clauses[] = "bugdb.ts2 $direction";
260    }
261
262    if (count($order_by_clauses)) {
263        $query .= ' ORDER BY ' . implode(', ', $order_by_clauses);
264    }
265
266    if ($limit != 'All' && $limit > 0) {
267        $query .= " LIMIT $begin, $limit";
268    }
269
270    if (stristr($query, ';')) {
271        $errors[] = 'BAD HACKER!! No database cracking for you today!';
272    } else {
273        try {
274            $result = $dbh->prepare($query)->execute()->fetchAll();
275            $rows = count($result);
276            $total_rows = $dbh->prepare('SELECT FOUND_ROWS()')->execute()->fetch(\PDO::FETCH_NUM)[0];
277        } catch (Exception $e) {
278            $errors[] = 'Invalid query' /*. $e->getMessage() */;
279        }
280        if (defined('MAX_BUGS_RETURN') && $total_rows > $rows) {
281            $warnings[] = 'The search was too general, only ' . MAX_BUGS_RETURN . ' bugs will be returned';
282        }
283    }
284}
285