node.jspostgresqlsql-injectionnode-orm2

Sql injection protection when sending json as a sql function parameter


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
      }')`,

Solution

  • 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().