How to create dynamic GORM query with Join avoiding SQL injection?

Question

I'm trying to create query helpers for my repository pattern. Actually I'm dealing how to add join clauses. The only idea is to inject Sprintf statement but this behavior might cause SQL injection.

Here's my query builder

func getQueryOptions(q *gorm.DB, opts ...query.QueryOption) *gorm.DB {
    options := query.NewQueryOptions(opts...)

    if joins := options.Joins; joins != nil {
        for _, join := range joins {
            q = q.Clauses(clause.Join{
                Type: clause.JoinType(join.Type),
                Table: clause.Table{
                    Name:  join.Table.Name,
                    Alias: join.Table.Alias,
                },
                ON: clause.Where{Exprs: []clause.Expression{
                    clause.Expr{SQL: join.On, Vars: join.Args},
                }},
            })
        }
    }
if filters := options.Filters; filters != nil {
        for _, filter := range filters {
            q = q.Where(filter.Condition, filter.Args...)
        }
    }
 [...]
}

here's my test

dbGames, err := repo.FindAll(
                    query.WithJoin(query.JoinType.InnerJoin, "platforms_games", "pg", "pg.game_id = games.id"),
                    query.WithJoin(query.JoinType.InnerJoin, "platforms", "", "pg.platform_id = platforms.id"),
                    query.WithFilter("platforms.name = ?", "Platform 1"),
                )
                assert.NoError(t, err)
                assert.Len(t, dbGames, 1)

but it returns incorrect SQL query

SELECT * FROM "games" WHERE INNER JOIN "platforms_games" "pg" ON pg.game_id = games.id AND INNER JOIN "platforms" ON pg.platform_id = platforms.id AND platforms.name = 'Platform 1'

Answer 1

That happens because in your builder you’re using q.Clauses(clause.Join{ ... }), but by default GORM interprets it as a WHERE clause unless you wrap it properly in clause.Join.
try this, Instead of injecting raw SQL with Sprintf, stick with GORM’s clause API. For example:

q = q.Clauses(clause.Join{
    Type: clause.JoinType(join.Type),
    Table: clause.Table{
        Name:  join.Table.Name,
        Alias: join.Table.Alias,
    },
    ON: clause.Where{
        Exprs: []clause.Expression{
            clause.Eq{Column: clause.Column{Table: "pg", Name: "game_id"}, Value: clause.Column{Table: "games", Name: "id"}},
        },
    },
})

This way GORM knows it’s a JOIN clause, not a WHERE.