Use parameterized queries to reduce risk of SQL Injection

[maxachis]

Context

Currently, SQL code in the repository uses string formatting to insert parameters into queries. For example, on quick_search_query.py :

    SELECT
        data_sources.airtable_uid,
        data_sources.name AS data_source_name,
        data_sources.description,
        data_sources.record_type,
        data_sources.source_url,
        data_sources.record_format,
        data_sources.coverage_start,
        data_sources.coverage_end,
        data_sources.agency_supplied,
        agencies.name AS agency_name,
        agencies.municipality,
        agencies.state_iso
    FROM
        agency_source_link
    INNER JOIN
        data_sources ON agency_source_link.airtable_uid = data_sources.airtable_uid
    INNER JOIN
        agencies ON agency_source_link.agency_described_linked_uid = agencies.airtable_uid
    INNER JOIN
        state_names ON agencies.state_iso = state_names.state_iso
    WHERE
        (data_sources.name LIKE '%{0}%' OR data_sources.description LIKE '%{0}%' OR data_sources.record_type LIKE '%{0}%' OR data_sources.tags LIKE '%{0}%') 
        AND (agencies.county_name LIKE '%{1}%' OR substr(agencies.county_name,3,length(agencies.county_name)-4) || ' county' LIKE '%{1}%' 
            OR agencies.state_iso LIKE '%{1}%' OR agencies.municipality LIKE '%{1}%' OR agencies.agency_type LIKE '%{1}%' OR agencies.jurisdiction_type LIKE '%{1}%' 
            OR agencies.name LIKE '%{1}%' OR state_names.state_name LIKE '%{1}%')
        AND data_sources.approval_status = 'approved'
        AND data_sources.url_status not in ('broken', 'none found')

Direct insertions pose a risk for SQL injection attacks.

A solution to this, compatible with psycopg2, is parameterized queries, which will enable these to be inserted without introducing security vulnerabilities.

Requirements

• [x] some queries refactored: https://github.com/Police-Data-Accessibility-Project/data-sources-app-v2/pull/18
• [ ] quick_search_query.py and security.py will need to be addressed in separate PRs.
• [ ] data_sources.py include string interpolation in SELECT clause, which will need to be separately handled, possibly as part of a larger refactor

[mbodeantor]

Just need to make sure it doesn't break tests, I had issues with pytest using parameterized queries before

[maxachis]

With the backend transformations and the addition of security linting, I think we can consider this issue reasonably resolved. We can reopen this if the issue occurs again.