postgresqlgogo-gorm

How can identifiers like roles and tables be used dynamically in GORM with Raw SQL?

I am working on a Go project using GORM and PostgreSQL, and I need to dynamically use identifiers such as roles and tables in my SQL queries. However, I am encountering an issue with parameterized queries: they automatically add single quotes around the identifiers, which results in invalid SQL syntax for certain commands.

For example, I am trying to set the default schema for a role using the following code:

// Execute the ALTER ROLE statement to set search_path using parameterized query
if err := db.Exec("ALTER ROLE ? SET search_path TO ?", roleName, schemaName).Error; err != nil {
    return nil, fmt.Errorf("error setting default search path for user: %v", err)
}

This produces invalid SQL because the role name and search path cannot be single quoted:

ALTER ROLE 'role_name_foo' SET search_path TO 'search_path_baa'

...assuming role_name_foo was the value of roleName and search_path_baa was the value of schemaName. The specific error is:

SQL Error [42601]: ERROR: syntax error at or near "'role_name_foo'"
  Position: 12

It works if I change those to double-quotes, but as this query is generated by GORM I haven't been able to do so without hacks. I'd prefer to not resort to hand-rolling SQL-injection prevention measures. What's the best practice here?

I have reviewed: https://gorm.io/docs/sql_builder.html#Raw-SQL

I didn't find any specific notes about identifiers.

Solution

  • Parameter placeholders $<n> or ? are not for identifiers, they can only be used for values.

    If you are using the github.com/lib/pq as the Postgres driver, consider using pq.QuoteIdentifier:

    QuoteIdentifier quotes an "identifier" (e.g. a table or a column name) to be used as part of an SQL statement. For example:

    tblname := "my_table"
data := "my_data"
quoted := pq.QuoteIdentifier(tblname)
err := db.Exec(fmt.Sprintf("INSERT INTO %s VALUES ($1)", quoted), data)

    Any double quotes in name will be escaped. The quoted identifier will be case sensitive when used in a query. If the input string contains a zero byte, the result will be truncated immediately before it.

    In this case, the code for your scene probably would be:

    
stmt := fmt.Sprintf("ALTER ROLE %s SET search_path TO %s", roleName, schemaName)

// Execute the ALTER ROLE statement to set search_path using parameterized query
if err := db.Exec(stmt).Error; err != nil {
    return nil, fmt.Errorf("error setting default search path for user: %v", err)
}

    See pq package - func QuoteIdentifier

    The QuoteIdentifier escapes any existing double quotes with in the name arg, this would prevent potential SQL injections.

    But you are still taking the risk if the roleName and schemaName are derived from user inputs, users could access roles or schemas they should not have access to.

    The pgx driver also has a similar feature, you can use the pgx.Identifier type and the Sanitize() method of it.

    See pgx - type Identifier