Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Data too long for column 'query' #1720

Closed
1 of 3 tasks
ThibaudLopez opened this issue Dec 23, 2019 · 16 comments
Closed
1 of 3 tasks

Data too long for column 'query' #1720

ThibaudLopez opened this issue Dec 23, 2019 · 16 comments
Labels

Comments

@ThibaudLopez
Copy link

ThibaudLopez commented Dec 23, 2019

Bug description
The DefectDojo feature Import Scan Results throws the error "Data too long for column 'query'" for scan results that contain URLs with a query string over 1000 characters long whereas the URL specification allows URLs with at a minimum 8000 characters long [1] [2] [3].

Steps to reproduce
Steps to reproduce the behavior:

  1. Go to DefectDojo > Products > All Products, e.g. http://host:8080/product
  2. Select a Product, e.g. http://host:8080/product/1
  3. Go to Findings > Import Scan Results, e.g. http://host:8080/product/1/import_scan_results
  4. Select a Scan type, e.g. ZAP Scan
  5. Choose a report file that contains a query string over 1000 characters long, e.g. the following URL has a query string that's 1001 characters long: https://example.com/p/?q=0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQ
  6. Click Upload File
  7. It will throw HTTP 500 Internal Server Error, Server Error (500)

Expected behavior
The Import Scan Results should not throw an error. It should correctly import the scan results even for query strings over 1000 characters long.

Deployment method (select with an X)

  • Kubernetes
  • Docker
  • setup.bash / legacy-setup.bash

Environment information

  • Operating System: Ubuntu 19.10
  • DefectDojo Commit Message: 6d56806: Update README.md [2019-11-26 16:42:15 -0600]

Sample scan files (optional)
Here is a sample scan file that will throw the error (and if you remove one character in the URL query string, it will work):
test.xml.txt
<?xml version="1.0"?> <OWASPZAPReport version="2.8.0" generated="Tue, 3 Dec 2019 23:40:10"> <site name="https://example.com" host="example.com" port="443" ssl="true"> <alerts> <alertitem> <pluginid>123</pluginid> <alert>test</alert> <name>test</name> <riskcode>123</riskcode> <confidence>123</confidence> <riskdesc>Informational</riskdesc> <desc>test</desc> <instances> <instance> <uri>https://example.com/p/?q=0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCDEFGHIJKLMNOPQ</uri> <method>GET</method> <param>test</param> <evidence>test</evidence> </instance> </instances> <count>123</count> <solution>test</solution> <reference>test</reference> <cweid>123</cweid> <wascid>123</wascid> <sourceid>123</sourceid> </alertitem> </alerts> </site> </OWASPZAPReport>

Screenshots (optional)
Here are three screenshots showing the Import Scan Results page, the error, and the Traceback:
5 4
5 5 bug 1
5 5 bug 6

Console logs (optional)
Here is the console log:

applying rules
Internal Server Error: /product/1/import_scan_results
Traceback (most recent call last):
  File "/usr/local/lib/python3.5/site-packages/django/db/models/query.py", line 538, in get_or_create
    return self.get(**kwargs), False
  File "/usr/local/lib/python3.5/site-packages/django/db/models/query.py", line 408, in get
    self.model._meta.object_name
dojo.models.Endpoint.DoesNotExist: Endpoint matching query does not exist.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.5/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.5/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.5/site-packages/MySQLdb/cursors.py", line 312, in _query
    db.query(q)
  File "/usr/local/lib/python3.5/site-packages/MySQLdb/connections.py", line 224, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.DataError: (1406, "Data too long for column 'query' at row 1")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.5/site-packages/django/core/handlers/exception.py", line 34, in inner
    response = get_response(request)
  File "/usr/local/lib/python3.5/site-packages/django/core/handlers/base.py", line 115, in _get_response
    response = self.process_exception_by_middleware(e, request)
  File "/usr/local/lib/python3.5/site-packages/django/core/handlers/base.py", line 113, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/usr/local/lib/python3.5/site-packages/django/contrib/auth/decorators.py", line 21, in _wrapped_view
    return view_func(request, *args, **kwargs)
  File "./dojo/product/views.py", line 413, in import_scan_results_prod
    return import_scan_results(request, pid=pid)
  File "/usr/local/lib/python3.5/site-packages/django/contrib/auth/decorators.py", line 21, in _wrapped_view
    return view_func(request, *args, **kwargs)
  File "./dojo/engagement/views.py", line 584, in import_scan_results
    product=t.engagement.product)
  File "/usr/local/lib/python3.5/site-packages/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.5/site-packages/django/db/models/query.py", line 541, in get_or_create
    return self._create_object_from_params(kwargs, params)
  File "/usr/local/lib/python3.5/site-packages/django/db/models/query.py", line 575, in _create_object_from_params
    obj = self.create(**params)
  File "/usr/local/lib/python3.5/site-packages/django/db/models/query.py", line 422, in create
    obj.save(force_insert=True, using=self.db)
  File "/usr/local/lib/python3.5/site-packages/django/db/models/base.py", line 741, in save
    force_update=force_update, update_fields=update_fields)
  File "/usr/local/lib/python3.5/site-packages/django/db/models/base.py", line 779, in save_base
    force_update, using, update_fields,
  File "/usr/local/lib/python3.5/site-packages/django/db/models/base.py", line 870, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/usr/local/lib/python3.5/site-packages/django/db/models/base.py", line 908, in _do_insert
    using=using, raw=raw)
  File "/usr/local/lib/python3.5/site-packages/django/db/models/manager.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/usr/local/lib/python3.5/site-packages/django/db/models/query.py", line 1186, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/usr/local/lib/python3.5/site-packages/django/db/models/sql/compiler.py", line 1335, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/usr/local/lib/python3.5/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.5/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.5/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.5/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/usr/local/lib/python3.5/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/usr/local/lib/python3.5/site-packages/MySQLdb/cursors.py", line 312, in _query
    db.query(q)
  File "/usr/local/lib/python3.5/site-packages/MySQLdb/connections.py", line 224, in query
    _mysql.connection.query(self, query)
django.db.utils.DataError: (1406, "Data too long for column 'query' at row 1")
[pid: 1|app: 0|req: 24/24] 192.168.39.152 () {54 vars in 1300 bytes} [Thu Dec 19 18:25:25 2019] POST /product/1/import_scan_results => generated 27 bytes in 27897 msecs (HTTP/1.1 500) 4 headers in 126 bytes (1 switches on core 0)`

Additional context (optional)
I believe the bug is in django-DefectDojo/dojo/db_migrations/0001_initial.py > model Endpoint > field query max_length=1000
5 5 bug Y_
which creates table dojo_endpoint with column query varchar(1000):
5 5 bug X_

@ThibaudLopez
Copy link
Author

ThibaudLopez commented Dec 23, 2019

I tried changing django-DefectDojo/dojo/db_migrations/0001_initial.py > model Endpoint > fields path and query, to max_length=8000, but docker-compose up threw "Row size too large" even though 8000 seems within the MySQL Row Size Limits of 65,535 bytes:

initializer_1   |   Applying dojo.0001_initial...Traceback (most recent call last): [...]
initializer_1   | MySQLdb._exceptions.OperationalError: (1118, 'Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs')

@ThibaudLopez
Copy link
Author

ThibaudLopez commented Dec 24, 2019

max_length=5000 seems to work, but it turns out there are three files to change:

thibaud@work:~/git/django-DefectDojo$ grep -r -e "query.*max_length=1000"
dojo/db_migrations/0001_initial.py:                ('query', models.CharField(blank=True, help_text=b"The query string, the question mark should be omitted.For example 'group=4&team=8'", max_length=1000, null=True)),
dojo/db_migrations/0006_django2_upgrade.py:            field=models.CharField(blank=True, help_text="The query string, the question mark should be omitted.For example 'group=4&team=8'", max_length=1000, null=True),
dojo/models.py:    query = models.CharField(null=True, blank=True, max_length=1000,

@ThibaudLopez
Copy link
Author

ThibaudLopez commented Dec 24, 2019

I confirm it works by changing the fields path and query to max_length=5000 in these three files:

django-DefectDojo/dojo/db_migrations/0001_initial.py
django-DefectDojo/dojo/db_migrations/0006_django2_upgrade.py
django-DefectDojo/dojo/models.py

The docker-compose build, docker-compose up, and Import Scan Results succeed.

This solution is incomplete. The correct solution would to support URLs with at a minimum 8000 octets as per the URL specification.

@madchap
Copy link
Contributor

madchap commented Dec 24, 2019

Hi @ThibaudLopez

You should not modify previous migration files, yet rather change the models.py file and generate a new migration file which will take care of the details for you.

Also, just looked at your PR, which includes quite some commits that are not part of what you intended to. I would advise the following:

  • Close the PR
  • Rebase your own branch from upstream dev
  • Only modify models.py and generate migration files (makemigrations and migrate). I found a link that could be a good introduction on what that is.

Cheers, and happy holidays.

@ThibaudLopez
Copy link
Author

ThibaudLopez commented Dec 24, 2019 via email

@ThibaudLopez ThibaudLopez mentioned this issue Dec 26, 2019
5 tasks
@ThibaudLopez
Copy link
Author

ThibaudLopez commented Dec 26, 2019

Hi @madchap, do you know why I'm getting this error? I've tried all the apt install, pip install, and manage.py commands that I can think of.

$ python manage.py test
ModuleNotFoundError: No module named 'dojo.settings.settings'

full Traceback:

Original exception was:
Traceback (most recent call last):
  File "manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/thibaud/.local/lib/python3.8/site-packages/django/core/management/__init__.py", line 401, in execute_from_command_line
    utility.execute()
  File "/home/thibaud/.local/lib/python3.8/site-packages/django/core/management/__init__.py", line 395, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/thibaud/.local/lib/python3.8/site-packages/django/core/management/commands/test.py", line 23, in run_from_argv
    super().run_from_argv(argv)
  File "/home/thibaud/.local/lib/python3.8/site-packages/django/core/management/base.py", line 320, in run_from_argv
    parser = self.create_parser(argv[0], argv[1])
  File "/home/thibaud/.local/lib/python3.8/site-packages/django/core/management/base.py", line 294, in create_parser
    self.add_arguments(parser)
  File "/home/thibaud/.local/lib/python3.8/site-packages/django/core/management/commands/test.py", line 44, in add_arguments
    test_runner_class = get_runner(settings, self.test_runner)
  File "/home/thibaud/.local/lib/python3.8/site-packages/django/test/utils.py", line 301, in get_runner
    test_runner_class = test_runner_class or settings.TEST_RUNNER
  File "/home/thibaud/.local/lib/python3.8/site-packages/django/conf/__init__.py", line 76, in __getattr__
    self._setup(name)
  File "/home/thibaud/.local/lib/python3.8/site-packages/django/conf/__init__.py", line 63, in _setup
    self._wrapped = Settings(settings_module)
  File "/home/thibaud/.local/lib/python3.8/site-packages/django/conf/__init__.py", line 142, in __init__
    mod = importlib.import_module(self.SETTINGS_MODULE)
  File "/usr/lib/python3.8/importlib/__init__.py", line 127, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 1014, in _gcd_import
  File "<frozen importlib._bootstrap>", line 991, in _find_and_load
  File "<frozen importlib._bootstrap>", line 973, in _find_and_load_unlocked
ModuleNotFoundError: No module named 'dojo.settings.settings'

@madchap
Copy link
Contributor

madchap commented Dec 27, 2019

You're likely missing the settings.py file in your deployment. You can copy it from settings.dist.py.

@ThibaudLopez
Copy link
Author

ThibaudLopez commented Jan 3, 2020

Hi @madchap,

UPDATE: I'm still working on this. I'm battling errors because I'm new to DefectDojo, I don't yet know how to build a development environment for it, and I couldn't find documentation to help me. So I'm reading the files setup.py, setup.bash, upgrade.bash, requirements.txt, manage.py, and dojo/settings/settings.dist.py. Here's where I'm stuck at:

# Ubuntu 19.10 server
sudo apt install python3-pip
sudo apt install libmysqlclient-dev
git clone https://github.com/DefectDojo/django-DefectDojo.git
cd django-DefectDojo/
pip3 install --user -r requirements.txt
pip3 install --user django-taggit
sudo python3 setup.py install
...
error: Django 2.2.4 is installed but django<2.0,>=1.8 is required by {'django-overextends'}

Then, I'm trying the manage.py but it also throws a bunch of errors:

cp dojo/settings/settings.dist.py dojo/settings/settings.py
python3 manage.py test

If you know of a documentation or if you see something obvious please let me know.

Thanks

@madchap
Copy link
Contributor

madchap commented Jan 3, 2020

Your best bet would be to use docker-compose, and switch to the ptvsd environment. See DOCKER.md in the dev branch for more details including for dev or ptvsd modes (remote debug) stuff.

@ThibaudLopez
Copy link
Author

ThibaudLopez commented Jan 6, 2020

@madchap,

Thanks for the pointer. With the command docker-compose exec uwsgi bash I'm finally in a shell where manage.py works. But when I do makemigrations, it does recognize my changes to the model, but it throws an error about user permissions:

$ python manage.py makemigrations
Migrations for 'dojo':
  dojo/db_migrations/0015_auto_20200106_2200.py
    - Alter field path on endpoint
    - Alter field query on endpoint
    ...
PermissionError: [Errno 13] Permission denied: '/app/dojo/db_migrations/0015_auto_20200106_2200.py'

The files are owned by uid=1000, but the shell is uid=1001, neither of which exist in /etc/passwd. Is this about having to change the value of USER in Dockerfile.django? I did. But now my docker-compose build won't re-build because of yet a new error, this time at Building nginx...[2/5] Resolving packages.... This is frustrating, but I'll persist.

@madchap
Copy link
Contributor

madchap commented Jan 7, 2020

The files are owned by uid=1000, but the shell is uid=1001, neither of which exist in /etc/passwd. Is this about having to change the value of USER in Dockerfile.django? I did. But now my docker-compose build won't re-build because of yet a new error, this time at Building nginx...[2/5] Resolving packages.... This is frustrating, but I'll persist.

What I do is rebuild the docker images with USER 1000 (which is my local linux user), so that it matches within the docker containers. After changing the Dockerfiles, you can use docker-compose build to rebuild them. It should work then.

Update: and reading your full answer.. well, what's the new error as you seem to be passed the uid issue?

@ptrovatelli
Copy link
Contributor

hello,
I just fixed something very similar on ZAP scanner: #1804 . I didn't increase the max length, rather i cut the parsed field to 1000 chars
It's faster than modifying the model and we need a limit anyway. i didn't find critical to keep the whole url when it's that big

@valentijnscholten
Copy link
Member

It's better to truncate to 1000 indeed. 8000 or 5000 for a url is to big. I think the url ends up in endpoints and gets indexed etc.
You could put the full url in the description to be sure it is there for reference.

@ThibaudLopez
Copy link
Author

I confirm that with the fix, I can no longer reproduce the error.

However, I think it's an incorrect fix to truncate the URL to 1000 characters instead of increasing the model to 8000 characters which would have been a correct fix given the URL specification says it should be at least 8000 characters.

@valentijnscholten
Copy link
Member

Yeah maybe, but I don't think many tools support such a long url so let's leave it for now.

@ptrovatelli
Copy link
Contributor

ptrovatelli commented Mar 24, 2020

@ThibaudLopez having very long URL will take up space in the DB, will make imports last longer, will make the gui load slower. For me it's totally fine to keep only the first 1000 char which is already very big, and then go to the source tool if I really need the full URL. Although I totally understand how one would want to avoid data loss and make sure to keep everyting.

If you really need that data, feel free to open a new PR with the model change to 8000 chars. see Madchap comment about this #1720 (comment)
thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Development

No branches or pull requests

4 participants