Ensure JOOQ is serializing fields `sender_ip` and `external_name` in a safe manner and that said values are valid.

[jack-h-wang]

User Story

As ReportStream, I want inputs from external sources sanitized so that I can be certain data is valid and does not pose a security concern.

Description/Use Case

As a best practice, we should ensure that all database access is sanitized so that unintended consequences cannot occur. We should look at both incoming data when performing writes as well as the methods by which the database is accessed when processing API calls.

Risks/Impacts/Considerations

Dev Notes

Some examples that may need to be addressed:

• action.sender_ip is in some cases taken from HttpRequestMessage headers. We could attempt to validate the headers contain valid IP addresses.
• report_file.external_name is in some cases taken in by the submission endpoint as payloadName. We could attempt to ensure this name does not contain invalid characters.
• Ensure database access is strictly via jOOQ using methods that do not introduce SQL injection vulnerabilities. See jOOQ documentation

Acceptance Criteria

• [ ] The serialization of sender_ip to the database from submissions is being serialized by JOOQ safely. If this is not the case refactor so we're using JOOQ safely.
• [ ] prior to serialization ensure sender_ip is a valid ip address (commons has a ready bake way to do this). If validation fails ~reject operation on error~ don't set the ip.
• [ ] The serialization of external_name to the database from submissions is being serialized by JOOQ safely. If this is not the case refactor so we're using JOOQ safely.
• ~prior to serializtion of external_name to the database ensure the value is a valid unix/windows filename. You should be able to use one of the implementations of Path to check for valid filename w/o touching the actual file system. If validation fails reject operation on error~
• [ ] Unit test ensuring malicious input to those fields is sanitized before it lands in the db

[Andrey-Glazkv]

Hey team! Please add your planning poker estimate with Zenhub @adegolier @arnejduranovic @brick-green @david-navapbc @jack-h-wang @jalbinson @JFisk42 @mkalish @thetaurean

[david-navapbc]

This ticket has been updated since it was groomed. The volume of work is greater than was originally pointed.

[mkalish]

I've dropped the 4th ac and the payloadname currently has no expectations for it to be valid filename (in fact it typically would not have an extension)

I've also adjust the sender ip ac since it does not make sense to reject a report if there is an IP issue as the sender wont' have a remediation

[mkalish]

Taking a look at the SQL injection angle:

• I found no usages org.jooq.PlainSQL
• We use the codegen capabilities which the documentation states that jooq will use prepared statements with bound params