Gorm Query from JSON

Want your users to be able to perform advanced searches on your database without all the hassle of handling each individual input - just provide a schema and the library will validate that your users are only doing queries you permit.

Install

go get github.com/CADawg/GormQueryFromJson

Usage

Set maximum levels of recursion

GormQueryFromJson.MaxDepth = 10

Define a schema

var mainSchema = []GormQueryFromJson.AcceptableQueryTypes{
	{
		ColumnName: "id",
		QueryTypes: []query.TypeIdentifier{GormQueryFromJson.TypeNumber},
		Limit:      1,
	},
	{
		ColumnName: "actor",
		QueryTypes: []query.TypeIdentifier{GormQueryFromJson.TypeString, GormQueryFromJson.TypeStrictString},
		Limit:      10,
	},
	{
		ColumnName: "target",
		QueryTypes: []query.TypeIdentifier{GormQueryFromJson.TypeString, GormQueryFromJson.TypeStrictString},
		Limit:      10,
	}
}

• Limit sets the maximum times this column can be targeted in a query.
• QueryTypes defines what queries can be run against the column
  • Or Is not a type of query for this purpose, only its children count

Define an endpoint like so

http.HandleFunc("POST /testQuery", func(res http.ResponseWriter, req *http.Request) {
    var queries []query.JSON

    err := json.NewDecoder(req.Body).Decode(&queries)

    if err != nil {
        http.Error(res, fmt.Sprintf("Error: %v", err), http.StatusBadRequest)
        return
    }

    statement := db.ToSQL(func(tx *gorm.DB) *gorm.DB {
        // use gorm to create a sql query and print out the statement
        tx = tx.Model(&types.HistoryEntry{}).Limit(100)

        // !!! This is our function
        tx, _, err = GormQueryFromJson.DoQuery(queries, tx, mainSchema)

        return tx.Find(&[]types.HistoryEntry{})
    })

    if err != nil {
        http.Error(res, fmt.Sprintf("Error: %v", err), http.StatusBadRequest)
        return
    }

    _, err = res.Write([]byte(statement))
})

You can use it however you like, just make sure that you have not placed any existing where conditions on the query. You can do so after calling DoQuery

Queries (User Side)

[
    {
        "type": "or",
        "query": [
            {
                "type": "string",
                "query": {
                    "column": "target",
                    "contains": "user2"
                }
            },
            {
                "type": "string",
                "query": {
                    "column": "target",
                    "contains": "user1"
                }
            }
        ]
    },
    {
        "type": "or",
        "query": [
            {
                "type": "string",
                "query": {
                    "column": "actor",
                    "contains": "user3"
                }
            },
            {
                "type": "string",
                "query": {
                    "column": "target",
                    "contains": "user4"
                }
            }
        ]
    },
    {
        "type": "number",
        "query": {
            "column": "id",
            "greaterThanOrEqual": 5000000
        }
    }
]

-> SQL (Where Clauses generated by above)

SELECT * FROM `table` WHERE (target LIKE '%user2%' OR target LIKE '%user1%') AND (actor LIKE '%user3%' OR target LIKE '%user4%') AND id >= 5000000

If any columns you have not allowed are included or the amount is above the limit, those where clauses will be discarded.