How to Safely Inject User Input into SQL Queries for Full-Text Search and Similarity Matching

[tkrajewski89]

Issue Description I'm looking for guidance on how to prevent SQL Injection when injecting user-provided values into SQL queries in Vapor with PostgreSQL.

Specifically, I'm working with a combination of the pg_trgm extension (for SIMILARITY) and tsvector full-text search in a query. I've been able to create a working SQL query, but I'm unsure how to safely bind parameters (e.g., the user input query parameter) to prevent SQL injection.

Simplified code example

let query = req.query[String.self, at: "query"]

// Start building the query
var myQuery = Item.query(on: req.db)

// Perform full-text search if query is provided
if let query = query {
  // Combine full-text search with pg_trgm similarity for typo-tolerant matches
  myQuery = myQuery.group(.or) { orGroup in
    // Full-text search using tsvector
    orGroup.filter(.custom("search @@ plainto_tsquery('english', '\(query)')"))
    // Fuzzy matching using trigram similarity (pg_trgm)
    orGroup.filter(.custom("similarity(name, '\(query)') > 0.3"))
  }

  // Sorting based on rank and similarity
  myQuery = myQuery.sort(.custom("""
    GREATEST(
      ts_rank(search, plainto_tsquery('english', '\(query)')),
      similarity(name, '\(query)')
    ) DESC
  """))
}

What I've tried I have also tried using .sql(unsafeRaw:) approach but couldn’t find a way to bind the query parameter safely, which is critical to prevent SQL injection.

What I'm looking for

1. Best practices or examples on how to safely inject user input into raw SQL queries when using pg_trgm and tsvector searches in Fluent.
2. How to properly bind parameters to ensure the query is safe and the input is sanitized.

Any help or examples on this would be greatly appreciated!