mirror of
https://github.com/hanzoai/ai.git
synced 2026-11-21 02:36:49 +00:00
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]>
197 lines
6.8 KiB
Go
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)
|
|
}
|
|
}
|
|
}
|
|
}
|