SQL layer (PostgreSQL): Pg#deleteEqual, deleteEqualAnd and deleteLowerThan reject the value 0 (and ""), which the SQLite backend deletes normally

Open Beginner friendly
  • Is something wrong?
#117 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
62/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
postgresql, sqlite, typescript
Domain
databases

Research direction

Start with the truthiness guards !value || value.toString().length === 0 in src/modules/legacy/matrix-identity-server/db/sql/pg.ts (deleteEqual, deleteEqualAnd, deleteLowerThan) and the same expressions in src/modules/legacy/db/sql/pg.ts, then compare with the SQLite delete methods, which have no such guard. Add the regression test from the issue, using a faked pool, so that value 0 binds as $1 and resolves. The open decision is what to do with empty strings, so confirm that choice with the maintainers before changing "" handling.

Written by the indexing model from the issue text.

Description

ai-driven-fuzzing bug claude Minor

Steps to reproduce

The two backends behave differently on the same library call (no HTTP request is needed). The fuzzer found three variants on an empty invitations table:

await db.deleteEqual('invitations', 'id', 0)                      // matrix-identity-server Pg
await db.deleteLowerThan('invitations', 'id', 0)                  // matrix-identity-server Pg
await db.deleteEqualAnd('invitations', { field: 'id', value: 0 }, // TwakeDB Pg (src/modules/legacy/db/sql/pg.ts)
                                       { field: 'id', value: 0 })

SQLite resolves all three. Pg rejects all three before sending any SQL. The same happens with ''.

Fuzzer input (scenario 33-sql-backend-consistency, 5 bytes, hex): 3c 08 00 2b 80

Regression test (bun:test, e.g. src/modules/legacy/matrix-identity-server/db/sql/pg.test.ts; the pool is faked, so no PostgreSQL is needed):

import { describe, expect, it } from 'bun:test'
import Pg from './pg'

const makePg = () => {
  const queries: Array<{ text: string; values: unknown[] }> = []
  const noop = (): void => {}
  const pg = Object.create(Pg.prototype) as Pg<string>
  Object.assign(pg, {
    logger: { debug: noop, info: noop, warn: noop, error: noop },
    db: {
      query: (text: string, values: unknown[], cb: (e: Error | null, r: { rows: unknown[] }) => void) => {
        queries.push({ text, values })
        cb(null, { rows: [] })
      }
    }
  })
  return { pg, queries }
}

describe('Pg delete methods accept the value 0', () => {
  it('deleteEqual', async () => {
    const { pg, queries } = makePg()
    await expect(pg.deleteEqual('invitations', 'id', 0)).resolves.toBeUndefined()
    expect(queries).toEqual([{ text: 'DELETE FROM invitations WHERE id=$1', values: [0] }])
  })
  it('deleteLowerThan', async () => {
    const { pg } = makePg()
    await expect(pg.deleteLowerThan('invitations', 'expiration', 0)).resolves.toBeUndefined()
  })
  it('deleteEqualAnd', async () => {
    const { pg } = makePg()
    await expect(
      pg.deleteEqualAnd('invitations', { field: 'id', value: 0 }, { field: 'sender', value: 0 })
    ).resolves.toBeUndefined()
  })
})

Add the same test for src/modules/legacy/db/sql/pg.ts.

Expected

Both backends treat the same value the same way. 0 is a valid string | number value and is bound as $1. For "", the maintainers should pick one behaviour (reject it as an invalid filter, or run WHERE x='') and apply it to both Pg and SQLite.

Observed

deleteEqual: SQLite resolved but Pg rejected (Bad deleteEqual call, field: id, value: 0)
deleteLowerThan: SQLite resolved but Pg rejected (Bad deleteLowerThan call, field: id, value: 0)
deleteEqualAnd: SQLite resolved but Pg rejected (Bad deleteAnd call, conditions: id=0, id=0)   [TwakeDB copy]

In each case no statement reached the pool (pgStatements: []).

Possible cause

The parameter guards test the values for truthiness:

  • src/modules/legacy/matrix-identity-server/db/sql/pg.ts:889 (deleteEqual), :954 and :958 (deleteEqualAnd), :1022 (deleteLowerThan): !value || value.toString().length === 0
  • src/modules/legacy/db/sql/pg.ts:809, :868/:872 and :928: the same expressions.

!value is true for 0. The SQLite classes (sqlite.ts deleteEqual / deleteEqualAnd / deleteLowerThan) have no such guard. Suggested fix: replace !value with value == null, and move one shared guard into the SQL base class (or a helper used by both backends), so that "" is handled the same way on both.

Introduced by

Not found: the guards and the whole pg.ts files date from 4d406b7 (2026-09-15, "chore: initial import"), a squashed import of the code with no earlier history.

Impact

This is a backend divergence and is not reachable by any client today. The production callers (changePepper, logout, unbind, the address book and invitation services, the token cleanup deleteLowerThan(table, 'expires', epoch())) pass non-empty strings or a current timestamp, never 0. If an empty string reaches one of them (hypothesis: an empty id or mxid), PostgreSQL deployments reject the call and the handler reports an error, while SQLite deployments and the test suite run the DELETE. The Pg files are marked istanbul ignore file and have no unit tests, so this kind of difference only shows up in production. There is no confidentiality or integrity impact: the PostgreSQL side deletes less, not more.

Fuzzing scenario

33-sql-backend-consistency (runs the same operation sequences on the Pg and SQLite query builders and compares results and table contents). It produced 3 crash inputs for this bug (deleteEqual, deleteLowerThan, deleteEqualAnd) in a 900 s run on commit e2e64af072bd45835ff6738234014135b4f3793d. The run's log does not report the execution count at the first crash.

Found by twake-fuzz (coverage-guided fuzzing), scenario 33-sql-backend-consistency, commit e2e64af.

Dominant language
TypeScript
Stars
2
Forks
3
Avg merge
2d 3h
Merged PRs (30d)
19

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 linagora/ToM-Server

All issues in linagora/ToM-Server

Similar issues

More TypeScript issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.