jackc / jackc/pgx

Question about simple protocol sanitization in `Query` vs `Exec`

Open
#2,471 0 comments 1 reaction 0 assignees View on GitHub
Dominant language
Go
Stars
14.3k
Forks
1.1k
Avg merge
6d 9h
Merged PRs (30d)
11

Description

I’m trying to understand the rationale behind the difference in how simple-protocol sanitization is applied between `Conn.Exec` and `Conn.Query`

**Context / motivation**
- The code already used `Query`
- I need to use the simple protocol because I rely on multi-statement SQLs
- The SQL uses dollar-quoted strings, which are valid PostgreSQL syntax
- No parameters are passed (the SQL is already fully constructed and assumed that they are safe from SQL injection)

**What I’m seeing**

In `Exec`, `sanitizeForSimpleQuery` is only called when arguments are present:
`https://github.com/jackc/pgx/blob/master/conn.go#L579-L584`
```go
if len(arguments) > 0 {
sql, err = c.sanitizeForSimpleQuery(sql, arguments...)
}
```

But in `Query`, when `QueryExecModeSimpleProtocol` is used, sanitization happens unconditionally, even when args is empty:
`https://github.com/jackc/pgx/blob/master/conn.go#L855-L856`
```go
} else if mode == QueryExecModeSimpleProtocol {
sql, err = c.sanitizeForSimpleQuery(sql, args...)
}
```

This makes `Query` understands an SQL such as `select $tag$1A2B$tag$` as having positional argument `$1`, which it then compares with the number of arguments passed and throws `insufficient argument`

**What I’m trying to clarify**

- Is this behavioral difference intentional?
- If so, what is the reason Query always sanitizes while Exec does not?
- Is there a supported way to run raw, already-valid SQL through Query using the simple protocol without triggering sanitization?
- As far as I understand, client side interpolation in simple protocol is used to prevent SQL injection
- I'm leaving the option of using `Exec` instead to last

Any clarification on the design intent here would be appreciated.

Contributor guide

Open the contributing guide

Research direction

Start in conn.go at the Conn.Exec and Conn.Query paths linked in the issue, then inspect sanitizeForSimpleQuery. Reproduce the QueryExecModeSimpleProtocol case with select $tag$1A2B$tag$ and compare it with Exec; done means the project’s intended behavior and supported approach are clarified.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, postgresql
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.