Is this parameterized query open to SQL injection?-CodePudding

Someone looking at my code said that the SQL query below (SELECT * FROM...) was obviously open to attack. I have researched this and it seems I'm doing this correctly by using a parameterized query, but clearly I'm missing something.

app.get("/api/v1/:userId", async (req, res) => {
    try {
        const teammate = await db.query("SELECT * FROM teammates WHERE uid = $1", [
            req.params.userId,
        ]);

CodePudding user response：

This query is not open to SQL injection, because it uses a parameterized query. The data is not substituted for the parameter ($1), but sent separately in a “bind” message, so no matter what the data contain, it is not interpreted as part of the SQL statement.

Moreover, it looks like the argument is an integer, and SQL injection can only happen with string arguments.

CodePudding user response：

Someone at that company may have assumed that the $1 was going to be used for string interpolation, instead of a SQL query parameter.

They might not be aware that PostgreSQL uses the dollar-sign sigil for a query parameter placeholder. Other databases like MySQL use ? for a placeholder, and Oracle uses a : sigil in front of a named parameter.

You wrote the code correctly. See a similar example for node.js and PostgreSQL here: https://node-postgres.com/features/queries The section on "Parameterized query" specifically shows that style of code as the defense against SQL injection.

You might be better off not getting that job, in retrospect.