Database Requirements

[abakst]

Document the requirements for the Database so that we know when it is "done."

The completed document should at least imply an answer to:

• What resources are required in addition to the database to constitute a valid FETT Target? Is it just SSH access + permissions to execute the sqlite binary?
• Do we need to generate a sample database file somewhere (home directory or system-wide)?
• What additional configuration do we need to expose the known CVEs?

This should also be driven by the requirements of the FETT-Voting app (cc @dmzimmerman for awareness)

Effort: 1 hour

[abakst]

@dragan01 I will be writing requirements for the Web Server in Lando (@dmzimmerman has some nice examples here https://github.com/DARPA-SSITH-Demonstrators/SSITH-FETT-Voting and I'm sure you remember the SBB requirements i.e. https://github.com/GaloisInc/BESSPIN-Voting-System-Demonstrator-2019/blob/master/specs/lando/sbb/requirements.lando).

[dmzimmerman]

(note that people keep tagging the poor guy who has "dmz" on GitHub instead of me, so I've been editing them where I can)

[dragansts]

@abakst I am not able to clone your repo because you have restricted access - private mode repo.

[dragansts]

As per CVE/CWE spreadsheet provided , here are basic SQLite database requirements written in lando format: This file has not been completed yet

requirements database SQLite

Sqlite_installation_versions For the CVE/CWE tests purpose only, provide installation and set-up of the following SQLite database versions: 3.22.0, 3.25.3, 3.27.2, 3.29.0 and 3.30.1.

Adjust_sqlite_memory_allocation Adjust how SQLite does memory allocation, including setting up alternative memory allocators appropriate for safety-critical real-time embedded systems and application-defined memory allocators.

Application_defined_page_cache Specify an application-defined page cache.

Sqlite_error_log Set up a process-wide error log.

Sqlite_db_connection Configure and provide limit for individual database connections

Sqlite_access_and_permission Provide ssh access and login without password by using authentication keys mechanism and grant permissions to corresponding users in order to be able to execute the sqlite binary.

Sqlite_fts5_virtual_table Provide fts5 for sqite3 version >= SQLite 3.27.2. Note that fts5 is an experimental fts extension added in SQLite. The principle difference between FTS3/4 and FTS5 is that in FTS3/4, each instance-list is stored as a single large database record, whereas in FTS5 large instance-lists are divided between multiple database records.

Sqlite_via_WebSQL_in_Google_Chrome Html5 has implementation of databases and the implementation is done by embedding SqLite database into browser. Browser's like Google Chrome and Safari have their implementation of Html5 databases implemented using the SQLite termed as websql.

Sqlite_fts3_virtual_table Provide fts3 for sqite3 version < SQLite 3.25.3.

Sqlite_faxfinder Install Multi-Tech FaxFinder version < 5.1.6

[kiniry]

@dragansts you should be able to clone all repos under this private SSITH organization.

[dragansts]

As an addition I have provided a bit low level of OTA SQLite spec if we need the SQLite3 behind the OTA service,

requirements OTA SQLite database

Ota_update_database An "OTA update" is stored as a separate SQLite database. A database containing an OTA update is an "OTA database"

Ota_database_table_name For each table in the target database to be updated, the OTA database should contain a table named "data_" containing the same set of columns as the target table, and one more - "ota_control"

Ota_database_table_columns The data_% table should have no PRIMARY KEY or UNIQUE constraints, but each column should have the same type as the corresponding column in the target database. The "ota_control" column should have no type at all.

Ota_database_table_columns_order The order of the columns in the data_% table does not matter.

Ota_database_virtual_table If the target database table is a virtual table or a table that has no PRIMARY KEY declaration, the data_% table must also contain a column named "ota_rowid". This column is mapped to the tables implicit primary key column - "rowid". Virtual tables for which the "rowid" column does not function like a primary key value cannot be updated using OTA.

Ota_database_table_with_non_hidden_columns A non-hidden columns ( columns matched by select *) of the target table must be present in the input table.

Ota_database_virtual_table_with_non_hidden_columns For virtual tables, hidden columns are optional - they are updated by OTA if present in the input table, or not otherwise.

Ota_database_insert_row For each row to INSERT into the target database as part of the OTA update, the corresponding data_% table should contain a single record with the "ota_control" column set to contain integer value 0. The other columns should be set to the values that make up the new record to insert.

Ota_database_delete_row For each row to DELETE from the target database as part of the OTA update, the corresponding data_% table should contain a single record with the "otacontrol" column set to contain integer value 1. The real primary key values of the row to delete should be stored in the corresponding columns of the data% table. The values stored in the other columns are not used.

Ota_database_update_row For each row to UPDATE from the target database as part of the OTA update, the corresponding data_% table should contain a single record with the "otacontrol" column set to contain a value of type text. The real primary key values identifying the row to update should be stored in the corresponding columns of the data% table row, as should the new values of all columns being update. The text value in the "ota_control" column must contain the same number of characters as there are columns in the target database table, and must consist entirely 'd'.

Ota_database_update_row_ota_delta Instead of updating the target table with the value stored in the corresponding data_% column, the user-defined SQL function "ota_delta()" is invoked and the result stored in the target table column. The ota_delta() is invoked with two arguments - the original value currently stored in the target table column and the value specified in the data_xxx table.

[dragansts]

requirements OTA SQLite API usage

Ota_open_handle Opens an OTA handle using the sqlite3ota_open() function

Ota_registration Registers any required virtual table modules with the database handle returned by sqlite3ota_db(). Also, register the ota_delta() implementation.

Ota_steps_repetitive Calls the sqlite3ota_step() function one or more times on the new handle. Each call to sqlite3ota_step() performs a single operation, so lots of calls may be required to apply a complete update.

Ota_close_handle_partially_applied Calls sqlite3ota_close() to close the OTA update handle. If sqlite3ota_step() has been called enough times to completely apply the update to the target database, then the OTA database is marked as fully applied. Otherwise, If an update is only partially applied to the target database by the time sqlite3ota_close() is called, various state information is saved within the OTA database. This allows subsequent processes to automatically resume the OTA update from where it left off.

Ota_close_handle_fully_applied If the OTA update has been completely applied, mark the OTA database as fully applied. Otherwise, assuming no error has occurred, save the current state of the OTA update application to the OTA database.

Ota_open_step_handle_error_occurred If an error has already occurred as part of an sqlite3ota_step() or sqlite3ota_open() call, or if one occurs within this function, an SQLite error code is returned. Additionally, *pzErrmsg may be set to point to a buffer containing a utf-8 formatted English language error message. It is the responsibility of the caller to eventually free any such buffer using sqlite3_free().

Ota_handle_operation_status If no error occurs, this function returns SQLITE_OK if the update has been partially applied, or SQLITE_DONE if it has been completely applied.

Ota_progress_statistic Return the total number of key-value operations (inserts, deletes or updates) that have been performed on the target database since the current OTA update was started.

Remove_all_ota_extension To remove all OTA extension state information, returning an OTA database to its original contents, it is sufficient to drop all tables that begin with the prefix "ota_"

[dragansts]

Note that: In case we don't need SQLite behind the OTA service, someone else who has been working with OTA spec can be free to take anything useful and valuable from the spec above.

[dragansts]

@kiniry It failed 3 times , perhaps someone should grant permission for me ? Not sure what I am doing wrong.

gajaka@Dragans-MacBook-Pro % git clone https://github.com/DARPA-SSITH-Demonstrators/SSITH-FETT-Target.git Cloning into 'SSITH-FETT-Target'... remote: Repository not found. fatal: repository 'https://github.com/DARPA-SSITH-Demonstrators/SSITH-FETT-Target.git/' not found

[dragansts]

@abakst, @rtadros-Galois , @dmz and @kiniry please add/suggest anything I missed in the spec ( one on top of the hub )

[dragansts]

Questions and notes: This will be updated.

1) Do we need clone of the SQLIte database? 2) Do I have to provide spec for an interface ( perhaps basic header file ) with the following functions : open connection, register handle, insert raw, delete raw, update raw, close connection ? 3) SQLite binary path destination should be specified. 4) Database port should be specified. 5) Installation script should be provided. 6) Installation script should create SQLite database instance. 7) Installation script should create database schema and/or database tables with initial load if needed. 8) Initial tests should be provided.

[dragansts]

Architecture specific: This will be updated.

[kiniry]

We need no medium to low level specs for anything but FETT-Voting. Coordinate with subsystem leads before writing additional specs please.

On Apr 18, 2020, at 12:14 PM, dragansts notifications@github.com wrote:

 Do we need to clone database? Do I have to provide spec for an interface ( perhaps basic header file ) with the folowing functions : open connection, register handle, insert raw, delete raw, update raw, close connedction ? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[kiniry]

We will look into permissions for you. I’m at the supermarket now, so it won’t be me for the moment. ;)

On Apr 18, 2020, at 11:53 AM, dragansts notifications@github.com wrote:

 @kiniry It failed 3 times , perhaps someone should grant permission for me ? Not sure what I am doing wrong.

gajaka@Dragans-MacBook-Pro testgen % git clone https://github.com/DARPA-SSITH-Demonstrators/SSITH-FETT-Target.git Cloning into 'SSITH-FETT-Target'... remote: Repository not found. fatal: repository 'https://github.com/DARPA-SSITH-Demonstrators/SSITH-FETT-Target.git/' not found

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.

[abakst]

Regarding the SQLite versions, we should only be using a single versio 3.22.0, so we should only document those requirements. If some CVEs require a higher version, then please indicate that on the spreadsheet.

[abakst]

Whoops, didn't mean to close :)

[abakst]

• QLite binary path destination should be specified.
• Database port should be specified.
• Installation script should be provided.

These probably don't merit specification here -- once the FETT-Target tool is implemented, there should be instructions on how to integrate a build of sqlite either in the target repository or in the OS image.

• Installation script should create SQLite database instance.
• Installation script should create database schema and/or database tables with initial load if needed.

I'm not sure these are necessary. Since the user (redteam researcher) will have permission to the sqlite binary, they can create whatever database they'd like to try and find vulnerabilities.

• Initial tests should be provided.

Indeed, but again we should tailor this to making sure that the Target environment is set up correctly and not much else. This should be done in accordance with #62 .

[dragansts]

@abakst Ok I have updated spreadsheet. Please see comments in column B. Also I have pushed lando file in my own branch db_requirement_docs. Anything else ? Can we close ticket ?

[abakst]

Open a pull request. You'll need reviewers for the specification before you can merge it (I suggest more than 1). When the PR is merged you can close this one.

[dragansts]

I have opened pull request #78

[abakst]

Closing as #78 was merged