How in pg to prepare queries with named variables in the LIKE clause between two percent?

Question

i have a code

import { Pool } from 'pg'
import { pg as named } from 'yesql'

const db = new Pool({
    host: 'localhost',
    user: 'postgres',
    password: '1',
    database: 'libcourse'
})

const query = `select * from table where column like '%:var%'`
const result = await db.query(named(query)({var: 'test'})).rows

:var is not recognized as a variable

Answer 1

I assume that yesql is only able to inject variables into an SQL statement where placeholders would also be allowed, otherwise SQL injection attacks would be too hard to detect. And a placeholder between percent signs is not allowed, neither is a placeholder in quotes.

The solution is to include the percent signs in the variable value:

const query = `select * from table where column like :var`
const result = (await db.query(named(query)({var: `%${'test'}%`))).rows

(Also, you should not await the rows, but take the rows of the awaited result.)