Description
GORM Playground Link
Description
The current implementation of GORM's Raw method has a critical flaw in how it detects and processes named parameters. The method uses a simple strings.Contains(sql, "@") check to determine if the SQL contains named parameters, but this causes issues with PostgreSQL's full-text search operator @@.
Current Implementation
func (db *DB) Raw(sql string, values ...interface{}) (tx *DB) {
tx = db.getInstance()
tx.Statement.SQL = strings.Builder{}
if strings.Contains(sql, "@") {
clause.NamedExpr{SQL: sql, Vars: values}.Build(tx.Statement)
} else {
clause.Expr{SQL: sql, Vars: values}.Build(tx.Statement)
}
return
}
Problem
When a SQL query contains the PostgreSQL full-text search operator @@ (such as in to_tsvector('english', name) @@ plainto_tsquery('english', ?), the method incorrectly identifies it as containing named parameters due to the presence of @ characters. This causes:
The query to be processed as a named parameter query when it should be a positional parameter query
Parameter binding to fail with errors like "expected X arguments, got 0"
Syntax errors when trying to escape the @@ operator
Reproduction Steps
- Create a query using PostgreSQL full-text search with the @@ operator
- Pass the query to GORM's Raw method with positional parameters
- Observe the error: "expected X arguments, got 0" or syntax errors
// This query will fail due to the @@ operator
db.Raw(`
SELECT id, name,
to_tsvector('english', name) @@ plainto_tsquery('english', $1) as matches
FROM users
`, searchTerm).Scan(&results).Error
Haven't setup playground, will try to do it later on! Thank you :)
Things I've tried
Tried: @@ -> doesn't work
Changing to ILIKE as an option, but last resort