codeql-intro-csharp

This document is intended to support CodeQL workshops and presentations; it focuses on the the section labeled 'CodeQL Running Sequence', in grids C2 through E5 of the full CodeQL and GHAS integration diagram shown here. The section 'CodeQL query development sequence, using CI artifacts', in grids H0 through J4, is a subset without database building.

There are two identifyable tracks for codeql users: devops and query writers. The first one focuses on setup, deployment, and query selection; the second on query writing. There is significant overlap; the CodeQL CLI Setup is needed by both.

CodeQL CLI Setup

After you have installed the CodeQL CLI proceed with setting up this repository:

  # Clone repository
  cd && mkdir -p work-gh && cd work-gh
  git clone https://github.com/hohn/codeql-intro-csharp.git

  # Initialize CodeQL
  cd ~/work-gh/codeql-intro-csharp
  codeql resolve  packs
  codeql pack install

Using the file qlpack.yml, this will install the packs matching this codeql version, then create codeql-pack.lock.yml which pins the version.

Setup Test Problems

Hello World Sample

  # Install sdk
  brew install --cask dotnet-sdk
  dotnet --version

  # Compile template project
  cd ~/work-gh/codeql-intro-csharp/HelloWorld/
  dotnet build

  # Run template project
  dotnet run
  # or
  ./bin/Debug/net9.0/HelloWorld

SQL Injection Sample

These are detailed steps. The next section is higher level.

  # Database Init
  cd ~/work-gh/codeql-intro-csharp/SqliDemo
  sqlite3 users.sqlite
  CREATE TABLE users (id INTEGER, info TEXT);
  .exit

  # Build
  cd ~/work-gh/codeql-intro-csharp/SqliDemo
  dotnet build

  # Run
  dotnet run
  First User

  # Check db
  echo '
      SELECT * FROM users;
  ' | sqlite3 users.sqlite 

  # Add Johnny Droptable 
  dotnet run
  Johnny'); DROP TABLE users; --

  # Check db
  echo '
      SELECT * FROM users;
  ' | sqlite3 users.sqlite 
  # Parse error near line 2: no such table: users

Build CodeQL Database

To get started, build the codeql database (adjust paths to your setup).

Build CodeQL Database with bash

  # Build the db with source commit id.
  cd $HOME/work-gh/codeql-intro-csharp
  SRCDIR=$(pwd)
  DB=$SRCDIR/csharp-sqli-$(cd $SRCDIR && git rev-parse --short HEAD)

  echo "preparing database directory $DB"
  test -d "$DB" && rm -fR "$DB"
  mkdir -p "$DB"

  # Run the build under codeql
  cd $SRCDIR && codeql database create --language=csharp -s . -j 8 -v $DB --command='./build.sh'
  # ...
  # Successfully created database at /Users/hohn/work-gh/codeql-intro-csharp/csharp-sqli-c89fbf8.

Build CodeQL Database with pwsh

  # Set the working directory
  Set-Location -Path "$HOME/work-gh/codeql-intro-csharp"

  # Get the current directory
  $SRCDIR = Get-Location

  # Build the database name using the current Git commit ID
  $CommitId = git rev-parse --short HEAD
  $DB = "$SRCDIR/csharp-sqli-$CommitId"

  # Prepare the database directory
  Write-Host "Preparing database directory $DB"
  if (Test-Path -Path $DB) {
      Remove-Item -Recurse -Force -Path $DB
  }
  New-Item -ItemType Directory -Path $DB | Out-Null

  # Run the build under CodeQL
  Write-Host "Running CodeQL database creation..."
  & codeql database create --language=csharp -s . -j 8 -v $DB --command="pwsh ./build.ps1"

Run analysis using given script and database

The bash version

  # The setup information from before
  echo $DB
  echo $SRCDIR

  # To see the help
  codeql database analyze -h

  # Run a query
  codeql database analyze                                 \
         -v                                               \
         --ram=14000                                      \
         -j12                                             \
         --rerun                                          \
         --format=sarif-latest                            \
         --output csharp-sqli.sarif                       \
         --                                               \
         $DB                                              \
         $SRCDIR/FindFunction.ql

  # optional: pretty-print
  jq . < csharp-sqli.sarif | sponge csharp-sqli.sarif

  # Examine the file in an editor
  edit csharp-sqli.sarif

The pwsh version

  # The setup information from before
  Write-Host $DB
  Write-Host $SRCDIR

  # To see the help for CodeQL database analyze
  codeql database analyze -h

  # Run a query
  & codeql database analyze `
      -v `
      --ram=14000 `
      -j12 `
      --rerun `
      --format=sarif-latest `
      --output csharp-sqli.sarif `
      -- `
      $DB `
      "$SRCDIR/FindFunction.ql"

  # Optional: pretty-print the output
  jq '.' csharp-sqli.sarif | Set-Content -Path csharp-sqli.sarif

  # Examine the file in an editor
  edit csharp-sqli.sarif

Common to All Shells

An example of using the sarif data is in the the jq script ./sarif-summary.jq. When run against the sarif input via

  # bash
  jq --raw-output --join-output  -f sarif-summary.jq < csharp-sqli.sarif > csharp-sqli.txt

  # pwsh
  jq --raw-output --join-output -f sarif-summary.jq csharp-sqli.sarif > csharp-sqli.txt

it produces output in a form close to that of compiler error messages:

  query-id: message line 
      Path
         ...

Here, that is

  csharp/intro/FindFunction: Method found [0 more]
          SqliDemo/Injectable.cs:8:
  csharp/intro/FindFunction: Method found [0 more]
          SqliDemo/Injectable.cs:17:
  csharp/intro/FindFunction: Method found [0 more]
          SqliDemo/Injectable.cs:22:
  csharp/intro/FindFunction: Method found [0 more]
          SqliDemo/Injectable.cs:47:

CodeQL for Query Writers

SQL Injection Code Sample Run

  # All run in pwsh, typical prompt is
  # PS /Users/hohn/work-gh/codeql-intro-csharp> 

  # Build
  cd $HOME/work-gh/codeql-intro-csharp
  ./build.ps1

  # Prepare db
  ./admin.ps1 -r
  ./admin.ps1 -c
  ./admin.ps1 -s

  # Add regular user interactively
  ./build.ps1
  ./SqliDemo/bin/Debug/net9.0/SqliDemo
  hello user

  # Check
  ./admin.ps1 -s

  # Add Johnny Droptable 
  ./SqliDemo/bin/Debug/net9.0/SqliDemo
  Johnny'); DROP TABLE users; --

  # And the problem:
  ./admin.ps1 -s
  Parse error near line 1: no such table: users

Identify the problem

./SqliDemo/bin/Debug/net9.0/SqliDemo is reading from STDIN, and writing to a database; looking at the code in ./SqliDemo/Injectable.cs leads to

Console.ReadLine()

for the read and

new SqliteCommand(query, connection)

for the write.

This problem is thus a dataflow or taintflow problem; in codeql terminology we have

a source at the Console.ReadLine()
a sink at the new SqliteCommand(query, connection)

We write codeql to identify these two, and then connect them via

a dataflow configuration – for this problem, the more general taintflow configuration.

Develop the query bottom-up

The main codeql library for csharp documentation is online. A table mapping C# syntax to CodeQL classes, autogenerated from those docs, is here.

Identify the source part of the
```
Console.ReadLine()?.Trim() ?? string.Empty;
```
expression, the Console.ReadLine() call. Start from a from..where..select then convert to a predicate or class. The from..where..select is found in ./SqlInjection-source.ql
Identify the sink part of the
```
var command = new SqliteCommand(query, connection))
```
expression, the query argument. Again start from from..where..select, then convert to a predicate or class. There is a subtlety here; the docs mention 'The Expr class represents all C# expressions in the program. An expression is something producing a value such as a+b or new List<int>().' Use the 'view AST' option from the results of step 1 to see what is needed here. It's not obvious. The from..where..select is found in ./SqlInjection-sink.ql

Fill in the taintflow configuration boilerplate. The documentation explains in detail. For this example, use

  module MyFlowConfiguration implements DataFlow::ConfigSig {
    predicate isSource(DataFlow::Node source) {
      ...
    }

    predicate isSink(DataFlow::Node sink) {
      ...
    }
  }

  module MyFlow = TaintTracking::Global<MyFlowConfiguration>;

  from DataFlow::Node source, DataFlow::Node sink
  where MyFlow::flow(source, sink)
  select source, "Dataflow to $@.", sink, sink.toString()

Note the different CodeQL classes used here and their connections: Node, ExprNode, ParameterNode are part of the DFG (data flow graph), Expr and Parameter are part of the AST (abstract syntax tree). Here, this means using

source.asExpr() = call

for the source and

sink.asExpr() = queryArg

for the sink.

For the completed query, see ./SqlInjection-flow-no-path.ql

Also, note that we want the flow path. So the query changes from
```
* @kind problem
```
to
```
* @kind path-problem
```
There are other changes, see ./SqlInjection-flow-with-path.ql
Try this with dataflow instead of taintflow, and notice that there are no results.

NEXT CodeQL for Devops and Administrators

codeql packs

https://docs.github.com/en/code-security/codeql-cli/using-the-advanced-functionality-of-the-codeql-cli/publishing-and-using-codeql-packs

  # Create a pack 
  cd ~/work-gh/codeql-intro-csharp
  codeql pack create -- .
  # output in 
  ls .codeql/pack/workshop/csharp-sql-injection/0.0.1/

  # Compile and Bundle
  cd ~/work-gh/codeql-intro-csharp
  codeql pack bundle                                      \
         -o csharp-sql-injection-pack.tgz                 \
         -- .

  # Get help via 
  codeql pack create -h
  codeql pack publish -h

Note the warning for FindFunction.ql. This will cause failures later in the pipeline.

  WARNING: The @id property should be a valid query identifier. (/Users/hohn/work-gh/codeql-intro-csharp/.codeql/pack/workshop/csharp-sql-injection/0.0.1/FindFunction.ql:1,1-7,4)

At the end, note

  Query pack creation complete.
  Contents directory: /Users/hohn/work-gh/codeql-intro-csharp/.codeql/pack/workshop/csharp-sql-injection/0.0.1

TODO Optional: Multiple Builds

  dotnet sln codeql-intro-csharp.sln list
  dotnet build codeql-intro-csharp.sln

Languages

HTML 50.2%

CodeQL 19.1%

Shell 12.2%

PowerShell 10.1%

C# 8.4%

README.org Unescape Escape

Introduction to CodeQL