Sanitizing Identifiers

[KeynesYouDigIt]

https://www.psycopg.org/docs/sql.html has this useful feature where tables, schemas, etc can be safely parametrized and added to a query.

query = sql.SQL("select {field} from {table} where {pkey} = %s").format(
    field=sql.Identifier('my_name'),
    table=sql.Identifier('some_table'),
    pkey=sql.Identifier('id'))

is this supported or on the roadmap?

I also asked the team working on https://github.com/qooleot/node-pg-query-template but I thought id mention it here as well.

https://stackoverflow.com/questions/62907228/sanitizing-user-inputs-when-the-user-input-is-a-schema-name-in-node-postgres provides a bit more background.

[charmander]

pg.Client.prototype has escapeIdentifier, although it’s undocumented; pg-format has %I. pg might document its escaping functions in the future, but I think the rest is the job of query builders (so indeed qooleot/node-pg-query-template if you’re using that).

Looking at the Stack Overflow question,

• I’m not sure how this is supposed to sanitize anything:

  const sanitizeOrgKey = async (key) => {
      const org_name = await db.query(builder('SELECT :orgKey::text')({'orgKey': key}))
      return org_name.rows[0].text
  }

  If key is a string, it looks like it’ll return it unchanged.

• On top of any escaping, remember that validating the name of the schema to make sure the user should have access to it is very important.

[KeynesYouDigIt]

thanks @charmander !!!!! needs docs of course but this was very helpful :)

[KeynesYouDigIt]

I skipped the templating for now (see updated SO question)