SQLAlchemy host pagination leaves recently updated targets on older pages

Open Beginner friendly
  • Is something wrong?
#1,895 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
70/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
python, sqlite
Domain
backend, databases

Research direction

The bug is in the SQLAlchemy backend's last_host_logs() and search_logs() queries, which group by HostsLog.target but order by HostsLog.id. Read both queries and reproduce with an in-memory SQLite database holding 12 targets plus one newer event for host-0. Done means both functions return host-0 first on page 1 and that pagination still applies after grouping and ordering, with the existing search filters intact. Confirm with a maintainer that the newest-event ordering is intended before starting.

Written by the indexing model from the issue text.

Description

Bug Description

The SQLAlchemy backend's last_host_logs() and search_logs() functions can return targets in an incorrect order when a previously scanned target receives a newer event.

The queries group records by HostsLog.target but order by the non-aggregated HostsLog.id. As a result, a target with the newest scan event can remain on an older page.

Steps to Reproduce

  1. Use Nettacker commit 02275b6b with Python 3.12 and SQLAlchemy 2.0.48.
  2. Create an in-memory SQLite database using the existing HostsLog model.
  3. Insert 12 distinct targets (host-0 through host-11).
  4. Insert an additional, newer event for host-0.
  5. Query pages 1 and 2 using last_host_logs() and search_logs().

Expected Behavior

Targets should be ordered by their most recent matching event.

Page 1 should start with host-0, followed by host-11, host-10, and so on.

Actual Behavior

Both functions return host-0 on page 2, despite it having the newest event.

The observed results were:

Page 1: host-11, host-10, ..., host-2
Page 2: host-1, host-0

Root Cause

The SQLAlchemy queries use the following pattern:

.group_by(HostsLog.target)
.order_by(HostsLog.id.desc())

Ordering grouped records by a non-aggregated ID does not guarantee ordering by the newest record within each target group.

A possible fix is to order grouped targets using:

func.max(HostsLog.id).desc()

The query should apply pagination after grouping and ordering, while preserving existing search filters and response structures.

Environment

  • OS: Windows 11
  • Python: 3.12.14
  • Nettacker: 0.4.2
  • Commit: 02275b6b
  • SQLAlchemy: 2.0.48
  • SQLite: 3.53.1
  • Backend: SQLAlchemy SQLite (use_apsw_for_sqlite=False)

Verification

The behavior was reproduced using synthetic records in an isolated in-memory SQLite database.

  • Both affected SQLAlchemy functions failed the expected-order tests.
  • Equivalent control queries returned the expected ordering.
  • 67 existing focused tests passed.
  • No external network targets were scanned.

The default APSW backend was not demonstrated to have this issue.

Related Work

I checked existing pagination and database discussions, including:

  • #1294 – Pagination-related changes
  • #1076 – APSW migration
  • #1145 – Database writer queue

I did not find an existing report covering this specific grouped-ordering behavior within the issues and PR discussions reviewed.

Contribution

Could a maintainer confirm whether ordering targets by their newest matching event is the intended behavior?

If this is a valid issue and is available, I would be happy to work on a focused fix and add regression tests after approval and assignment.

Thank you!

Dominant language
Python
Stars
5.7k
Forks
1.2k
Avg merge
2d 17h
Merged PRs (30d)
12

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from OWASP/Nettacker

All issues in OWASP/Nettacker

Similar issues

More Python issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.