CODEWITHSUNDEEP
fluentvapor
user2555515
user2555515

Reputation: 1051

How to properly query Postgres JSONB field in Vapor/Fluent

I have a table with some jsonb columns created by a migration like this:

    public func prepare(on database: Database) -> EventLoopFuture<Void> {
        return database.schema(MyTable.schema)
            .id()
            .field(.metadata, .custom("JSONB"), .required)
            .create()
    }

I am trying to filter query on jsonb field. The following is a simple string interpolation that works.

//jsonFilters is a dictionary of key value pair for which we want to filter in jsonb field            
            
            var query = MyTable.query(on: db)
            var filterString  = ""
            var cycleCount = 0;
            jsonFilters.forEach({
                (key, value) in
                filterString +=
                "metadata->>'\(key)' = '\(value)' "
                cycleCount+=1
                if(cycleCount < filter.metadata!.count) {
                    filterString += " AND "
                }
            })
            query = query.filter(.custom(metadataString))
            // Also filter on something else.
            query = query.filter(....)

However this is not secure and is sql injection vulnerable. Is there a way to bind the filter arguments in for example using SQLQueryString? It should work in conjunction with the rest of the regular filter. ( Last line in the code)

Upvotes: 1

Views: 183

Answers (1)

user2555515
user2555515

Reputation: 1051

Just in case someone runs into the same here is what works with SQLQueryString so you can pass the parameters instead of string interpolation:

        var queryString = SQLQueryString("")
        var cycleCount = 0;
        filter.metadata!.forEach({
            (key, value) in
            
            queryString.appendLiteral("metadata->>")
            queryString.appendInterpolation(bind: key)
            queryString.appendLiteral(" = ")
            queryString.appendInterpolation(bind: value)
           
            cycleCount+=1
            if(cycleCount < filter.metadata!.count) {
                queryString.appendLiteral(" AND ")
            }
        })

Upvotes: 1

Related Questions