Failure when non-numeric Ticket ID appears in Trac report

[tractest@…]

Our Trac reports contain 0 or even non-numeric value in the column which contains Ticket ID. Such rows are either removed from the report or cause an error which is not correct as these values are on rows representing totals etc.

Following fix in sensitivetickets.py works for me:

        if resource and resource.realm == 'ticket' and resource.id is not None:
            bypass = False
            try:
                ticket_id = int(resource.id)
                if ticket_id == 0:
                    sensitive = 0
                else:
                    ticket = Ticket(self.env, ticket_id)
                    sensitive = ticket['sensitive']
                if as_bool(sensitive):
                    bypass = self.bypass_sensitive_view(ticket, username)
            except ResourceNotFound:
                sensitive = 1  # Fail safe to prevent a race condition.
            except ValueError:
                sensitive = 0  # Fail safe to allow non-numeric resource.id.

Of course, we could discuss whether the non-numeric value in Ticket ID column is correct or not. It is not systematic for sure but SQLite allows it w/o problems.

[Ryan J Ollos]

Replying to tractest@…:

Of course, we could discuss whether the non-numeric value in Ticket ID column is correct or not. It is not systematic for sure but SQLite allows it w/o problems.

Non-numeric values for the ticket id are not allowed. I'll be surprised if this doesn't cause problems elsewhere in Trac. Many plugins will correctly assume that the ticket column id will be an integer, because it's described in the database schema: ​trac:TracDev/DatabaseSchema.

You must have edited the database schema since the id column is type int and therefore won't allow non-numeric values:

sqlite> .schema ticket
CREATE TABLE ticket (
    id integer PRIMARY KEY,
    type text,
    time integer,
    changetime integer,
    component text,
    severity text,
    priority text,
    owner text,
    reporter text,
    cc text,
    version text,
    milestone text,
    status text,
    resolution text,
    summary text,
    description text,
    keywords text
);
CREATE INDEX ticket_time_idx ON ticket (time);
CREATE INDEX ticket_status_idx ON ticket (status);

sqlite> UPDATE ticket SET id='a' WHERE id='1';
Error: datatype mismatch

In future, please consider providing proposed changes as a patch to make it easier to review (​trac:TracDev/SubmittingPatches).

[tractest@…]

Non-numeric value in the Ticket column

[tractest@…]

I am not saying the ID column in the Ticket table contains non-numeric values but Trac Reports allow non-numeric values in the ticket column:

[anonymous]

I'll try follow Trac patching instructions next time.

[Ryan J Ollos]

When do you see the error? When rendering a report? Can you share the report SQL?

Where does the #0 link to? Is the #0 intended to be a dummy value or is there actually a ticket 0 that you inserted manually to the database?

[tractest@…]

Yes, the error raised when rendering the report. The Report query has about 250 rows but following SQL sample should also produce the same error:

SELECT 'Date:' AS ticket, 'some text' AS summary
  FROM Ticket
 WHERE ID = (SELECT MAX(ID) FROM Ticket) 
UNION ALL
SELECT ID, summary
  FROM Ticket
 WHERE ID >= (SELECT MAX(ID)-5 FROM Ticket)

Database used is SQLite and its advantage for Trac reports is the possibility to mix strings and numbers in one column as you can see in above query or in the screen shot attached to comment No. 2.

The #0 value is hardcoded on lines containing total figures in our case. It can easily be replaced by NULL but I would propose to handle #0 as non-sensitive otherwise the total disappears from the output.

Other database engines should also allow to produce such report output when we use explicit data type conversions.

[Ryan J Ollos]

The plugin hasn't been compatible with Trac 0.11 since at least r11289. I've copied the 0.11 directory to 0.12 and restored 0.11 from sensitiveticketsplugin/trunk@11288​.

Make sure to install from the 0.12 directory.

[Ryan J Ollos]

In 16890:

sensitivetickets 0.24: Fix failure with non-integer resource id

Restore the 0.11 directory to the revision prior to
r11289, which should be compatible with Trac 0.11.6.

Refs #13297.

[Ryan J Ollos]

In 16891:

TracSensitiveTickets 1.2.2dev: Fix failure with non-integer resource id

Refs #13297.

[Ryan J Ollos]

In 16892:

TracSensitiveTickets 1.2.3dev: Bump version

TracSensitiveTickets 1.2.2 has been published to PyPI.

Refs #13297.