如何与数据库/ sql lib一起使用COALESCE？

Question

I'm trying to pass arguments into a postgres sql statement that are injection safe.

I’m having a bit of trouble with passing in arugements using DB.Query of the database/sql package in Go.

This is what Postgres registers

STATEMENT:  SELECT mc.company_name_full, msc.company_id, msc.cdate, msc.value->>'n_rules', msc.value->>'pct_interfaces_classified'
                  FROM mn_company AS mc INNER JOIN mn_statistics_company AS msc
                      ON (mc.id = msc.company_id)
                  WHERE (msc.value->>'n_rules')::int>0 AND (msc.value->>'pct_interfaces_classified')::int>0  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', 2017-09-19)

This is my function call

rows, err := a.DB.Query(*query, qf.companyIDFilter, qf.companyNameFilter, firstDate, secondDate)

all arguments that are being pasted into a.DB.Query are pointers.

I’m trying to replaces the $1, $2,… with arguments

Answer 1

Remove the quotes from around the placeholders ($1, $2, ...):

query := `SELECT mc.company_name_full, msc.company_id, msc.cdate, msc.value->>'n_rules', msc.value->>'pct_interfaces_classified'
                  FROM mn_company AS mc INNER JOIN mn_statistics_company AS msc
                      ON (mc.id = msc.company_id)
                  WHERE (msc.value->>'n_rules')::int>0 AND (msc.value->>'pct_interfaces_classified')::int>0  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, 2017-09-19)`

The lib/pq package automatically adds the quotes where needed.