Sql injection protection when sending json as a sql function parameter

Question

I am sending a json array with filter parameters from ExtJs in the sql function parameter.

filters look like this:

[{"field":"product_type_id","data":{"type":"number","value":"43","comparison":"in"}},{"field":"code","data":{"type":"string","value":"RRR","comparison":"like"}}]

I would like to protect myself against possible SQL injection, but my sanitizeString function removes all characters such as {, ", : etc. This prevents me from sending json, so I cannot use it for parameters. Can you tell me how to best protect myself through sql injection and at the same time pass json in the parameter without any problems?

// this.DBModels?.execQuery
 execQuery<T = any>(query: string, callback: (err: Error, data: T[]) => void) {
    this.db.driver.execQuery(query, (err: Error, data: T[]): void => {
      callback(err, data)
    })
  }

   this.DBModels?.execQuery(
      `SELECT * FROM ${sanitizeString(functionName)}('${
        functionParameter
      }')`,

I'm not sure I understand this question. Surely your database library is handling parameter escaping for you? If not, get a better database library. — Richard Huxton
– Richard Huxton, Commented Oct 9 at 16:14
@RichardHuxton I use the orm library, but as you can see in the part of the code, I provide a query which I concatenate as a whole string. I guess it's not sanitized by the library? — Pawel
– Pawel, Commented Oct 9 at 17:50
@Pawel You mean the no longer actively maintained node-orm2 you get from npm install orm? If you have control or influence over this, consider a switch. Not much point discussing security issues concerning a library that saw its last patch 9 years ago. — Zegarek
– Zegarek, Commented Oct 9 at 18:04

Zegarek · Accepted Answer · 2025-10-09 17:15:50Z

4

Sanitize the function name and bind the json variable as query parameter. Using an example from node-postgres:

const queryBody = 'SELECT * FROM ${sanitizeString(functionName)}($1)'
const functionParameter = [{"field":"product_type_id","data":{"type":"number","value":"43","comparison":"in"}},{"field":"code","data":{"type":"string","value":"RRR","comparison":"like"}}]
await client.query(queryBody, [functionParameter])

That way the query gets sent, parsed and validated as SQL on its own, the param sent separately and validated as a db-side json-type constant before being made available to the executor of the actual query. At no point does the db even consider parsing that value as a part of the SQL statement.

Note that if the db-side function uses dynamic SQL, you need to make sure it also binds the param it receives with USING, an %L placeholder in a format() or sanitizes it with quote_literal().

edited Oct 9 at 17:15

answered Oct 9 at 17:10

Zegarek

29.9k5 gold badges27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Charles Candy · Accepted Answer · 2025-10-10 21:02:54Z

-1

for node mariadb

multipleStatements: false

answered Oct 10 at 21:02

Charles Candy

597 bronze badges

1 Comment

NotTheDr01ds Oct 10 at 23:30

Keep in mind that this question was specifically about PostgreSQL, and answers should stay on-topic. If you'd like (and if there's not a duplicate answer already), you can create a new question regarding how to do this in MariaDb and then self-answer it.

Collectives™ on Stack Overflow

Sql injection protection when sending json as a sql function parameter

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related