Files
ai/object/util_sortfield_test.go
zeekayandHanzo Dev fbfd738d8d security: two more sort sites took the column caller input, unwhitelisted
GetDbQuery whitelists sortField, but it is not the only place a sort column is
built. Two callers construct their own ORDER BY and neither ran the check:

  chat.go   getPaginationChatsByMessages — the ?field=messages branch of the
            chat list, which searches message text through a join and sorts on
            "chat." + the caller's column.
  provider.go GetPaginationProviders — the remote half, sorted by hand so it
            matches the local half; the local half goes through GetDbQuery and
            is whitelisted, the remote one beside it was not.

Both are identifiers, so a bound parameter cannot reach them, and nothing
underneath catches them: dbx.QuoteColumnName returns its argument UNQUOTED when
it contains "(", "{{" or "[[", and SnakeString only lowercases and inserts "_"
before capitals, so an all-lowercase payload passes byte for byte. Measured
against the real builder on the real driver:

  ORDER BY (select/**/group_concat(name)/**/from/**/sqlite_master) ASC
  ORDER BY iif((select/**/count(*)/**/from/**/chat)>2,name,owner) ASC

both emitted whole and both executed against the provider database with no
error; "/**/" stands in for the spaces SnakeString strips. The chat prefix is
not a guard either — a comma opens a second ORDER BY term, so the payload rides
in behind "chat.name," just the same. The provider half is the sharper of the
two because it reads a SECOND database, the one providerDbName points at, which
the caller's own queries never touch.

The fix is the guard that already existed, moved to one place. sortColumn is now
the only mapping from a caller's sortField to a column, and sortDirection the
only mapping from sortOrder to ASC/DESC, so a fourth sort site cannot get it
wrong by being written somewhere else. The provider half also stops disagreeing
with its local half about the default when sortOrder is empty; they are
concatenated into one list and have to share a sort.

Tests assert on the SQL the driver receives — captured through QueryLogFunc,
since these two build and execute internally — so they fail if the quoting or
SnakeString changes underneath the whitelist.

Co-authored-by: Hanzo Dev <[email protected]>
2026-08-02 21:27:29 -07:00

197 lines
6.8 KiB
Go

package object
import (
"context"
"database/sql"
"strings"
"testing"
"time"
)
// sortPayloads are the values a caller can send as ?sortField=. Each is
// lowercase on purpose — that is what survives SnakeString — and each uses
// "/**/" where a space would go, because SnakeString strips spaces.
var sortPayloads = []string{
"(select 1)",
"case when (select 1)=1 then name else id end",
"(select group_concat(name) from sqlite_master)",
"name desc, (select 1)",
"{{name}}",
"[[name]]",
"name; drop table article",
"name--",
"(select/**/group_concat(name)/**/from/**/sqlite_master)",
"iif((select/**/count(*)/**/from/**/chat)>2,name,owner)",
"name,(select/**/group_concat(name)/**/from/**/sqlite_master)",
}
// sortPayloadFragments are the distinguishing substrings that must never appear
// in emitted SQL. A payload leaks if it carries a fragment and the SQL has it.
var sortPayloadFragments = []string{"select 1", "sqlite_master", "drop table", "case when", "--", "iif(", "group_concat"}
// assertNoPayloadLeak fails when a payload's own text reached the SQL, and when
// the query did not fall back to the default column.
func assertNoPayloadLeak(t *testing.T, payload, sql string) {
t.Helper()
if !strings.Contains(sql, "created_time") {
t.Errorf("sortField=%q did not fall back to the default; SQL = %s", payload, sql)
}
for _, frag := range sortPayloadFragments {
if strings.Contains(payload, frag) && strings.Contains(sql, frag) {
t.Errorf("sortField=%q leaked %q into SQL: %s", payload, frag, sql)
}
}
}
// captureSQL records every statement the adapter sends to the driver while fn
// runs. Some call sites build and execute the query internally, so the emitted
// SQL is only observable from the driver side.
func captureSQL(t *testing.T, fn func()) []string {
t.Helper()
var got []string
adapter.db.QueryLogFunc = func(ctx context.Context, d time.Duration, s string, rows *sql.Rows, err error) {
got = append(got, s)
}
defer func() { adapter.db.QueryLogFunc = nil }()
fn()
return got
}
// The sort column is caller-supplied and lands in an ORDER BY identifier
// position, where a bound parameter cannot reach. Two layers that look like
// they protect it do not:
//
// - dbx.QuoteColumnName returns its argument UNQUOTED when it contains "(",
// "{{" or "[[", so a value carrying a parenthesis is concatenated verbatim.
// - util.SnakeString only lowercases and inserts "_" before capitals, so an
// all-lowercase payload passes through it byte for byte.
//
// These assert on the SQL the real builder emits against the real driver, so
// they fail if either layer's behaviour changes underneath the whitelist.
func TestGetDbQuery_SortFieldCannotReachOrderBy(t *testing.T) {
restore, err := UseMemoryDB("file:sortfield?mode=memory&cache=shared", &Article{})
if err != nil {
t.Fatalf("memory db: %v", err)
}
defer restore()
for _, payload := range sortPayloads {
q := GetDbQuery("", 0, 10, "", "", payload, "ascend")
assertNoPayloadLeak(t, payload, q.Build().SQL())
}
}
// The chat list has a second sort site: ?field=messages searches message text
// through a join, and that branch builds its own ORDER BY with a "chat." table
// prefix instead of going through GetDbQuery. The prefix is not a defence — a
// comma starts a second ORDER BY term, so "name,(select …)" lands whole.
func TestGetPaginationChatsByMessages_SortFieldCannotReachOrderBy(t *testing.T) {
restore, err := UseMemoryDB("file:sortfieldchat?mode=memory&cache=shared", &Chat{}, &Message{})
if err != nil {
t.Fatalf("memory db: %v", err)
}
defer restore()
for _, payload := range sortPayloads {
for _, stmt := range captureSQL(t, func() {
_, _ = getPaginationChatsByMessages("", 0, 10, "needle", payload, "ascend", "")
}) {
assertNoPayloadLeak(t, payload, stmt)
}
}
}
// The provider list has a third sort site: when a separate provider database is
// configured, the remote half of the result set is sorted by a hand-built ORDER
// BY rather than by GetDbQuery. That half reads a DIFFERENT database, so a
// payload here walks rows the caller's own database does not even hold.
func TestGetPaginationProviders_SortFieldCannotReachOrderBy(t *testing.T) {
restore, err := UseMemoryDB("file:sortfieldprov?mode=memory&cache=shared", &Provider{})
if err != nil {
t.Fatalf("memory db: %v", err)
}
defer restore()
prev := providerAdapter
providerAdapter = adapter
defer func() { providerAdapter = prev }()
for _, payload := range sortPayloads {
for _, stmt := range captureSQL(t, func() {
_, _ = GetPaginationProviders("acme", "", -1, -1, "", "", payload, "ascend")
}) {
assertNoPayloadLeak(t, payload, stmt)
}
}
}
// The whitelist must not break real sorting. The UI sends Ant Design dataIndex
// names, which are alphanumeric; each must survive to its snake_case column.
func TestGetDbQuery_LegitimateSortFieldsStillSort(t *testing.T) {
restore, err := UseMemoryDB("file:sortfield2?mode=memory&cache=shared", &Article{})
if err != nil {
t.Fatalf("memory db: %v", err)
}
defer restore()
for field, want := range map[string]string{
"createdTime": "created_time",
"name": "name",
"displayName": "display_name",
"owner": "owner",
} {
q := GetDbQuery("", 0, 10, "", "", field, "descend")
sql := q.Build().SQL()
if !strings.Contains(sql, want) {
t.Errorf("sortField=%q should sort by %q, SQL = %s", field, want, sql)
}
if !strings.Contains(sql, "DESC") {
t.Errorf("sortField=%q lost its DESC direction: %s", field, sql)
}
}
}
// Same requirement for the two sites that build their own ORDER BY: a real
// column must still reach it, prefixed and directed as before.
func TestOtherSortSites_LegitimateSortFieldsStillSort(t *testing.T) {
restore, err := UseMemoryDB("file:sortfield3?mode=memory&cache=shared", &Chat{}, &Message{}, &Provider{})
if err != nil {
t.Fatalf("memory db: %v", err)
}
defer restore()
prev := providerAdapter
providerAdapter = adapter
defer func() { providerAdapter = prev }()
for field, want := range map[string]string{
"createdTime": "created_time",
"name": "name",
"displayName": "display_name",
} {
chatSQL := captureSQL(t, func() {
_, _ = getPaginationChatsByMessages("", 0, 10, "needle", field, "descend", "")
})
if len(chatSQL) == 0 {
t.Fatalf("sortField=%q: chat query emitted no SQL", field)
}
for _, stmt := range chatSQL {
if !strings.Contains(stmt, "`chat`.`"+want+"` DESC") {
t.Errorf("chat sortField=%q should sort by chat.%s DESC, SQL = %s", field, want, stmt)
}
}
provSQL := captureSQL(t, func() {
_, _ = GetPaginationProviders("acme", "", -1, -1, "", "", field, "ascend")
})
if len(provSQL) == 0 {
t.Fatalf("sortField=%q: provider query emitted no SQL", field)
}
for _, stmt := range provSQL {
if !strings.Contains(stmt, "`"+want+"` ASC") {
t.Errorf("provider sortField=%q should sort by %s ASC, SQL = %s", field, want, stmt)
}
}
}
}