Zenn
🔎

OPAを使用したSQLの判定

2022/05/24に公開
Rust
SQL
Rego
OPA
tech

初めに

SQLを目視で確認するのが面倒くさいので、OPAを使用して本番環境とかで実行したくないSQL（例えばWHERE句を指定していないDELETE文）を判定できないか、試してみました。

実装内容

今回はRustで作成しました。
処理の概要は以下の通りです。

SQLのパース

まずOPAで判定したいSQLを以下のコードでパースします。
パースにはsqlparser-rsを使用しました。
https://github.com/sqlparser-rs/sqlparser-rs

fn parse(contents: String) -> Vec<ParseResult> {
    let dialect = GenericDialect {};
    let ast_list = SqlParser::parse_sql(&dialect, contents.as_str()).unwrap();

    // パースするSQLとパース結果をまとめる
    let mut result: Vec<ParseResult> = Vec::new();
    for ast in ast_list.iter() {
        result.push(ParseResult {
            query: ast.to_string(),
            ast: ast.clone(),
        });
    }

    // 返却する値の例
    // [{"query":"DELETE FROM test","ast":{"Delete":{"table_name":[{"value":"test","quote_style":null}],"selection":null}}}]
    result
}

OPAサーバーへのクエリ

次にパースした結果をJSON形式にし、hyperを使ってOPAのサーバーに渡します。
https://github.com/hyperium/hyper

async fn opa_request(uri: String, input: Vec<ParseResult>) -> OpaResponse {
    let client = Client::new();

    let req_body = serde_json::to_string(&OpaRequest { input }).unwrap();

    let req = Request::builder()
        .method(Method::POST)
        .uri(uri)
        .header("Content-Type", "application/json")
        .body(Body::from(req_body))
        .unwrap();

    let res = client.request(req).await.unwrap();
    if res.status() != StatusCode::OK {
        panic!("Responses other than HTTP code 200: {}", res.status());
    }

    // レスポンスボディの取得
    let bytes = body::to_bytes(res.into_body()).await.unwrap();
    let res_body = String::from_utf8(bytes.to_vec()).unwrap();

    // レスポンスボディをデシリアライズ
    // 返却する値の例
    // {"result":{"deny":["DELETE FROM test: WHERE句がないDELETE文"]}}
    serde_json::from_str(&res_body).unwrap()
}

判定結果の出力

最後に、OPAからの判定結果にdenyの結果があれば内容を出力します。

#[tokio::main]
async fn main() {

    // その他処理は省略

    if json.result.deny.is_empty() {
        println!("There is no problem with SQL");
    } else {
        for deny in json.result.deny.iter() {
            println!("{}", deny);
        }
    }
}

完成したものの動作

完成したものはこちらになります。
https://github.com/takenoko-gohan/check-sql-with-opa

ポリシー

OPAサーバーで判定するためのregoファイルを用意します。
今回はDELETE文とUPDATE文にWHERE句が存在するか判定するポリシーを作成します。

WHERE句がないDELETE文とWHERE句があるDELTE文のパース結果は以下になります。

WHERE句がないDELETE文のパース結果
[
  {
    "query":"DELETE FROM tbl",
    "ast":{
      "Delete":{
        "table_name":[
          {
            "value":"tbl",
            "quote_style":null
          }
        ],
        "selection":null
      }
    }
  }
]
WHERE句があるDELETE文のパース結果
[
  {
    "query":"DELETE FROM tbl WHERE id = 1",
    "ast":{
      "Delete":{
        "table_name":[
          {
            "value":"tbl",
            "quote_style":null
          }
        ],
        "selection":{
          "BinaryOp":{
            "left":{
              "Identifier":{
                "value":"id",
                "quote_style":null
              }
            },
            "op":"Eq",
            "right":{
              "Value":{
                "Number":[
                  "1",
                  false
                ]
              }
            }
          }
        }
      }
    }
  }
]

WHERE句がない場合、ast.Delete.selectionがnullになります。
なので、配列の要素ごとにast.Delete.selectionがnullかどうか判定するポリシーを作成します。

deny[msg] {
  sql := input[_]
  sql.ast.Delete.selection == null
  msg := sprintf("%s: WHERE句がないDELETE文", [sql.query])
}

今回は下記のregoファイルになりました。
UPDATE文のポリシーもDELTE文と同様でast.Update.selectionがnullの場合はWHERE句がないと判定します。

policies/bad_sql.rego
package bad_sql

deny[msg] {
  sql := input[_]
  sql.ast.Delete.selection == null
  msg := sprintf("%s: WHERE句がないDELETE文", [sql.query])
}

deny[msg] {
  sql := input[_]
  sql.ast.Update.selection == null
  msg := sprintf("%s: WHERE句がないUPDATE文", [sql.query])
}

OPAサーバーの立ち上げ

docker-compose.ymlでローカルにOPAサーバーを立ち上げます。

docker-compose.yml
version: "3.8"

services:
  opa:
    image: openpolicyagent/opa:latest-rootless
    ports:
      - "8181:8181"
    command:
      - "run"
      - "--server"
      - "--log-format=json-pretty"
      - "--set=decision_logs.console=true"
      - "policies"
    volumes:
      - ./policies:/workspace/policies:ro
    working_dir: /workspace

コンテナの立ち上げ

docker compose up -d

SQLの判定

コンテナ立ち上げ後、適当なSQLファイルを作成して、作成したコマンドとOPAサーバーで判定します。

# SQLファイルの作成
echo "SELECT * FROM tbl WHERE id = 1;
UPDATE tbl SET price = 100;
DELETE FROM tbl;" > .\test.sql

# SQLの判定
cargo run -- -f .\test.sql

# 実行結果
#    Finished dev [unoptimized + debuginfo] target(s) in 1.22s
#     Running `target\debug\check-sql-with-opa.exe -f .\test.sql`
#DELETE FROM tbl: WHERE句がないDELETE文
#UPDATE tbl SET price = 100: WHERE句がないUPDATE文

最後に

思いつきで作成しましたが、本番で実行するSQLの確認やマイグレーションツールのSQLファイル確認に使えるかもしれません。

Discussion