Internal Server Error when importing juiceshop challenges due to lack of SQLite migration support

[jsiegmund]

Environment:

• CTFd Version/Commit: 3.4.0 / d03692d
• Operating System: Unix 4.15.0.135
• Web Browser and Version: Edge 93.0.961.47

What happened?
I'm trying to import the zip file that I exported from my configured juiceshop instance. This didn't work, I'm getting a database related error which states:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: pages.format

What did you expect to happen?
I expected the import to work.

How to reproduce your issue
I used the latest versions of both juiceshop as well as CTFd. I've exported the juiceshop challenges using the juice-shop-ctf command, which produces the zip file. I then created a new instance of CTFd and imported the zip file, which triggers the error for me. I didn't do any customization to either.

Any associated stack traces or error logs

2021-09-17T20:19:59.275871494Z ERROR [CTFd] Exception on /setup [GET]
2021-09-17T20:19:59.275909194Z Traceback (most recent call last):
2021-09-17T20:19:59.275916794Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1284, in _execute_context
2021-09-17T20:19:59.275921294Z     cursor, statement, parameters, context
2021-09-17T20:19:59.275925094Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
2021-09-17T20:19:59.275928894Z     cursor.execute(statement, parameters)
2021-09-17T20:19:59.275932494Z sqlite3.OperationalError: no such column: pages.format
2021-09-17T20:19:59.275936194Z
2021-09-17T20:19:59.275939494Z The above exception was the direct cause of the following exception:
2021-09-17T20:19:59.275942994Z
2021-09-17T20:19:59.275946294Z Traceback (most recent call last):
2021-09-17T20:19:59.275949794Z   File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2447, in wsgi_app
2021-09-17T20:19:59.275953394Z     response = self.full_dispatch_request()
2021-09-17T20:19:59.275956794Z   File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1952, in full_dispatch_request
2021-09-17T20:19:59.275960394Z     rv = self.handle_user_exception(e)
2021-09-17T20:19:59.275963894Z   File "/usr/local/lib/python3.7/site-packages/flask_restx/api.py", line 639, in error_router
2021-09-17T20:19:59.275967494Z     return original_handler(e)
2021-09-17T20:19:59.275970894Z   File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1821, in handle_user_exception
2021-09-17T20:19:59.275974794Z     reraise(exc_type, exc_value, tb)
2021-09-17T20:19:59.275978294Z   File "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
2021-09-17T20:19:59.275981994Z     raise value
2021-09-17T20:19:59.275985394Z   File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1950, in full_dispatch_request
2021-09-17T20:19:59.275988994Z     rv = self.dispatch_request()
2021-09-17T20:19:59.275992394Z   File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1936, in dispatch_request
2021-09-17T20:19:59.275996094Z     return self.view_functions[rule.endpoint](**req.view_args)
2021-09-17T20:19:59.276009294Z   File "/opt/CTFd/CTFd/views.py", line 261, in setup
2021-09-17T20:19:59.276013394Z     return render_template("setup.html", state=serialize(generate_nonce()))
2021-09-17T20:19:59.276016994Z   File "/usr/local/lib/python3.7/site-packages/flask/templating.py", line 140, in render_template
2021-09-17T20:19:59.276020694Z     ctx.app,
2021-09-17T20:19:59.276024094Z   File "/usr/local/lib/python3.7/site-packages/flask/templating.py", lin
e 120, in _render
2021-09-17T20:19:59.276027694Z     rv = template.render(context)
2021-09-17T20:19:59.276031094Z   File "/usr/local/lib/python3.7/site-packages/jinja2/environment.py", line 1090, in render
2021-09-17T20:19:59.276034794Z     self.environment.handle_exception()
2021-09-17T20:19:59.276038294Z   File "/usr/local/lib/python3.7/site-packages/jinja2/environment.py", line 832, in handle_exception
2021-09-17T20:19:59.276042994Z     reraise(*rewrite_traceback_stack(source=source))
2021-09-17T20:19:59.276046594Z   File "/usr/local/lib/python3.7/site-packages/jinja2/_compat.py", line 28, in reraise
2021-09-17T20:19:59.276050194Z     raise value.with_traceback(tb)
2021-09-17T20:19:59.276053594Z   File "/opt/CTFd/CTFd/themes/core/templates/setup.html", line 1, in top-level template code
2021-09-17T20:19:59.276057194Z     {% extends "base.html" %}
2021-09-17T20:19:59.276060794Z   File "/opt/CTFd/CTFd/themes/core/templates/base.html", line 29, in top-level template code
2021-09-17T20:19:59.276064494Z     {% include "components/navbar.html" %}
2021-09-17T20:19:59.276067994Z   File "/opt/CTFd/CTFd/themes/core/templates/components/navbar.html", line 16, in top-level template code
2021-09-17T20:19:59.276071594Z     {% for page in Plugins.user_menu_pages %}
2021-09-17T20:19:59.276075094Z   File "/usr/local/lib/python3.7/site-packages/jinja2/sandbox.py", line 407, in getattr
2021-09-17T20:19:59.276078694Z     value = getattr(obj, attribute)
2021-09-17T20:19:59.276081994Z   File "/opt/CTFd/CTFd/constants/plugins.py", line 47, in user_menu_pages
2021-09-17T20:19:59.276097494Z     return get_user_page_menu_bar()
2021-09-17T20:19:59.276101094Z   File "/opt/CTFd/CTFd/plugins/__init__.py", line 149, in get_user_page_menu_bar
2021-09-17T20:19:59.276104794Z     for p in get_pages() + app.plugin_menu_bar:
2021-09-17T20:19:59.276108294Z   File "/usr/local/lib/python3.7/site-packages/flask_caching/__init__.py", line 834, in decorated_function
2021-09-17T20:19:59.276112194Z     rv = f(*args, **kwargs)
2021-09-17T20:19:59.276115594Z   File "/opt/CTFd/CTFd/utils/config/pages.py", line 55, in get_pages
2021-09-17T20:19:59.276119294Z     Pages.route != "index", Pages.draft.isnot(True), Pages.hidden.isnot(True)
2021-09-17T20:19:59.276122994Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3319, in all
2021-09-17T20:19:59.276126694Z     return list(self)
2021-09-17T20:19:59.284499706Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3481, in __iter__
2021-09-17T20:19:59.284511506Z     return self._execute_and_instances(context)
2021-09-17T20:19:59.284515806Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3506, in _execute_and_instances
2021-09-17T20:19:59.284519706Z     result = conn.execute(querycontext.statement, self._params)
2021-09-17T20:19:59.284523406Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1020, in execute
2021-09-17T20:19:59.284527306Z     return meth(self, multiparams, params)
2021-09-17T20:19:59.284530906Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
2021-09-17T20:19:59.284534706Z     return connection._execute_clauseelement(self, multiparams, params)
2021-09-17T20:19:59.284540106Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_clauseelement
2021-09-17T20:19:59.2845
44106Z     distilled_params,
2021-09-17T20:19:59.284556906Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1324, in _execute_context
2021-09-17T20:19:59.284562006Z     e, statement, parameters, cursor, context
2021-09-17T20:19:59.284565706Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1518, in _handle_dbapi_exception
2021-09-17T20:19:59.284569406Z     sqlalchemy_exception, with_traceback=exc_info[2], from_=e
2021-09-17T20:19:59.284573106Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
2021-09-17T20:19:59.284576906Z     raise exception
2021-09-17T20:19:59.284580406Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1284, in _execute_context
2021-09-17T20:19:59.284584206Z     cursor, statement, parameters, context
2021-09-17T20:19:59.284587806Z   File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
2021-09-17T20:19:59.284591606Z     cursor.execute(statement, parameters)
2021-09-17T20:19:59.284595106Z sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: pages.format

I am not 100% sure what's to blame here: the juiceshop export file or the import routine. But if the export file is wrong somehow I would expect CTFd to complain about that.

[bkimminich]

https://github.com/juice-shop/juice-shop-ctf is compatible with CTFd 2.x or newer officially. I'm pretty sure up until recently CTFd 3.x also worked, or I would expect a few people to have complained by now. Either 3.4 made an incompatible data model change or some version before that is not very old either. Would be easiest to get this info from the CTFd developers, then I could at least update the Juice shop CTF tool docs to state the max. version that is supported. Then we can open a ticket on our end to create a new importer for the changed data model, in parallel to the existing 2.x-3.? one.

[bkimminich]

I have an old Docker image 3.2.1 lying around and imported an older backup file. The challenges were successfully imported, but the admin panel throws a 500 error after the import. So, this might be an older issue. As CTFd is too complex for me to dive into, I'd have to rely on assistance by its developers to check our current export data format for incompatibility with their latest 3.x model. Then I'm happy to add a second exporter for that format.

This is the main file for the CTFd export: https://github.com/juice-shop/juice-shop-ctf/blob/master/lib/generators/ctfd.js - Please point me to where changes are necessary to have a valid 3.x data package.

[bkimminich]

OWASP_Juice_Shop.2021-09-17.CTFd.zip

[ColdHeat]

The issue here is that you're using SQLite instead of MySQL/MariaDB. The docs should be updated to reflect this expectation but the recommended server is MySQL/MariaDB https://docs.ctfd.io/docs/deployment/#database-server.

SQLite doesn't really do migrations very well (last I checked) so it's hard to import the data from Juice Shop at it's older migration version and then upgrade. This should work in MySQL but when I have time I will investigate how this either be made to work in SQLite or suggest changes in Juice Shop CLI.

[jsiegmund]

Thanks. I'll need to check whether this works when having a MySQL back-end instead. That might take me some time as I have to do this as an in-between lots of other work.

With help of @bkimminich I verified that version 2.5.0-dev does correctly import the zip file, so the most imminent problem for me has been handled. We can use v2.5 in case I can't get it working using MySQL in time. But I'll give it a go nonetheless.

[jsiegmund]

I ended up using 2.5 with MySQL back-end for now. After our event I'll see if I can up the version to latest and try it again with the back-end in place. But for me the issue was mitigated by downgrading CTFd.

[ColdHeat]

I dug into this and found that migrations dont work well b/c SQLite doesn't support ALTER TABLE very well: https://blog.miguelgrinberg.com/post/fixing-alter-table-errors-with-flask-migrate-and-sqlite. SQLite also doesn't support ALTER COLUMN. Thus to migrate you need to create a new table and migrate the previous data.

This can be done with alembic's batch mode: https://alembic.sqlalchemy.org/en/latest/batch.html#batch-mode-with-autogenerate.

However while I think this is technically possible I don't really think that it's worth the difficulty. The above alembic page outlines a lot of edge cases for this.

It might be worth it if this could somehow help the import process in other database types (the alembic page talks a bit about this) but I'd rather view SQLite as primarily a development and testing backend.

I'll take a PR for this if someone wants to do the large amt of work & testing needed for this. If this somehow ends up affecting a lot of people perhaps I will take it on.

[elonmallin]

I might be wrong but I think SQLite is quite fitting for small to medium sized CTFs. It would also make CTFd extremely easy to deploy since all you need is the docker image and import/export to setup/template/share/recover CTFs.

Ex:

• Spin it up locally and setup a starting point for your company CTFs and export.
• Spin it up and import the starting point and prepare a CTF and export.
• Share the exports with anyone and they can contribute by spin up, import, change, export and share again.
• Or deploy and collaborate then export.
• When you're ready to host, deploy the image and import.
• Backup during CTF and archive after.

It's already quite easy I guess but setting up a DB is a bit of a hassle depending on infrastructure.

I've tried adding render_as_batch in env.py and some more migrate boilerplate I've found in different places but I'm kind of fumbling in the dark with this.

If someone has some knowledge about this stuff or hints would be nice :)

I kinda like the idea of the above "workflow" but tbh I've never hosted myself and first time looking at this so not sure it's a valid point for smaller things?

[chrish]

I hosted a smallish CTF a few years ago using SQLite with CTFd, and now I need to restore it to extract all the challenges for a new CTF we're hosting. Does this issue mean that I can't restore at all, or would it work with a restore if the CTFd instance I'm restoring to uses MySQL?

I can extract the files and all the text from the export itself, but would be nicer/easier to just restore the CTF.

[jinrowarrior]

I have a fresh installation of CTFD on Docker and when I import my extraction from the day before I get this error. However, it doesn't seem to me that I have a version problem?

[bkimminich]

I have a fresh installation of CTFD on Docker and when I import my extraction from the day before I get this error. However, it doesn't seem to me that I have a version problem?

Did you try it with docker compose? That should be spinning up a dedicated DB container instead of using internal SQLite.

[jinrowarrior]

I have a fresh installation of CTFD on Docker and when I import my extraction from the day before I get this error. However, it doesn't seem to me that I have a version problem?

Did you try it with docker compose? That should be spinning up a dedicated DB container instead of using internal SQLite.

Work well THX