COALESCE在Postgres中返回文本类型而不是time_stamp类型

Question

I'm trying to use COALESCE to deal with sql injections in Go.

query := `SELECT mc.company_name_full, msc.company_id, msc.cdate, %s
          FROM %s AS mc INNER JOIN %s AS msc
          ON (mc.id = msc.company_id)
          WHERE %s AND
          msc.company_id = COALESCE($1, msc.company_id) AND
          mc.company_name_full ~* COALESCE($2, mc.company_name_full) AND
          msc.cdate >= '2017-07-01' AND
          msc.cdate <= '%s'`
query = fmt.Sprintf(query, selectParam, companyTable, statsTable, whereParam, time.Now().Local().Format("2006-01-02"))

This query works, but when I try to use COALESCE with Time_stamp

query := `SELECT mc.company_name_full, msc.company_id, msc.cdate, %s
          FROM %s AS mc INNER JOIN %s AS msc
          ON (mc.id = msc.company_id)
          WHERE %s AND
          msc.company_id = COALESCE($1, msc.company_id) AND
          mc.company_name_full ~* COALESCE($2, mc.company_name_full) AND
          msc.cdate >= COALESCE($3, '2017-07-01') AND
          msc.cdate <= COALESCE($4, '%s')`

but I get this error

pq:operator does not exist: timestamp without time zone >= text

What can I do to ensure that COALESCE returns a timestamp type?

Answer 1

You can cast the text string to a timestamp, like so:

`SELECT mc.company_name_full, msc.company_id, msc.cdate, %s
      FROM %s AS mc INNER JOIN %s AS msc
      ON (mc.id = msc.company_id)
      WHERE %s AND
      msc.company_id = COALESCE($1, msc.company_id) AND
      mc.company_name_full ~* COALESCE($2, mc.company_name_full) AND
      msc.cdate >= COALESCE($3, '2017-07-01'::timestamp) AND
      msc.cdate <= COALESCE($4, '%s'::timestamp)`

General Caveat:

I'm not too familiar with the syntax of Go, but from what I can tell, %s here is used by Sprintf for direct formatting (interpolation), so having these in your where clause still technically allows SQL Injection.

Anywhere possible (and for some things, like table names, it really isn't, but even in that case you need to be as careful as possible from where the variables that are interpolated come), bindings should be used.

e.g. why can't time.Now().Local().Format("2006-01-02") be passed as a binding and not interpolated?

Update in response to comment from OP:

From the Go lib/pq package doc, in the example code:

rows, err := db.Query(`SELECT name FROM users WHERE favorite_fruit = $1
    OR age BETWEEN $2 AND $2 + 3`, "orange", 64)

the $1 and $2 are bindings. That is to say, the library handles escaping them (Go's pq package is a Go wrapper around the pure Postgres libpq library), and should prevent SQL Injection. They bind (that is, connect to) the arguments that are subsequently passed.

e.g. $1 maps to "orange" and $2 maps to 64.

The references to %s in your original queries, in contrast, are directly formatted -- meaning there is no Postgres-based (libpq) escaping, and as such, no protection from SQL Injection. The values are simply passed right on through.

More detail on Go formatting.