Uploaded image for project: 'mod-di-converter-storage'
  1. mod-di-converter-storage
  2. MODDICONV-258

Apostrophe not escaped in input when creating field mapping profile (possible SQL injection)

    XMLWordPrintable

Details

    • Folijet Sprint 150
    • 0
    • Folijet
    • Nolana (R3 2022)
    • Users can work around this by avoiding apostrophes, of course, but that doesn't change the possible security risk. (Wayne Schneider)
    • !!!ALL!!!
    • Implementation coding issue

    Description

      Overview:
      When creating a field mapping profile using the /data-import-profiles/mappingProfiles API, an apostrophe in the profile name causes an SQL error resulting in a 500 response. The error log shows that the apostrophe is not escaped in a generated SQL query.

      Steps to Reproduce:
      Using the Okapi API, POST a new profile with an apostrophe in the name to the /data-import-profiles/mappingProfiles endpoint, e.g. profile.json

      Expected Results:
      The profile is created.

      Actual Results:
      Module responds with a 500 error and the profile is not created.

      Additional Information:
      The log shows that the apostrophe is not escaped in an SQL query, which causes an exception and raises to my mind the question of an SQL injection risk:

      15:32:43 [523677/data-import-profiles] [sim] [60863c3f-2883-458c-8c8d-daf44e79c67d] [mod_data_import_converter_storage] ERROR PostgresClient queryAndAnalyze: ERROR: syntax error at or near "s" (42601) - SELECT jsonb FROM sim_mod_data_import_converter_storage.mapping_profiles WHERE trim(both ' ' from lower(jsonb ->> 'name')) ='print book: holdings: children's lit - fiction: single' AND jsonb ->>'id'!= 'null' AND jsonb ->> 'deleted' = 'false' LIMIT 1;
      

      It seems like a profile name like '; DELETE from sim_mod_data_import_converter_storage.mapping_profiles; -- or other arbitrary SQL statements might be very destructive (though I didn't test this).

      This problem has been verified in both a Lotus environment and in the current folio-snapshot.

      URL:
      Interested parties:
      clark6

      TestRail: Results

        Attachments

          1. image-2022-10-07-13-13-30-216.png
            82 kB
            Maksym Ishchenko
          2. profile.json
            7 kB
            Wayne Schneider

          Issue Links

            Activity

              People

                Roman_Chernetskyi Roman Chernetskyi
                wayne Wayne Schneider
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases