Sanitizing JSON objects in SQL queries

[hoffmanilya]

When using Postgres 10/Rails 5, given an INSERT statement as so:

INSERT INTO foos(foo_id, bar_id, external_id, email_address, created_at, updated_at)
    SELECT 123, 456, external_id, email_address, NOW(), NOW()
    FROM jsonb_to_recordset($${"items":[{"external_id":1234,"email_address":"test@domain.com"}]}$$::jsonb->'items')
        AS t(external_id integer, email_address varchar)

The trace that is sent to Scout does not sanitize the JSON object properly. This is what I see as the SQL in Scout:

INSERT INTO foos(foo_id, bar_id, external_id, email_address, created_at, updated_at) 
    SELECT ?, ?, external_id, email_address, NOW(), NOW() 
    FROM jsonb_to_recordset($${"items":[{"external_id":"?","email_address":"test@domain.com"}]}$$::jsonb->'items') 
    AS t(external_id integer, email_address varchar)

Notice that the value of the external_id key in the JSON is sanitized but the value of the email_address key is not. Is there a configuration setting to enable sanitization of all parameters in the query?

[cschneid]

Thank you for the excellent report. Unfortunately there's no additional config to tweak the sql sanitization.

Is the json object inside jsonb_to_recordset being passed as a bind parameter, or is it in the SQL directly?

I'll get this added to the sanitization code either way.

[hoffmanilya]

@cschneid thanks for the quick reply! In this case, the JSON object is directly inside the SQL.

[dlanderson]

Closed in #431 - pending gem release.