GORM Raw SQL and the Builder Without Losing Safety

GORM's builder covers a lot, and then a query needs a window function, a recursive CTE, a lateral join or a FILTER clause, and the builder has no method for it. GORM has an escape hatch for that: Raw for reads and Exec for writes. The danger is not the hatch itself. It is that the same string accepting methods that take SQL fragments (Where, Order, Select, Joins) all treat what you pass as SQL text, and a value spliced into that text is an injection hole. This article uses each form against PostgreSQL 18.6, then runs one hostile input through the safe and unsafe versions.

It follows the GORM tutorial. For the general rules, SQL injection in Go covers the bug class with database/sql, and the dynamic query builder article shows how to assemble variable queries without text splicing.

The lab

The program seeds three users, runs each form of raw access, and ends with the injection comparison.

package main

import (
	"database/sql"
	"fmt"
	"log"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

// connect recreates a schema and returns a connection whose search_path points at it.
func connect(schema string, cfg *gorm.Config) *gorm.DB {
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)
	must(admin.Exec("drop schema if exists " + schema + " cascade").Error)
	must(admin.Exec("create schema " + schema).Error)
	if cfg == nil {
		cfg = &gorm.Config{Logger: logger.Discard}
	}
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), cfg)
	must(err)
	return db
}

type User struct {
	ID    uint
	Name  string
	Email string
	Age   int
}

func main() {
	db := connect("l0713", nil)
	must(db.AutoMigrate(&User{}))
	db.Create(&[]User{{Name: "ada", Email: "[email protected]", Age: 36}, {Name: "bob", Email: "[email protected]", Age: 41}, {Name: "cy", Email: "[email protected]", Age: 29}})

	// Raw with Scan into a struct and into a slice of scalars.
	var users []User
	db.Raw("select * from users where age > ? order by age", 30).Scan(&users)
	fmt.Println("raw scan:", len(users), users[0].Name, users[1].Name)

	var names []string
	db.Raw("select name from users order by name").Scan(&names)
	fmt.Println("scalars:", names)

	// A custom projection into a struct that is not a model.
	type stat struct {
		Count int
		Avg   float64
	}
	var st stat
	db.Raw("select count(*) as count, avg(age) as avg from users").Scan(&st)
	fmt.Printf("stat: %d %.1f\n", st.Count, st.Avg)

	// Named arguments.
	var found User
	db.Raw("select * from users where name = @name or email = @email limit 1", sql.Named("name", "bob"), sql.Named("email", "x")).Scan(&found)
	fmt.Println("named:", found.Name)

	// Exec for writes, with RowsAffected.
	res := db.Exec("update users set age = age + 1 where age > ?", 30)
	fmt.Println("exec rows:", res.RowsAffected)

	// Mixing the builder with a SQL fragment.
	var older []User
	db.Where("age > ?", 30).Where("lower(name) like ?", "a%").Order(gorm.Expr("age desc")).Find(&older)
	fmt.Println("builder + fragment:", len(older))

	// The injection demo: the same input, built two ways.
	input := "x' or '1'='1"
	var unsafe []User
	db.Raw(fmt.Sprintf("select * from users where name = '%s'", input)).Scan(&unsafe)
	fmt.Println("concatenated rows:", len(unsafe))
	var safe []User
	db.Raw("select * from users where name = ?", input).Scan(&safe)
	fmt.Println("placeholder rows:", len(safe))

	// Where with a string is a fragment too, so the same rule applies to it.
	var viaWhere []User
	db.Where("name = '" + input + "'").Find(&viaWhere)
	fmt.Println("Where with concatenation rows:", len(viaWhere))
	var viaMap []User
	db.Where(map[string]any{"name": input}).Find(&viaMap)
	fmt.Println("Where with map rows:", len(viaMap))

	// Order and column names cannot be bind parameters; allow list them.
	col := "age"
	allowed := map[string]bool{"age": true, "name": true}
	if allowed[col] {
		var sorted []User
		db.Order(col + " desc").Find(&sorted)
		fmt.Println("sorted first:", sorted[0].Name)
	}

	// Rows for streaming big results.
	rows, err := db.Raw("select id, name from users order by id").Rows()
	must(err)
	defer rows.Close()
	n := 0
	for rows.Next() {
		var u User
		must(db.ScanRows(rows, &u))
		n++
	}
	fmt.Println("streamed rows:", n)
}

raw scan: 2 ada bob
scalars: [ada bob cy]
stat: 3 35.3
named: bob
exec rows: 2
builder + fragment: 1
concatenated rows: 3
placeholder rows: 0
Where with concatenation rows: 3
Where with map rows: 0
sorted first: bob
streamed rows: 3

Raw and Scan

db.Raw(sql, args...) builds a statement and Scan(&dest) runs it and fills the destination. The destination can be many shapes, and the lab used three:

• A slice of model structs. select * from users where age > ? order by age returned 2 users, ada then bob. Columns are matched to fields by their snake case names, so select * works when the struct covers the columns.
• A slice of scalars. select name from users order by name filled a []string: [ada bob cy]. Selecting one column into a slice of its type is the quick way to get a list of ids.
• A struct that is not a model. The stat struct has Count and Avg, and select count(*) as count, avg(age) as avg filled it: 3 35.3. Define small result structs next to the query for reports and aggregates. Alias columns to match the field names, because the match is by name.

Raw does not run until Scan, Find or a similar finisher, and it returns the usual *gorm.DB, so you check .Error as always. Scan into a struct does not return ErrRecordNotFound for no rows. It leaves the struct at its zero value and reports RowsAffected of 0, so check that when absence matters.

Named arguments

PostgreSQL uses numbered placeholders ($1) and GORM translates ? for you. For queries with repeated values or many parameters, named arguments read better. The lab's query used @name and @email with sql.Named(...) values, and returned bob: named: bob. A struct or a map also works as the argument (map[string]any{"name": "bob"}), and each @name in the text is replaced by one bind parameter, in order.

Use named arguments when the same value appears twice. A positional query with six ? is where a swapped pair of arguments hides.

Exec for writes

db.Exec("update users set age = age + 1 where age > ?", 30) ran the statement and reported exec rows: 2, available as res.RowsAffected. Exec is also the right call for DDL and for SET LOCAL statements. It does not fire hooks and does not touch updated_at, because GORM does not parse the SQL. Those are good reasons to prefer the builder for ordinary updates and keep Exec for bulk statements where the savings matter. The hooks article lists what is skipped.

Mixing the builder with a fragment

You rarely have to drop the builder entirely. A fragment can sit inside a chain: Where("lower(name) like ?", "a%") and Order(gorm.Expr("age desc")) in the lab combined with other conditions and returned one row. gorm.Expr("balance - ?", amount) is the way to write a column expression in Update, so a decrement happens in the database and not in a read modify write loop. clause.Expr, clause.Column and clause.OrderBy are the typed versions of the same idea, and Select accepts expressions for the column list. When the 90 percent of a query is simple and one piece is not, a fragment keeps the rest of the chain.

The injection comparison

The last part of the lab sends one hostile string, x' or '1'='1, through four ways of writing the same lookup by name:

The concatenated forms turned name = 'x' or '1'='1' into a condition that is always true and returned every row. The placeholder forms sent the whole string to PostgreSQL as a value, compared it to the name column and found no match. The input is the same, and the only difference is whether it reached the database as part of the SQL text or as a parameter.

Note the third row. Where takes a string, and a string with no ? in it is treated as SQL. That is documented, and it surprises people who assume the builder is always safe. The rule is the same one: a value never goes into the text of a statement. Use ?, a struct or a map.

The same applies to Order, Select, Group, Having and Joins. For these, a placeholder helps only when the fragment holds a value. Joins("left join orders on orders.user_id = ? ", id) is fine. A column name or direction taken from a request is another matter.

Names cannot be parameters

A bind parameter stands for a value, not an identifier. Order("? desc", col) would sort by a constant string and do nothing useful, and the column name has to be spliced into the text. The lab shows the safe way, an allow list:

allowed := map[string]bool{"age": true, "name": true}
if allowed[col] {
	db.Order(col + " desc").Find(&sorted)
}

The sort key comes from a fixed set, so no input can reach the SQL text. The same pattern covers sort direction (only asc or desc), table names and column lists for dynamic reports. Build the map once, reject anything outside it with a 400, and never try to "sanitise" a name by stripping characters. A related helper is clause.OrderByColumn{Column: clause.Column{Name: col}}, which quotes the identifier, but quoting does not stop someone ordering by a column you did not mean to expose, so the allow list is still needed.

Streaming with Rows

For large result sets, db.Raw(...).Rows() returns a *sql.Rows that you iterate, and db.ScanRows(rows, &u) fills a model from the current row. The lab streamed three rows. The benefit is memory: rows are decoded one at a time instead of into a slice. Close the rows (defer rows.Close()) and check rows.Err() after the loop. The cost is that you manage the cursor yourself, and the connection stays checked out until you finish, so do not hold it open while calling slow services.

When to leave GORM

If most of a service's queries are raw, the ORM is adding a layer you are not using. Two honest alternatives are to keep GORM for the simple CRUD tables and write the reports with sqlc, or to use the standard database/sql for those paths (reachable from GORM with db.DB()). Mixing is fine. A single transaction can run both if you pass the same connection, and for queries with Raw inside a GORM transaction use the tx handle.

Checklist

• Pass values as ?, named arguments, a struct or a map. Never format them into the text.
• Remember that Where, Having, Joins and friends treat a plain string as SQL.
• Allow list every identifier that comes from a request.
• Use a small result struct for aggregates, and alias columns to match field names.
• Check RowsAffected from Exec, and rows.Err() after streaming.

The query forms are in the GORM SQL builder guide, and the broader rule in the OWASP SQL injection prevention cheat sheet.