sql select query column named in array how

[Mint-Joy]

in Knexjs I do it this way

const rolesTableName = "org_roles AS roles"
const roleFieldsList = [
    "roles.id",
    "roles.title",
]

    let aQuery = DB.from(rolesTableName)
    aQuery.orderBy("roles.sorting", "asc").orderBy("roles.title", "asc")
    const recs = await aQuery.select(roleFieldsList)

in Bun I do it this way

const cityFieldsList = [
	"id",
	"title",
]

  const cities = await sql`
  SELECT ${sql.array(cityFieldsList)} FROM city
  LIMIT ${2}
`

I receive

[{"json":["id","title"]},{"json":["id","title"]}]

How to set a variable to an array of strings for select.
P.S. I only present one option here, but I have used many of them.

const cityFieldsList = [
	"id",
	"title",
]

const fieldsString = cityFieldsList.join(", ")
const fieldsArray = sql.array(cityFieldsList)
const fieldsArray_ = sql.array(fieldsString)
console.log('fieldsArray', fieldsArray)
console.log('fieldsArray', fieldsArray_)

const fields = sql(cityFieldsList)
const fields_ = sql(fieldsString)
console.log('fields', fields)
console.log('fields', fields_)

const cities = sql("cities")
const cities_ = sql(fieldsString)
console.log('cities', cities)
console.log('cities', cities_)

const unsafe = sql.unsafe(cityFieldsList)
const unsafe_ = sql.unsafe(fieldsString)
console.log('unsafe', unsafe)
console.log('unsafe', unsafe_)

[heathdutton]

The issue is that sql.array() is meant for array values (like for an IN clause), not for column names. Column names are identifiers, and SQL parameterisation doesn't work for identifiers, only for values.

For dynamic column names you need sql.unsafe():

const cityFieldsList = ["id", "title"]
const columns = cityFieldsList.join(", ")
const cities = await sql`SELECT ${sql.unsafe(columns)} FROM city LIMIT ${2}`

That should give you what you expect.

BUT: Since unsafe does exactly what it says, you want to make sure those column names aren't coming from user input directly. If they are, whitelist them first:

const allowedColumns = ["id", "title", "name", "population"]
const requestedColumns = ["id", "title"] // from user or wherever

const safeColumns = requestedColumns.filter(col => allowedColumns.includes(col))
if (safeColumns.length === 0) {
  throw new Error("no valid columns")
}

const cities = await sql`SELECT ${sql.unsafe(safeColumns.join(", "))} FROM city LIMIT ${2}`

The reason this works differently than Knex is that Knex has its own query builder that knows the difference between a column reference and a value. Bun's sql template literals are more bare metal, they just do parameterised queries, and SQL parameters can only be values not identifiers. So anything structural (column names, table names, ORDER BY directions, etc) needs the unsafe escape hatch.

[heathdutton]

The issue is that sql.array() is meant for array values (like for an IN clause), not for column names. Column names are identifiers, and SQL parameterization doesn't really work for identifiers, only for values.

For dynamic column names you need sql.unsafe():

const cityFieldsList = ["id", "title"]
const columns = cityFieldsList.join(", ")
const cities = await sql`SELECT ${sql.unsafe(columns)} FROM city LIMIT ${2}`

That should give you what you expect.

Important though: since unsafe does exactly what it says, you want to make sure those column names aren't coming from user input directly. If they are, whitelist them first:

const allowedColumns = ["id", "title", "name", "population"]
const requestedColumns = ["id", "title"] // from user or wherever

const safeColumns = requestedColumns.filter(col => allowedColumns.includes(col))
if (safeColumns.length === 0) {
  throw new Error("no valid columns")
}

const cities = await sql`SELECT ${sql.unsafe(safeColumns.join(", "))} FROM city LIMIT ${2}`

The reason this works differently than Knex is that Knex has its own query builder that knows the difference between a column reference and a value. Bun's sql template literals are more bare metal, they just do parameterized queries, and SQL parameters can only be values not identifiers. So anything structural (column names, table names, ORDER BY directions, etc) needs the unsafe escape hatch.