174296 - Column definitions for buglist.cgi shouldn't include SQL fragments

Status: RESOLVED WORKSFORME

(Bugzilla :: Query/Bug List, defect)

Description

[Dave Miller [:justdave]]

The security implications of this have been dealt with elsewhere by comparing
the returned results with known values before submitting to SQL, however, some
of these column definitions use SQL that's probably MySQL-specific as
expressions, and to ease in the database independence efforts, we should
probably remove the "expressions" from the column lists and find some way of
defining them elsewhere.  This will also have the desired side-effect of
removing SQL fragments from cookies and URLs.

Comment 1

[Bradley Baetz (:bbaetz)]

Note that we have to fix this for custfields to be searchable, anyway, really.

I've always thought that the fielddefs table is an evil, ugly, ugly, hack,
personally.

Comment 2

[Tomas Kopal]

There is an attempt at object-oriented Search.pm at bug 91037, comment #259
(https://bugzilla.mozilla.org/show_bug.cgi?id=91037#c259), Query.pm. It can be
used as a good starting point for fixing this.

Comment 3

[Frédéric Buclin]

The last fragment has been removed in bug 302599 in Bugzilla 2.20.1.

Comment 4

[Max Kanat-Alexander]

No, this isn't fixed at all:

DefineColumn("actual_time"       , "(SUM(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id)) AS actual_time", "Actual Hours");
DefineColumn("percentage_complete",
    "(CASE WHEN (SUM(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id)) " .
    "            + bugs.remaining_time = 0.0 " .
    "THEN 0.0 " .
    "ELSE 100*((SUM(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id)) " .
    "     /((SUM(ldtime.work_time)*COUNT(DISTINCT ldtime.bug_when)/COUNT(bugs.bug_id)) + bugs.remaining_time)) " .
    "END) AS percentage_complete"                               , "% Complete");

Comment 5

[Max Kanat-Alexander]

I consider that this was basically fixed by Bugzilla 3.4, when we put the COLUMNS constant into Search.pm. LpSolit is right that the SQL in *fielddefs* went away in 2.20, though, and that's probably all we actually really needed to handle to mark this bug fixed.