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