parameterized queries wont read in (using express and postgresql)

Question

Complete rookie here,

I'm trying to refactor my endpoint to allow for sorting the return table by several columns read in via variables 'sort_by' and 'order' which are set to default values, however when running the tests it brings back an unordered array. I've tried the command manuualy through postgresql terminal so i know the syntax is right, the problem is when trying to insert the variable as a parameter in express, its like it ignores the ORDER BY line and I'm not quite sure why as i have similar endpoints using the same function that work as intended, the only difference i can see is that in this instance the value is not before an = sign. any pointers would be a godsend as this is becoming quite a blocker for my project.

My techstack is :

"pg-promises": "^8.7.3"
"express": "^4.18.2",
"postgresql" : ^14.10 

using the below command and function:

return request(app).get("/api/articles")

exports.fetchAllArt = (sort_by = 'created_at',order = 'DESC')=>{

        return db.query(
            `SELECT 
            articles.article_id,
            articles.author,
            articles.title,
            articles.topic,
            articles.created_at,
            articles.votes,
            articles.article_img_url,
            COUNT(comments.article_id) AS comment_count
            FROM articles
            LEFT JOIN comments 
            ON articles.article_id = comments.article_id 
            GROUP BY articles.article_id
            ORDER BY ($1), ($2)`,[sort_by, order]
            ).then(({rows})=>{
                return rows
            })
    }
}

i have console logged both values being passed in, they are correct, i have tried various syntax with the above value , example combining both values into one string via string laterals.

Answer 1

Your issue seems to be related to how SQL handles parameters in prepared statements, especially in the context of dynamic sorting with ORDER BY. In PostgreSQL, when you use parameters (like $1, $2) in a query, they are treated as literal values, not as identifiers or keywords. This means that your ORDER BY ($1), ($2) clause is not being interpreted as you expect; instead of using the contents of sort_by and order as column names and sort directions, it's treating them as string literals.

To address this, you'll need to dynamically construct your SQL query to include the correct column names and sort order. However, this approach should be handled with care to avoid SQL injection vulnerabilities. Since pg-promise supports named parameters, you can safely construct your query as follows:

exports.fetchAllArt = (sort_by = 'created_at', order = 'DESC') => {

// Construct the query with the validated parameters
const query = `
    SELECT 
    articles.article_id,
    articles.author,
    articles.title,
    articles.topic,
    articles.created_at,
    articles.votes,
    articles.article_img_url,
    COUNT(comments.article_id) AS comment_count
    FROM articles
    LEFT JOIN comments 
    ON articles.article_id = comments.article_id 
    GROUP BY articles.article_id
    ORDER BY ${sort_by} ${order}`;

return db.query(query).then(({ rows }) => {
    return rows;
});}