README
¶
MQL
The mql (Model Query Language) Go package provides a language that end users can use to query your database models, without them having to learn SQL or exposing your application to SQL injection.
Examples
github.com/go-gorm/gorm
w, err := mql.Parse(`name="alice" or name="bob"`,User{})
if err != nil {
return nil, err
}
err = db.Where(w.Condition, w.Args...).Find(&users).Error
database/sql
w, err := mql.Parse(`name="alice" or name="bob"`,User{}, mql.WithPgPlaceholders())
if err != nil {
return nil, err
}
q := fmt.Sprintf("select * from users where %s", w.Condition)
rows, err := db.Query(q, w.Args...)
github.com/hashicorp/go-dbw
w, err := mql.Parse(`name="alice" or name="bob")`,User{})
if err != nil {
return nil, err
}
err := rw.SearchWhere(ctx, &users, w.Condition, w.Args)
Some bits about usage
First, you define a model you wish to query as a Go struct and then provide a mql
query. The package then uses the query along with a model to generate a
parameterized SQL where clause.
Fields in your model can be compared with the following operators: =, !=,
>=, <=, <, >, % .
Strings must be quoted. Double quotes ", single quotes ' or backticks ` can be used as delimiters. Users can choose whichever supported delimiter
makes it easier to quote their string.
Comparison operators can have optional leading/trailing whitespace.
The % operator allows you to do partial string matching using LIKE "%value%". This
matching is case insensitive.
The = equality operator is case insensitive when used with string fields.
Comparisons can be combined using: and, or.
More complex queries can be created using parentheses.
See GRAMMAR.md for a more complete documentation of mql's grammar.
Example query:
name="alice" and age > 11 and (region % 'Boston' or region="south shore")
Date/Time fields
If your model contains a time.Time field, then we'll append ::date to the
column name when generating a where clause and the comparison value must be in
an ISO-8601 format.
Note: It's possible to compare date-time fields down to the
millisecond using ::date and a literal in ISO-8601 format.
Currently, this is the only supported way to compare dates, if you need something different then you'll need to provide your own custom validator/converter via WithConverter(...) when calling mql.Parse(...).
We provide default validation+conversion of fields in a model when parsing and generating a WhereClause. You can provide optional validation+conversion functions for fields in your model via WithConverter(...).
Example date comparison down to the HH::MM using an ISO-8601 format:
name="alice" and created_at>"2023-12-01 14:01"
Note: Expressions with the same level of precedence are evaluated right to left.
Example:
name="alice" and age > 11 and region = "Boston" is evaluated as: name="alice" and (age > 11 and region = "Boston")
Mapping field names
You can also provide an optional map from query column identifiers to model field names via WithColumnMap(...) if needed.
Example WithColumnMap(...) usage:
type User struct {
FullName string
}
// map the column alice to field name FullName
columnMap := map[string]string{
"name": "FullName",
}
w, err := mql.Parse(
`name="alice"`,
User{},
mql.WithColumnMap(columnMap))
if err != nil {
return nil, err
}
Mapping via struct tags
You can use struct tags to map model fields to column names by using WithColumnFieldTag(...). This allows you to define the mapping in your struct definition rather than at query time.
Example WithColumnFieldTag(...) usage:
type User struct {
Name string `db:"full_name"`
}
w, err := mql.Parse(
`Name="alice"`,
User{},
mql.WithColumnFieldTag("db"))
if err != nil {
return nil, err
}
fmt.Print(w.Condition) // prints full_name=?
Mapping output column names
You can also provide an optional map from model field names to output column names via WithTableColumnMap(...) if needed.
Example WithTableColumnMap(...) usage:
type User struct {
FullName string
}
// map the field name FullName to column "u.fullname"
tableColumnMap := map[string]string{
"fullname": "u.fullname",
}
w, err := mql.Parse(
`FullName="alice"`,
User{},
mql.WithTableColumnMap(tableColumnMap))
if err != nil {
return nil, err
}
fmt.Print(w.Condition) // prints u.fullname=?
Ignoring fields
If your model (Go struct) has fields you don't want users searching then you can optionally provide a list of columns to be ignored via WithIgnoreFields(...)
Example WithIgnoreFields(...) usage:
type User {
Name string
CreatedAt time.Time
UpdatedAt time.Time
}
// you want to keep users from using queries that include the user fields
// of: created_at updated_at
w, err := mql.Parse(
`name="alice"`,
User{},
mql.WithIgnoreFields("CreatedAt", "UpdatedAt"))
if err != nil {
return nil, err
}
Custom converters/validators
Sometimes the default out-of-the-box bits doesn't fit your needs. If you need to override how expressions (column name, operator and value) is converted and validated during the generation of a WhereClause, then you can optionally provide your own validator/convertor via WithConverter(...)
Example WithConverter(...) usage:
// define a converter for mySQL dates
mySQLDateConverter := func(columnName string, comparisonOp mql.ComparisonOp, value *string) (*mql.WhereClause, error) {
// you should add some validation of function parameters here.
return &mql.WhereClause{
Condition: fmt.Sprintf("%s%sSTR_TO_DATE(?)", columnName, comparisonOp),
Args: []any{*value},
}, nil
}
w, err := mql.Parse(
`name="alice" and created_at > "2023-06-18"`,
User{},
mql.WithConverter("CreatedAt", mySqlDateConverter))
if err != nil {
return nil, err
}
Grammar
See: GRAMMAR.md
Security
Please note: We take security and our users' trust very seriously. If you believe you have found a security issue, please responsibly disclose by contacting us at security@hashicorp.com.
Contributing
Thank you for your interest in contributing! Please refer to CONTRIBUTING.md for guidance.
Documentation
¶
Overview ¶
Package mql provides a language that end users can use to query your database models, without them having to learn SQL or exposing your application to SQL injection.
Examples ¶
Gorm: https://github.com/go-gorm/gorm
w, err := mql.Parse(`name="alice" or name="bob"`,User{})
if err != nil {
return nil, err
}
err = db.Where(w.Condition, w.Args...).Find(&users).Error
database/sql: https://pkg.go.dev/database/sql
w, err := mql.Parse(`name="alice" or name="bob"`,User{})
if err != nil {
return nil, err
}
q := fmt.Sprintf("select * from users where %s", w.Condition)
rows, err := db.Query(q, w.Args...)
go-dbw: https://github.com/hashicorp/go-dbw
w, err := mql.Parse(`name="alice" or name="bob")`,User{})
if err != nil {
return nil, err
}
err := rw.SearchWhere(ctx, &users, w.Condition, w.Args)
Simple Usage ¶
You define a model you wish to query as a Go struct and provide a mql query. The package then uses the query along with a model to generate a parameterized SQL where clause.
Fields in your model can be compared with the following operators: =, !=, >=, <=, <, >, %
Strings must be quoted. Double quotes ", single quotes ' or backticks ` can be used as delimiters. Users can choose whichever supported delimiter makes it easier to quote their string.
Comparison operators can have optional leading/trailing whitespace.
The % operator allows you to do partial string matching using LIKE and this matching is case insensitive.
The = equality operator is case insensitive when used with string fields.
Comparisons can be combined using: and, or.
More complex queries can be created using parentheses.
See [GRAMMAR.md]: https://github.com/hashicorp/mql/blob/main/GRAMMAR.md for a more complete documentation of mql's grammar.
Example query:
name="alice" and age > 11 and (region % "Boston" or region="south shore")
Index ¶
Constants ¶
This section is empty.
Variables ¶
var ( ErrInternal = errors.New("internal error") ErrInvalidParameter = errors.New("invalid parameter") ErrInvalidNotEqual = errors.New(`invalid "!=" token`) ErrMissingExpr = errors.New("missing expression") ErrUnexpectedExpr = errors.New("unexpected expression") ErrUnexpectedClosingParen = errors.New("unexpected closing paren") ErrMissingClosingParen = errors.New("missing closing paren") ErrUnexpectedOpeningParen = errors.New("unexpected opening paren") ErrUnexpectedLogicalOp = errors.New("unexpected logical operator") ErrUnexpectedToken = errors.New("unexpected token") ErrInvalidComparisonOp = errors.New("invalid comparison operator") ErrMissingComparisonOp = errors.New("missing comparison operator") ErrMissingColumn = errors.New("missing column") ErrInvalidLogicalOp = errors.New("invalid logical operator") ErrMissingLogicalOp = errors.New("missing logical operator") ErrMissingRightSideExpr = errors.New("logical operator without a right side expr") ErrMissingComparisonValue = errors.New("missing comparison value") ErrInvalidColumn = errors.New("invalid column") ErrInvalidNumber = errors.New("invalid number") ErrInvalidComparisonValueType = errors.New("invalid comparison value type") ErrMissingEndOfStringTokenDelimiter = errors.New("missing end of stringToken delimiter") ErrInvalidTrailingBackslash = errors.New("invalid trailing backslash") ErrInvalidDelimiter = errors.New("invalid delimiter") )
Functions ¶
This section is empty.
Types ¶
type ComparisonOp ¶
type ComparisonOp string
ComparisonOp defines a set of comparison operators
const ( GreaterThanOp ComparisonOp = ">" GreaterThanOrEqualOp ComparisonOp = ">=" LessThanOp ComparisonOp = "<" LessThanOrEqualOp ComparisonOp = "<=" EqualOp ComparisonOp = "=" NotEqualOp ComparisonOp = "!=" ContainsOp ComparisonOp = "%" )
type Option ¶
type Option func(*options) error
Option - how options are passed as args
func WithColumnFieldTag ¶ added in v0.1.5
WithColumnFieldTag provides an optional struct tag to use for field mapping If a field has this tag, the tag value will be used instead of the field name
func WithColumnMap ¶
WithColumnMap provides an optional map of columns from the user provided query to a field in the given model
func WithConverter ¶
func WithConverter(fieldName string, fn ValidateConvertFunc) Option
WithConverter provides an optional ConvertFunc for a column identifier in the query. This allows you to provide whatever custom validation+conversion you need on a per column basis. See: DefaultValidateConvert(...) for inspiration.
func WithIgnoredFields ¶
WithIgnoredFields provides an optional list of fields to ignore in the model (your Go struct) when parsing. Note: Field names are case sensitive.
func WithPgPlaceholders ¶ added in v0.1.1
func WithPgPlaceholders() Option
WithPgPlaceholders will use parameters placeholders that are compatible with the postgres pg driver which requires a placeholder like $1 instead of ?. See:
func WithTableColumnMap ¶ added in v0.1.5
WithTableColumnMap provides an optional map of columns from the model to the table.column name in the generated where clause
For example, if you need to map the language field name to something more complex in your SQL statement then you can use this map:
WithTableColumnMap(map[string]string{"language":"preferences->>'language'"})
In the example above we're mapping "language" field to a json field in the "preferences" column. A user can say `language="blah"` and the mql-created SQL where clause will contain `preferences->>'language'="blah"`
The field names in the keys to the map should always be lower case.
type ValidateConvertFunc ¶
type ValidateConvertFunc func(columnName string, comparisonOp ComparisonOp, value *string) (*WhereClause, error)
ValidateConvertFunc validates the value and then converts the columnName, comparisonOp and value to a WhereClause
type WhereClause ¶
type WhereClause struct {
// Condition is the where clause condition
Condition string
// Args for the where clause condition
Args []any
}
WhereClause contains a SQL where clause condition and its arguments.
Source Files
Directories