Modify

Opened 4 years ago

Closed 15 months ago

Last modified 15 months ago

#7496 closed defect (fixed)

SQL error on Trac 0.12

Reported by: dwc Owned by: rjollos
Priority: normal Component: TicketMoverPlugin
Severity: normal Keywords:
Cc: jun66j5, hasienda Trac Release: 0.12

Description (last modified by hasienda)

On Trac 0.12, an error results when moving a ticket:

ProgrammingError: operator does not exist: text = integer LINE 1: SELECT * FROM attachment WHERE id=17 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

This occurs on PostgreSQL databases, at least, due to its use of strict type checking.

A simple fix is to quote the value in the SQL statement. This works on PostgreSQL and SQLite databases.

  • 0.11/ticketmoverplugin/ticketmover.py

     
    188188 
    189189        # copy the changelog and attachment DBs 
    190190        for table, _id in tables.items(): 
    191             for row in get_all_dict(self.env, "SELECT * FROM %s WHERE %s=%s" % (table, _id, ticket_id)): 
     191            for row in get_all_dict(self.env, "SELECT * FROM %s WHERE %s = '%s'" % (table, _id, ticket_id)): 
    192192                row[_id] = new_ticket.id 
    193193                insert_row_from_dict(env, table, row) 
    194194 

Attachments (0)

Change History (7)

comment:1 Changed 15 months ago by rjollos

#10720 closed as a duplicate.

comment:3 Changed 15 months ago by rjollos

  • Cc jun66j5 added
  • Owner changed from k0s to rjollos
  • Status changed from new to assigned

Thanks, I'll take care of this if no one beats me to it.

comment:4 Changed 15 months ago by rjollos

  • Cc hasienda added

I have a slightly different patch that seems to fix the reported issue, and might also fix another issue.

  • I think that we should not be using string formatting for ticket_id since the value comes from the request, and therefore doing so opens up the possibility for SQL injection. Replace,
    get_all_dict(self.env, "SELECT * FROM %s WHERE %s=%s" % (table, _id, ticket_id))
    
    with,
    get_all_dict(self.env, "SELECT * FROM %s WHERE %s=%%s" % (table, _id), str(ticket_id))
    
  • In the aforementioned query, (table, _id) takes on the values ('attachment', 'id'), which is a TEXT field, and ('ticket_change', 'ticket'), which is an INTEGER field (t:TracDev/DatabaseSchema#DatabaseSchema0.11). Given that the change shown above fixes this issue but results in a cast of ticket_id to a string for both fields, I have to assume that PostgreSQL allows an implicit cast of a string to an integer, but not an integer to a string.

comment:5 follow-up: Changed 15 months ago by rjollos

  • Resolution set to fixed
  • Status changed from assigned to closed

(In [12956])

Fixes #7496: Perform an explict cast of ticket_id to a string to avoid a programming error on PostgreSQL, where strict type checking is done. Prevent possibility of SQL injection since ticket_id comes from the request.

comment:6 in reply to: ↑ 5 Changed 15 months ago by hasienda

Replying to rjollos:

Prevent possibility of SQL injection since ticket_id comes from the request.

+1, better indeed. Definitely the way to go.

comment:7 in reply to: ↑ 2 Changed 15 months ago by hasienda

  • Description modified (diff)

Replying to dwc:

In case it's helpful, here is a more permanent link to the referenced patch:

Thanks for taking care. But the most "permanent" way to provide the patch, would have been to attach a patch file to this ticket, or just inline the patch into the description, so it is really self-contained and not relying on external resources. I've done so for you now.

Add Comment

Modify Ticket

Action
as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from rjollos. Next status will be 'closed'.
The resolution will be deleted. Next status will be 'reopened'.
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.