I'm currently working on implementing a search feature using the pg_trgm module in my PostgreSQL project built with TypeScript and TypeOrm. My SQL query that works for me looks like this:
SELECT t, similarity(t, 'word') AS sml
FROM test_trgm
WHERE t % 'word'
ORDER BY sml DESC, t;
However, as I began integrating it into my builder, I realized that I am unable to protect the select statement from SQL injection because TypeOrm does not provide a way to bind parameters in any of its select methods (like SelectQueryBuilder::addSelect, SelectQueryBuilder::select).
Below is an example of my vulnerable method:
...
applySearch(builder: SelectQueryBuilder<any>, needle: string) {
if (needle) {
builder.addSelect(`similarity(title, ${needle})`);
builder.andWhere('title % :needle', { needle });
}
}
...
If anyone knows of a better way to implement this search functionality within my current technology stack, I would greatly appreciate the advice.