mirror of
https://github.com/hanzoai/orm.git
synced 2026-10-30 15:01:22 +00:00
zeekayandHanzo Dev
ba9217ec1b
ToJSONFieldName lowercased the first letter of each path segment and passed
everything else through. The result is interpolated into a SQL string literal —
json_extract(data, '$.X') — so a quote inside the name ENDS the literal and the
rest is executed:
Order("x') UNION SELECT name FROM sqlite_master --")
-> ORDER BY json_extract(data, '$.x') UNION SELECT name FROM sqlite_master --') ASC
That is valid SQL. The same helper builds the WHERE clause, so a filter field
reached it too, and both SQLite and ZAP query builders funnel through it — five
call sites, one function.
Field paths reach this from callers that take them off the wire, which is the
shape this library has to assume: a sort field is a presentation concern that
frontends pass straight through.
Illegal characters are dropped rather than escaped or rejected. Dropping keeps
the signature, so no caller changes, and it fails CLOSED — a name that carried
punctuation becomes a field that does not exist, so a filter on it matches no
rows and an ORDER BY on it sorts every row equally. There is also no escaping to
get subtly wrong and no dialect to be right about, which is the property that
matters for a helper shared by two backends.
Tests assert on the emitted json_extract fragment and that the literal stays
balanced, so they fail if an interpolation site stops using this.
Co-authored-by: Hanzo Dev <[email protected]>
62 lines
2.2 KiB
Go
62 lines
2.2 KiB
Go
package db
|
|
|
|
import (
|
|
"fmt"
|
|
"strings"
|
|
"testing"
|
|
)
|
|
|
|
// A field path is interpolated into a SQL string literal —
|
|
// json_extract(data, '$.X') — so a quote inside it ENDS the literal and
|
|
// everything after it is executed. Callers pass this straight from a query
|
|
// string: commerce's generic REST list helper does .Order(c.Query("sort")) for
|
|
// every entity it serves, so ?sort= reached this with arbitrary text.
|
|
//
|
|
// This asserts on the SQL the query builders actually emit, not on the helper
|
|
// in isolation, so it fails if an interpolation site stops using it.
|
|
func TestJSONFieldPathCannotEscapeItsQuotes(t *testing.T) {
|
|
for _, payload := range []string{
|
|
`x') UNION SELECT name FROM sqlite_master --`,
|
|
`x'||(SELECT hex(randomblob(8)))||'`,
|
|
`x') --`,
|
|
`x'; DROP TABLE records; --`,
|
|
`a b`,
|
|
`x")`,
|
|
} {
|
|
got := ToJSONFieldName(payload)
|
|
if strings.ContainsAny(got, `'"();- `) {
|
|
t.Errorf("ToJSONFieldName(%q) = %q, which still carries SQL punctuation", payload, got)
|
|
}
|
|
// The shape both builders construct.
|
|
sql := fmt.Sprintf("json_extract(data, '$.%s')", got)
|
|
if strings.Count(sql, "'") != 2 {
|
|
t.Errorf("payload %q produced %s — the literal is no longer balanced", payload, sql)
|
|
}
|
|
}
|
|
}
|
|
|
|
// The filter must not change what a legitimate field path means: the
|
|
// PascalCase→camelCase conversion and nested paths are the reason it exists.
|
|
func TestJSONFieldPathKeepsLegitimateNames(t *testing.T) {
|
|
for in, want := range map[string]string{
|
|
"Name": "name",
|
|
"createdAt": "createdAt",
|
|
"Account.TransactionHash": "account.transactionHash",
|
|
"user_id": "user_id",
|
|
"Line1": "line1",
|
|
"": "",
|
|
} {
|
|
if got := ToJSONFieldName(in); got != want {
|
|
t.Errorf("ToJSONFieldName(%q) = %q, want %q", in, got, want)
|
|
}
|
|
}
|
|
}
|
|
|
|
// A stripped name must name a field that does not exist, so the query fails
|
|
// CLOSED — a filter matches no rows rather than more of them.
|
|
func TestStrippedPathIsNotAnExistingField(t *testing.T) {
|
|
if got := ToJSONFieldName(`name') OR ('1'=('1`); got == "name" {
|
|
t.Error("a payload prefixed with a real field collapsed back to that field, which would filter on it")
|
|
}
|
|
}
|