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.

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.

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 githubuniverseworkshop/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 GitHub Universe! 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

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

Welcome to the workshop Codifying security knowledge - Going from security advisory to CodeQL query! 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.

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.

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 acceptes 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.

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. Find the first argument of all the method calls to 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.

To inform the data flow analysis of our new sink we must extend the class QueryInjectionSink with the logic of our select clause

Replace your select clause with the following query:

// Import the SQL injection data flow configuration and extensions points.
import semmle.code.java.security.SqlInjectionQuery

class XWikiSearchSqlInjectionSink extends QueryInjectionSink {
    XWikiSearchSqlInjectionSink() {
      exists(Method searchMethod, MethodAccess searchMethodInvocation, Expr firstArg |
        searchMethod.hasQualifiedName("com.xpn.xwiki.store", "XWikiStoreInterface", "search") and
        searchMethod = searchMethodInvocation.getMethod() and
        searchMethodInvocation.getArgument(0) = firstArg
      |
        firstArg = this.asExpr()
      )
    }
}

from QueryInjectionSink sink
select sink

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. Extends the query to find all the parameters of the just found public methods.

   Hints

   • The class Class provides the member predicate getAMethod to reason about method declared by a 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 a method's parameters.

To inform the data flow analysis of our new source we must extend the class RemoteFlowSource with the logic of our select clause

1. Replace your select clause with the following query:

   class XWikiScriptableComponentSource extends RemoteFlowSource {
      XWikiScriptableComponentSource() {
         exists(Class component, Interface scriptService, Method publicMethod, Parameter parameter |
            component.hasAnnotation("org.xwiki.component.annotation", "Component") and
            scriptService.hasQualifiedName("org.xwiki.script.service", "ScriptService") and
            component.extendsOrImplements(scriptService) and
            component.getAMethod() = publicMethod and
            publicMethod.isPublic() and
            publicMethod.getAParameter() = parameter
            |
            this.asParameter() = parameter
         )
      }
   
      override string getSourceType() { result = "XWiki scriptable component" }
   }
   
   import QueryInjectionFlow::PathGraph
   
   from QueryInjectionFlow::PathNode source, QueryInjectionFlow::PathNode sink
   where QueryInjectionFlow::flowPath(source, sink)
   select sink, source, sink, "Found SQL injection from $@", source, "source"

2. Add the following meta data to the top of your query (above the import java) to inform the CodeQL Result Viewer to display paths.

   /**
   * @kind path-problem
   */

3. Run you query.

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.