CodeQL tailoring

One size does not always fit all

Based on the work by @theztefan, @lcartey, @rvermeulen

Prerequisites • Resources • Learning Objectives

• Who is this for: Security Engineers, Security Researchers, Developers.
• What you'll learn: Learn how to use CodeQL for code exploration and for finding security issues.
• What you'll build: Build a CodeQL query based on a security advisory to find a SQL injection.

Learning Objectives

In this workshop will cover the following learning objectives:

• Understand how to use CodeQL in exploration and identification of security vulnerabilities.
• Be able to codify a security vulnerability as a CodeQL query.
• Be able to refine queries to find variants and increase precision.
• Understand how refined queries can be integrated into the developer workflow to prevent future vulnerabilities.

📣 Prerequisites

Before joining the workshop, there are a few items that you will need to install or bring with you.

• Install Visual Studio Code.

• Install the CodeQL extension.

  Walkthrough

  

• Install the required CodeQL pack dependencies by running the command CodeQL: Install pack dependencies to install the dependencies for the pack grehackworkshop/sql-injection-queries.

  Walkthrough

  

• Install git LFS to download the prepared databases or build the databases locally using the provide Make file. The Makefile requires the presence of Docker.

• Test your setup perform the steps:

  1. Right-click on the file xwiki-platform-ratings-api-12.8-db.zip and run the command CodeQL: Set Current Database.
  2. Right-click on the file SqlInjection.ql and run the command CodeQL: Run Queries in Selected Files.
  3. See the result Hello GreHack! in the CodeQL Query Results pane.

  If you run into issues with your setup feel free to ask for support at the start of the workshop.

  Walkthrough

  

After finishing the technical prerequisites consider the following tutorials/guides for basic understanding of QL and Java query writing:

• QL tutorials
• Basic query for Java code
• QL classes

📚 Resources

• QL tutorials
• CodeQL for Java language guide
• CodeQL documentation
• SQL injection
• QL language reference
• CodeQL library for Java

Workshop

Welcome to the workshop CodeQL tailoring - One size does not always fit all! In this workshop we will apply CodeQL to gain a better understanding of a security issues reported in a security advisory, codify this new security knowledge, and run it to find a SQL injection issue and other variants.

Before we get started it is important that all of the prerequisites are met so you can participate in the workshop.

The workshop is divided into multiple sections and each section consists of exercises that build up to the final query. For each section we provide hints that help you finish the exercise by providing you with references to QL classes and member predicates that you can use.

Overview

In this workshop we will look for a known SQL injection vulnerabilities in the XWiki Platform's ratings API component. Such vulnerabilities can occur in applications when information that is controlled by a user makes its way to application code that insecurely construct a SQL query and executes it. SQL queries insecurely constructed from user input can be rewritten to perform unintended actions such as the disclosure of sensitive information.

The SQL injection discussed in this workshop is reviewed in GHSA-79rg-7mv3-jrr5 in GitHub Advisory Database.

To find the SQL injection we are going to:

• Identify the vulnerable method discussed in the advisory and determine how it is used.
• Model the vulnerable method as a SQL sink so the SQL injection query is aware of this method.
• Identify how the vulnerable method can be used by finding new XWiki specific entrypoints.
• Model the new entrypoints as a source of untrusted data that can be used by CodeQL queries.

Once we have completed the above steps, we can see whether the models, our codified security knowledge, can uncover variants or possible other security issues.

Let's start with finding more about the SQL injection.

1. Finding the insecure method

In the security advisory GHSA-79rg-7mv3-jrr5 in GitHub Advisory Database we learn of a Jira issue that discusses SQL injection in more detail.

From the Jira issue we learn that:

1. There exists a method getAverageRating in the Rating Script Service.
2. The two parameters of getAverageRating are used in the class AbstractRatingManager to insecurely construct a SQL statement.

We will use CodeQL to find the method and use the results to better understand how the SQL injection can manifest.

Select the database [xwiki-platform-ratings-api-12.8-db.zip] as the current database by right-clicking on it in the Explorer and executing the command CodeQL: Set current database.

The following steps can be implemented in the exercise file SqlInjection.ql

1. Find all the methods with the name getAverageRating

   Hints

   • The java module provides a class Method to reason about methods in a program.
   • The class Method provides the member predicates getName and hasName to reason about the name of a method.

2. Refine the set of results by limiting it to methods named getAverageRating, that accept two parameters where the first parameter is named fromsql.

   Hints

   • The class Method provides the member predicates getParameter that expects an index to retrieve the corresponding parameter, if any, and getNumberOfParameters that returns the number of formal paramerters.
   • The class Parameter provides the member predicates getName and hasName to reason about the name of a parameter.
   • getFile() returns a File associated with an element which has a member predicate getAbsolutePath useful for debugging.

3. Find all the methods with the name getAverageRatingFromQuery.

   Hints

   • The java module provides a class Method to reason about methods in a program.
   • The class Method provides the member predicates getName and hasName to reason about the name of a method.

4. Reduce the number of results by filtering uninteresting results.

   Hints

   • The class Method provides the member predicates getBody to reason about the statements that constitute a method.
   • You can use the quantifier exists like not exists(foo()) to determine if a predicate has no results.

5. Find all the calls to a method named search.

   Hints

   • Calls to methods are method accesses. The class MethodAccess allows you to reason about method accesses.
   • The class MethodAccess provides a member predicate getMethod allows you to reason about the method being accessed.
   • The class MethodAccess provides the member predicates getName and hasName to reason about the name of a method.

6. Find all the method accesses in the method getAverageRatingFromQuery.

   Hints

   • The class MethodAccess provides the member predicate getEnclosingCallable to reason about the method or constructor containing the method access.
   • The class Callable provides the member predicates getName and hasName to reason about the name of a method.

7. Select the qualified name of the method search.

   Hints

   • The class Method provides the member predicate getQualifiedName useful fore debugging. The more efficient hasQualifiedName for restricting a method.

8. Use the qualified name of the method search to uniquely identify it.

   Hints

   • Use the where clause to restrict the results of the query.
   • The class Method provides the member predicate getQualifiedName useful fore debugging. The more efficient hasQualifiedName for restricting a method.

2. Identifying and modelling a SQL sink

The following steps can be implemented in the exercise file SqlInjection.ql You can use CheckPoint1.ql as a starting point if you were unable to complete the previous section.

1. Transform the where clause into a class with the name XWikiSearchMethod

   Hints

   The steps for transforming a where clause into a class are:

   1. Define a class and it's characteristic predicate. It will extend, through extends, from the class used in the from part of your select clause.
   2. Copy the where part from the select clause into the characteristic predicate.
   3. Replace the variable with type the class extends from with the this variable.
   4. If the class relies on other variables from the from part then you can wrap the body of the characteristic predicate with an exists quantifier to introduce those variable.

2. Find the first argument of all the invocations of the search method.

   Hints

   • The class MethodAccess provides a member predicate getMethod allows you to reason about the method being accessed.
   • The class MethodAccess provides the member predicate getArgument and getAnArgument to reason about arguments used by the method call.

3. Create the class XWikiSearchSqlInjectionSink that extends the QueryInjectionSink class to mark the first argument of an invocation to the method search a sink.

   Hints

   • The QueryInjectionSink can be imported through the SqlInjectionQuery module using import semmle.code.java.security.SqlInjectionQuery
   • The QueryInjectionSink is a subclass of DataFlow::Node, so it represents a node in the dataflow graph. You can use the member predicate asExpr to find a corresponding AST node.
   • The class MethodAccess provides a member predicate getMethod allows you to reason about the method being accessed.
   • The class MethodAccess has a member predicate getArgument that provided an index returns the nth argument provided to the method access.

3 Attack surface and sources

The following steps can be implemented in the exercise file SqlInjection.ql You can use CheckPoint2.ql as a starting point if you were unable to complete the previous section.

1. Write a query that finds classes annotated with org.xwiki.component.annotation.Component.

   Hints

   • The class Class provides the member predicate hasAnnotation to determine if the class is annotated.

2. Extend the query to include only classes that implement the interface org.xwiki.script.service.ScriptService.

   Hints

   • The class Interface represents all the Java interfaces in a program.
   • The class Interface provides the member predicates getQualifiedName and hasQualifiedName to reason about the qualified name of an Java interface.

3. Transform the where clause into the class XWikiScriptableComponent.

4. Use the class XWikiScriptableComponent and find all the public methods.

5. Extend the query to find all the parameters of the just found public methods.

6. Transform the where clause into the class XWikiScriptableComponentSource that extends the class RemoteFlowSource and identifies parameters of the public methods defined in a scriptable component as sources of untrusted data.

   Hints

   • Reuse the class XWikiScriptableComponentSource, a subclass of Class, to reason about scriptable components.

   • The class Class provides the member predicate getAMethod to get the Java methods that belong to a java class.

   • The class Method provides the member predicate isPublic to determine if a method is publicly accessible.

   • The class Method provides the member predicates getParameter and getAParameter to reason about parameters associated with a Java method.

   • Subclasses of RemoteFlowSource require the implementation of a member predicate getSourceType to describe the type of the source. Use the following implementation:

      override string getSourceType() {
         result = "XWiki scriptable component"
      }

7. Use the modelled source and sink in combination with the existing QueryInjectionFlowConfig to find the vulnerability as follows:

   1. At the top of the query add meta data to inform the CodeQL Query Results viewer to provide data flow paths.

      /**
      * @kind path-problem
      */

   2. Import the module QueryInjectionFlow::PathGraph that will construct the data flow paths for the CodeQL Query Results viewer

      import QueryInjectionFlow::PathGraph

   3. Use the QueryInjectionFlow module to find paths between remote flow sources and SQL injection sinks.

      from QueryInjectionFlow::PathNode source, QueryInjectionFlow::PathNode sink
      where QueryInjectionFlow::flowPath(source, sink)
      select sink, source, sink, "Found SQL injection from $@", source, "source"

With the final query we can commence with variant analysis. You can use CheckPoint3.ql as a starting point if you were unable to complete this section.