Using SQLite

How To

Summary

This tutorial will outline how you can use SQLite3 in your QRadar App and follow best practices. QRadar Apps have built in support for SQLite - allowing SQLite to be used without requiring installation of extra packages.

This tutorial uses a JSON configuration file to set Flask configuration options, while also using this Flask configuration to store database configuration (such as the DB name).

Environment

Prerequisites

QRadar App SDK v2
Docker

Steps

Create the App

Create a new directory for your app:

mkdir SQLiteApp && cd SQLiteApp

Use the QRadar App SDK to initialise the app code:

qapp create

Write the Manifest

Open the manifest.json file to edit some values to make it more relevant to the app, making it look like this:

{
  "name": "Sqlite Storage App",
  "description": "Save and read data from an sqlite database using an html form",
  "version": "1.0.0",
  "image": "qradar-app-base:2.0.0",
  "areas": [
    {
      "id": "SqliteStorageTab",
      "text": "SqliteStorage",
      "description": "Tab with html form to save data to database",
      "url": "index",
      "required_capabilities": []
    }
  ],
  "uuid": "<your unique app UUID>"
}

Set up the SQLite Database at App Startup

The SQLite database needs configured and set up at startup.

This startup set up of the database should be repeatable, as apps can be stopped and started and the /opt/app-root/store directory is persisted. Startup scripts should account for this.

Writing the Database SQL Schema

Create the directories for holding app SQL schemas from the top-level directory of your app workspace:

mkdir -p container/conf && mkdir -p container/conf/db

Create a new SQL file called schema.sql inside container/conf/db:

CREATE TABLE IF NOT EXISTS entries (
  id integer primary key autoincrement,
  title text not null
);

This SQL schema will create a new simple table entries if it does not yet exist, containing just an ID and title per row.

Please note, if you are maintaining SQL that should work across app updates and schema changes, consider using a SQL version control system, such as FlywayDB or golang-migrate.

Database and Flask configuration

Create a new JSON configuration file that will hold some configuration values that will be loaded into Flask at startup, and made available at runtime. Create a new file config.json inside container/conf:

{
  "DEBUG": false,
  "DB_NAME": "mystore"
}

This will load two keys into Flask:

DEBUG = false - Run Flask in non-debug mode.
DB_NAME = mystore - The name of the DB the app uses, can be accessed at runtime.

Startup Script to Set Up Database Directory

Create the directory for holding app startup scripts from the top-level directory of your app workspace:

mkdir -p container/run

At startup if no database directory exists it should be created. This directory inside the /opt/app-root/store directory will contain the SQLite DB files. Create a new script called startup.sh inside container/run:

#!/bin/bash

mkdir -p "${APP_ROOT}"/store/db

The app needs to know what script it should run at startup, so create a new ordering.txt file inside container/run that simply points to the path of the startup script:

/opt/app-root/container/run/startup.sh

Create Interface with the Database

Create the directory for holding the database interface code from the top-level directory of your app workspace:

mkdir -p app/db

Now create some helper methods for interacting with the database, handling database creation, connection, and executing SQL schema files. Create a file called database.py inside the directory app/db:

import os
import sqlite3
from contextlib import closing
from qpylib import qpylib

DB_STORAGE_PATH = qpylib.get_store_path('db')


# Create the database specified in the parameters provided
def create_db(db_name):
    get_db_connection(db_name)


# Execute the specified sql file against the database in the parameters provided
def execute_schema_sql(db_name, schema_file_path):
    conn = get_db_connection(db_name)
    with conn:
        with open(schema_file_path, mode='r') as schema_file:
            cur = conn.cursor()
            with closing(cur):
                cur.executescript(schema_file.read())
                conn.commit()


# Get db connection to sqlite database using the parameter provided
def get_db_connection(db_name):
    db_path = os.path.join(DB_STORAGE_PATH, db_name)
    conn = sqlite3.connect(db_path)
    return conn

Create the Python Initialisation Code

Edit the Python initialisation code in app/__init__.py to include loading the custom configuration and initialising the SQLite database by executing the SQL schema:

__author__ = 'IBM'

import json
from .db.database import create_db, execute_schema_sql
from flask import Flask
from qpylib import qpylib


# Flask application factory.
def create_app():
    # Create a Flask instance.
    qflask = Flask(__name__)

    # Retrieve QRadar app id.
    qradar_app_id = qpylib.get_app_id()

    # Create unique session cookie name for this app.
    qflask.config['SESSION_COOKIE_NAME'] = 'session_{0}'.format(qradar_app_id)

    # Initialize database settings and flask configuration options via json file
    with open(qpylib.get_root_path(
            "container/conf/config.json")) as config_json_file:
        config_json = json.load(config_json_file)

    qflask.config.update(config_json)

    # Hide server details in endpoint responses.
    # pylint: disable=unused-variable
    @qflask.after_request
    def obscure_server_header(resp):
        resp.headers['Server'] = 'QRadar App {0}'.format(qradar_app_id)
        return resp

    # Register q_url_for function for use with Jinja2 templates.
    qflask.add_template_global(qpylib.q_url_for, 'q_url_for')

    # Initialize logging.
    qpylib.create_log()

    # To enable app health checking, the QRadar App Framework
    # requires every Flask app to define a /debug endpoint.
    # The endpoint function should contain a trivial implementation
    # that returns a simple confirmation response message.
    @qflask.route('/debug')
    def debug():
        return 'Pong!'

    # Import additional endpoints.
    # For more information see:
    #   https://flask.palletsprojects.com/en/1.1.x/tutorial/views
    from . import views
    qflask.register_blueprint(views.viewsbp)

    # create db by loading schema
    db_name = qflask.config["DB_NAME"]
    schema_file_path = qpylib.get_root_path("container/conf/db/schema.sql")
    create_db(db_name)
    execute_schema_sql(db_name, schema_file_path)

    return qflask

There are two important additions to the __init__.py file above:

# Initialize database settings and flask configuration options via json file
with open(qpylib.get_root_path(
        "container/conf/config.json")) as config_json_file:
    config_json = json.load(config_json_file)

qflask.config.update(config_json)

This loads the config.json into Flask, allowing these values to be retrieved at runtime.

# create db by loading schema
db_name = qflask.config["DB_NAME"]
schema_file_path = qpylib.get_root_path("container/conf/db/schema.sql")
create_db(db_name)
execute_schema_sql(db_name, schema_file_path)

This creates the database by executing the schema.sql defined above.

Create the App Endpoints

Update app/views.py to add two new endpoints for serving the app UI and adding values to the app database:

from contextlib import closing
from flask import Blueprint, current_app, g, redirect, render_template, request, url_for
from .db.database import get_db_connection

# pylint: disable=invalid-name
viewsbp = Blueprint('viewsbp', __name__, url_prefix='/')


# get a db connection before request
def before_request():
    # Retrieve database settings from application configuration
    db_name = current_app.config["DB_NAME"]
    g.conn = get_db_connection(db_name)


# close db connection after request
def after_request(response):
    if g.conn is not None:
        g.conn.close()
    return response


viewsbp.before_request(before_request)
viewsbp.after_request(after_request)


@viewsbp.route('/')
@viewsbp.route('/index')
def show_entries():
    cur = g.conn.cursor()
    with closing(cur):
        cur.execute('SELECT TITLE FROM entries ORDER BY id DESC')
        entries = [dict(title=row[0],) for row in cur.fetchall()]
    return render_template('hello.html', entries=entries)


@viewsbp.route('/add_entry', methods=['POST'])
def add_entry():
    cur = g.conn.cursor()
    with closing(cur):
        insert_query = 'INSERT INTO entries (title) VALUES (?)'
        cur.execute(insert_query, (request.form['title'],))
        g.conn.commit()
    return redirect(url_for('viewsbp.show_entries'), code=303)

Write the App HTML

Update app/templates/hello.html to present both a list of stored entries, and a form for submitting a new entry:

<!DOCTYPE html>
<title>Sqlite Storage App</title>
<link rel="stylesheet" type="text/css" href="static/styles.css">
<div class="page">
    <h1>SQLite Storage App</h1>
    <ul class="entries">
        {% for entry in entries %}
        <li><h2>{{ entry.title }}</h2>
        {% else %}
        <li><em>No entries.</em>
        {% endfor %}
    </ul>
    <form action="add_entry" method="post" class="add-entry">
        <div class="row">
            <div class="right-col">
            <input type="text" id="title" name="title" placeholder="Title..">
            </div>
        </div>
        <br/>
        <div class="row">
            <input type="submit" value="Save">
        </div>
        <br/>
    </form>
</div>

Run the App/Package the App

The app can then be run locally with:

qapp run

Or packaged and deployed with:

qapp package -p <app zip name>

qapp deploy -p <app zip name> -q <qradar console> -q <qradar user>

Document Location

Worldwide

[{"Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSBQAC","label":"IBM Security QRadar SIEM"},"ARM Category":[{"code":"a8m0z000000cwt3AAA","label":"QRadar Apps"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Tips