Recently it was discovered that Open Event Server does not validate attendees’ tickets. Specifically, it was possible to create an arbitrary number of attendees who’d be attending an event on the same ticket! To fix this, a constraint had to be set up across different layers of Open Event Server, which is based on Flask and Postgres. This post will demonstrate how the constraint was added in the server, and these steps should apply in general to any Flask-based server with a relational back-end.
First of all, the immediate idea that comes after investigating such an issue, is to add a UNIQUE constraint to the database. For this specific case, the problem was in ticket_holders table of the Open Event database. There was originally no check imposed on the ticket_id and event_id columns.
As can be seen in the ticket_holders schema (using the \d+ ticket_holders command), there is no mention of uniqueness on either column. The initial guess was that the combination of ticket_id and event_id should be unique throughout the table to avoid multiple holders attending on the same ticket. However,imposing uniqueness on just the ticket_id column would’ve also worked. So, to be on the safer side, I moved ahead by adding uniqueness on both the columns.
To fix this, we need to make changes to the ticket_holder model. So, in the ticket_holder model file, we add a __table_args__ attribute to the TicketHolder class. This attribute represents the various constraints imposed on the ticket_holders table:
class TicketHolder(db.Model): __tablename__ = "ticket_holders" __table_args__ = ( db.UniqueConstraint('ticket_id', 'event_id', name='ticket_event'), ) # this is the constraint we add id = db.Column(db.Integer, primary_key=True) firstname = db.Column(db.String, nullable=False) lastname = db.Column(db.String, nullable=False) … … …
The TicketHolder class has attributes named ticket_id and event_id, so to add a unique constraint over them, we pass their names to the UniqueConstraint constructor. Also, any suitable name can be given to the constraint, I chose ‘ticket_event’ to simply emphasize the relationship. Now that we’ve edited the database model file, we have to perform a database migration.
Before we command the migration, we have to remove the entries that potentially violate the constraint we just imposed. As a temporary fix, I connected to the database and deleted all non-unique rows via plain SQL. For a more consistent fix, I will implement this simple deletion code in the database migration file, if need be. So, once the non-unique rows are gone, we perform the database migration as follows:
$ python manage.py db migrate
And then,
$ python manage.py db upgrade
These commands may be different for different projects, but their purpose is the same – to update the database. The upgrade command generates a migration file which looks as follows:
from alembic import op import sqlalchemy as sa import sqlalchemy_utils # revision identifiers, used by Alembic. revision = '9d21de792967' down_revision = '194a5a2a44ef' def upgrade(): op.create_unique_constraint('ticket_event', 'ticket_holders', ['ticket_id', 'event_id']) def downgrade(): op.drop_constraint('ticket_event', 'ticket_holders', type_='unique')
We can see that the upgrade() function has the command for adding our constraint. Once the database has been upgraded, we can revisit the schema of ticket_holders table (using the \d+ ticket_holders command again). Now we can see that our constraint is added very well in the table schema.
Now, if one tries to create multiple attendees that attend on the same ticket, s/he gets a 500 server error. Here are the related server logs:
2018-06-05 22:04:03.824 IST [46705] ERROR: duplicate key value violates unique constraint "ticket_event" 2018-06-05 22:04:03.824 IST [46705] DETAIL: Key (ticket_id, event_id)=(2, 6) already exists. 2018-06-05 22:04:03.824 IST [46705] STATEMENT: UPDATE ticket_holders SET event_id=6 WHERE ticket_holders.id = 16 127.0.0.1 - - [05/Jun/2018 22:04:03] "POST /v1/attendees HTTP/1.1" 500 - INFO:werkzeug:127.0.0.1 - - [05/Jun/2018 22:04:03] "POST /v1/attendees HTTP/1.1" 500 -
To get a more graceful error, we also need to make changes in the API schema. This will also allow to validate the data before it gets to the database. So, in the attendees.py file, we need to add a check. This check should extract the ticket and event ids from the data posted and see whether there is already an attendee in the database attending that event on the same ticket. If such an attendee is discovered, the check should raise an error and report it back to the API caller. The suitable place for this check is the before_post() method of the AttendeeListPost class. In any Flask app serving a REST API, such a method (perhaps of a different name) should exist in the API file corresponding to a model. Our check looks like the following within the before_post() method:
from flask_rest_jsonapi import ResourceList from app.api.helpers.exceptions import ConflictException from app.models import db from app.models.ticket_holder import TicketHolder … … … class AttendeeListPost(ResourceList): """ List and create Attendees through direct URL """ def before_post(self, args, kwargs, data): """ Before post method to check for required relationship and proper permissions :param args: :param kwargs: :param data: :return: """ require_relationship(['ticket', 'event'], data) … … … if db.session.query(TicketHolder.id).filter_by( ticket_id=int(data['ticket']), event_id=int(data['event']) ).scalar() is not None: raise ConflictException( {'pointer': '/data/attributes/ticket_id'}, "Attendee with this ticket already exists for the same event" )
Once this check is implemented, we’re all good to go. Now, if an attendee is created that maps to a ticket belonging to an already existing attendee, the following error is sent back to the API caller:
{ "errors": [ { "status": 409, "source": { "pointer": "/data/attributes/ticket_id" }, "title": "Conflict", "detail": "Attendee with this ticket already exists for the same event" } ], "jsonapi": { "version": "1.0" } }
This completes our work of enforcing this constraint throughout our Flask server. This leads to a more consistent database and potentially avoids confusion at actual events!
Resources: