The Art of the Security Double Play: How Mercari Combines Internal Audits and Custom CodeQL Queries to Keep Systems Safe

Abstract

I’m Ahmed Belkahla, and I am currently doing an internship in the Product Security team at Mercari. During my internship, I had the opportunity to work on several exciting projects and gained valuable insights into the strategies and techniques used to safeguard the security of Mercari’s products. In this blog post, I’ll share these approaches in detail. Specifically, we will dive into how we conduct our internal security tests and the different steps we follow. Additionally, I will explain how we seek to improve our Security testing approach and use CodeQL to automate our custom test cases.

Sections:

• A comprehensive overview of Mercari’s shift-left approach
• Why manual testing falls short: Limitations and possible improvements
• Integrating CodeQL to the SDLC: Automating security testing
• Maximizing security coverage with CodeQL custom queries
• A deep dive into CodeQL query development

A comprehensive overview of Mercari’s shift-left approach

Application security is one of the most crucial pillars of any organization’s security posture as it plays a key role in protecting businesses from data breaches and cyberattacks. At Mercari, ensuring the security of our products is a top priority, and this is why we have a dedicated team for Product Security. To secure our products, we are applying a shift-left security strategy. Addressing security earlier in the development process allows us to ensure that our software is designed with security best practices built in and fix any potential security issues when they are less difficult to address in the initial phases of the SDLC (Software Development Life Cycle). This approach allows us to be more cost, time, and resource efficient.

Now that we’ve gotten the formalities out of the way, let’s dive deeper into the technical details by discussing the first important step in our process! In order to guarantee the implementation of the appropriate controls and defenses for each project and ensure that the corresponding team takes them into consideration, our team typically arranges threat modeling sessions in the initial stages of each project. During these sessions, we analyze and identify possible threats and evaluate their associated risks. Not only does our Product Security team take part in these sessions, but also members from the relevant product actively participate in discussing potential threats. This collaborative effort broadens the security mindset across the company and has proven to be an efficient way of identifying and addressing security risks.
In addition to the threat model we also carry out design reviews and security testing. Testing is essential to ensure that our products meet high security standards, regardless of whether they are intended for public release or internal use. The design review and testing process can be broken down into the following core activities:

• Design Review
• Web Application Testing
• API Testing
• Mobile Application Testing (Android/iOS)

To ensure more efficiency, a straightforward and simple process has been implemented for the development and product teams to request design reviews and security testing. They simply need to use our internally developed Slackbot and the Product Security team will receive the request and handle it according to its urgency, priority, and release schedule.

Now that a clear understanding of our approach has been established, let’s take a closer look at how we conduct each activity.

Design Review:

This process involves reviewing the design documents and diagrams for new features, ensuring they don’t contain logic flaws or overlook critical security considerations. Additionally, we verify that the personally identifiable information (PII) related to our users is handled correctly. To conduct these types of reviews, a thorough understanding of UML (unified modeling language), software design, and flow diagrams is required in order to comprehend the software architecture and functionality. Interestingly enough, I found that some of the seemingly mundane software development lessons I learned in university proved to be quite valuable and aided me in completing these tasks. Furthermore, the Product Security team actively takes part in requirement review sessions during the early stages of project planning.

Web Application Testing:

Once the design review is concluded and approved, we move on to technical testing. This step is crucial to ensure the security of the actual implementation, to identify and to address any potential vulnerabilities.
In this section, we will focus on front-end and business-related vulnerabilities before delving into back-end-related flaws in the next section. At Mercari, we make extensive use of React JS as our front-end framework, which effectively mitigates most client-side attack vectors. Nevertheless, we remain vigilant and conduct thorough testing to prevent any possible misuse that could lead to malicious exploitation. As an example, let’s examine a recently released feature that I had the opportunity to work on, and explore some of the test cases that we have carefully examined.

• The use of dangerouslySetInnerHTML attribute: As its name suggests, this attribute is similar to innerHTML and allows developers to directly set HTML code to elements, bypassing React’s default sanitization process. This is usually the first sink we check, but luckily it’s rarely/never used by our developers.

• Fetch Diversion issues: This might seem new for some readers since this type of attack is not widely known and made its appearance lately.
  It mainly consists of injections in the path segment of the API’s URL, which usually occurs when the client side code tries to retrieve some data from the API using GET requests without any proper sanitization. More information about this attack can be found in the following article.

• Unsafe postMessage communication: The window.postMessage function is usually used to allow cross-origin or different windows/iframes communication.
  In this case, checking the origin of different event messages is important since insufficient controls can lead to some flaws. This may be more impactful when dangerous JS functions or sensitive actions are used with the data received through the messages.

• Possible JS injections or unsafe usage of dangerous functions: We always make sure that user input is sanitized and doesn’t end up in a sensitive function (eval, setTimeout, etc.).

Even though some of these checks are automatically done by some of CodeQL default queries, manual checks need to be added to circumvent some limitations.

These were some of the front-end audit test cases we perform on our web applications.
Now, let’s delve into the business-related test cases, where we assess the potential logic vulnerabilities that may exist within Mercari products and issues that might violate our internal guidelines.
Mercari operates across a broad variety of industries (fintech, e-commerce, blockchain, etc.) which allowed me to gain exposure to a variety of areas where security is critical to the business. For instance, in the Mercari C2C marketplace and Merpay mobile payment services, sensitive actions necessitate verification through a passcode that is set up using a custom flow. The classification of sensitive information may vary across our services as such it is crucial to consider all these factors during security tests.

API Testing:

This section focuses on the testing of API endpoints, which are used by both our web and mobile applications. Here, we perform a thorough check for the OWASP API security top 10 vulnerabilities and examine any business-related threats that may be specific to our services.

These are some examples of the main threat scenarios we focused on in a recent test we performed:

• Information disclosure to 3rd party: Here we check for any sensitive information that may be disclosed to third parties, as we prioritize protecting the privacy of our users and not unintentionally exposing Personally Identifiable Information (PII).

• Authentication and authorization: We test for possible Insecure Direct Object Reference (IDOR) vulnerabilities that could allow for information leakage or unauthorized actions on behalf of other users. Additionally, this section covers passcode verification, rate limiting, OTP verification as well as other authorization issues.

• Token Management: Our test includes a critical evaluation of token management, testing for possible token leaks and ensuring proper token revocation and timeout management.

Overall, each of these test cases requires careful consideration of potential edge cases that could result in unexpected or harmful behaviors.

Mobile Application Testing ( Android/iOS )

To wrap up our discussion on our pre-release Security testing areas, we’ll cover how we conduct mobile application testing. And what better way to illustrate our approach than by sharing a recent project we worked on. In fact, we recently had the opportunity to work on a security test related to the implementation of FIDO passkey support in our marketplace application, which we are gradually expanding to other services. This initiative was announced last month in a press release which you can read more about here. In the following section, we will use this test as an example to shed light on our mobile application testing process.

What are FIDO Passkeys ?

The FIDO Alliance defines passkeys as follows:

"Passkeys are a replacement for passwords that provide faster, easier, and more secure sign-ins to websites and apps across a user’s devices. Unlike passwords, passkeys are always strong and phishing-resistant.​"

Source: https://fidoalliance.org/passkeys/

FIDO Passkeys are a type of credential that offers more flexibility and security than traditional single-device credentials. Passkeys are stored in the platform’s cloud keychain, such as the iCloud Keychain or Google Password Manager. This storage enables users to access their accounts from multiple devices signed in to the same account. The introduction of FIDO Passkeys is motivated by the need to mitigate phishing risks on second key registration and the loss of account access with single-device credentials.

FIDO Passkeys use public/private key cryptography to provide secure authentication. When a user logs in with a Passkey, the RP (Relying Party) sends a challenge and the user retrieves the private key using their biometric information. The user then signs the response and sends it back to the RP, who verifies it using the public key. The private key is stored solely on the device belonging to the user and synchronized with the cloud keychain of the platform provider, while the relying party (RP) solely retains the public key.

Each Passkey is classified with the application package name, which helps to ensure that only the intended application can use the Passkey. This provides an additional layer of security for Passkey authentication.

Image Source: ThalesGroup

Threat scenarios:

I got exposed to passkeys for the first time during my internship so I had to familiarize myself with the flow and the design diagrams of the implementation before jumping into the test. In addition to scrutinizing the typical mobile security vulnerabilities, such as insecure data storage, cryptographic APIs and other tests from OWASP MASTG, we had to put in extra effort and brainstorm possible edge cases that could lead to unforeseen outcomes. Throughout the audit, we tested a variety of scenarios, some of which included the following:

• Register a passkey with one platform account then switch the platform account and try to use it.

• Try to delete an existing passkey and replace it with a passkey linked to a different passkey account.

• Log in to the same account on a different device with a different platform account, and try to use passkey.

• Check if one can approve his/her own passkey when adding a second passkey to the same account.

• Delete an already approved passkey from an unapproved device.

• Approve another passkey from an unapproved device

• Delete a passkey using its keyId from another account (IDOR)

In conclusion, the security of our products is of utmost importance to us at Mercari. We take a comprehensive approach to identifying and addressing potential vulnerabilities and security flaws through design reviews, web application testing, API testing, and mobile application testing. By following this process, we strive to ensure that our products are safe and secure for our users.

Why manual testing falls short: Limitations and possible improvements

In the previous section, we presented the overall process for manually testing a new feature within Mercari. The primary aspect that stands out is how we ensure our presence in every part of the development cycle. However, this level of involvement comes at a cost: conducting these tests and checks is a time-intensive process for our Product Security team. With the number of projects that we work on at Mercari, it can be challenging to keep track of all of them. Therefore, we may find ourselves obliged to prioritize certain products or only focus on critical functionalities.

In addition, the time frame for releasing certain features may be tight, and the amount of time available to address any vulnerabilities found during testing may be limited.

As a result, the Product Security team may feel pressure to align their efforts with the product release schedule. Mercari also has a massive codebase which makes it a daunting task for security engineers to thoroughly review and cover all of its components.

Finally, as with all manual tests, there’s always a possibility of overlooking some vulnerabilities, even the most meticulous among us can occasionally miss the mark. We’re only human after all, but don’t worry, we’re doing our best to keep up with our robot overlords!

As previously mentioned, it is important to address the limitations and difficulties we face during manual testing by considering possible solutions and improvements.
One solution that we have implemented is the integration of automated security testing tools into our CI/CD pipeline. By identifying the most common vulnerabilities through automated testing, our developers and security engineers can investigate and address these flaws before manual testing. This approach helps to reduce the number of viable attacks and save time.
The most important tools we use are:

• NowSecure: An automated dynamic mobile app security testing solution that performs SAST (Static Application Security Testing) and DAST (Dynamic Application Security Testing) and can be easily integrated into CI/CD pipelines.

• Burp Suite Entreprise: As the enterprise version of the well-known product by Portswigger, Burp Suite Enterprise allows running security tests on web applications and visualizing them on the Burp Enterprise dashboard. Moreover, it can be seamlessly integrated into the CI/CD pipeline, you can refer to the Portswigger documentation for more information.

• Mend: A Software Composition Analysis (SCA) tool we use to detect vulnerable open source dependencies and comply with license policies. It’s also integrated into our CI/CD pipeline and has a separate dashboard to track the alerts.

• CodeQL: An advanced static analysis tool utilized for comprehensive code analysis and vulnerability detection. It enables us to identify security vulnerabilities, coding errors, and quality issues in our codebase.

We will focus on CodeQL as the primary solution in this blog post. CodeQL is a powerful semantic code analysis engine that enables querying code as if it were data, automating security checks and variant analysis by modeling security vulnerabilities as queries. One of the most significant advantages of CodeQL is its ability to integrate with GitHub Advanced Security (GHAS) and GitHub workflows with ease. At Mercari, we primarily use CodeQL as our SAST solution in GitHub code scanning, and it has greatly improved the productivity of our developers by reducing false positives and allowing for efficient visualization and triage of alerts.

The details about the process of integrating CodeQL into our SDLC have already been well-explained in a blog post by my mentor, @Eli.

I would also like to mention that, alongside automated testing, it is worth noting that our organized threat modeling sessions and security champion program play a vital role in reducing the need for manual testing by forecasting potential threats and promoting a security-focused mindset among our developers.

Integrating CodeQL to the SDLC: Automating security testing

In this section we will talk about how CodeQL is integrated into our Secure Systems Development Lifecycle (SSDLC) and provide more information about this solution.
First of all let’s start by quickly explaining the different phases of SSDLC:

• Education: Empowering developers through specialized training sessions on writing secure code and promoting a security-focused mindset through internal training programs such as Security Champion.

• Planning/Requirements: The planning phase is where the project or product requirements are identified and defined by project managers.

• Design: Based on the requirements identified in the planning phase, engineers will create design documents to realize the product or new features.

• Development: In this phase, engineers write code for the product or new features, and use GitHub for collaboration, along with CI/CD tools for deployment.

• Testing: After development, the QA and security teams test the application, identify bugs and vulnerabilities, which are reported to the development team for fixing, and the product is released once all issues are resolved.

• Deployment: Once testing is completed, the decision to deploy the product to the production environment is made.

• Maintenance: Engineers continue to maintain the product, adding new features and making improvements to the code.

• Retirement: Ensuring secure decommissioning of systems and associated assets in compliance with established protocols and regulatory requirements

We will focus on the Development and Testing phases where we are using GitHub Advanced Security, which is a market leading solution, offering exceptional features such as secret scanning to detect hardcoded keys or tokens, and code scanning that scans the code for vulnerabilities and facilitates the triage. Code scanning can be configured to utilize either CodeQL or a third-party tool.

Next, we will discuss how to integrate CodeQL into a project and set up automated security scanning using GitHub’s built-in features. To access the code scanning feature in private repositories, you will need to have a license for GitHub Advanced Security.

Integrating CodeQL into your project is a straightforward process, you have to navigate to the Security tab in your repository, then simply visit the code scanning alerts page and follow the easy steps provided by GitHub. This will generate a new workflow using a template comparable to the one below, which is set to run a CodeQL scan automatically on push and pull requests to the master branch.

name: "CodeQL"

on:
  push:
    branches: [ "master" ]
  pull_request:
    # The branches below must be a subset of the branches above
    branches: [ "master" ]
  schedule:
    - cron: '40 7 * * 3'

jobs:
  analyze:
    name: Analyze
    runs-on: ${{ (matrix.language == 'swift' && 'macos-latest') || 'ubuntu-latest' }}
    permissions:
      actions: read
      contents: read
      security-events: write

    strategy:
      fail-fast: false
      matrix:
        language: [ 'go' ]

    steps:
    - name: Checkout repository
      uses: actions/checkout@v3

    # Initializes the CodeQL tools for scanning.
    - name: Initialize CodeQL
      uses: github/codeql-action/init@v2
      with:
        languages: ${{ matrix.language }}
    - name: Autobuild
      uses: github/codeql-action/autobuild@v2

    - name: Perform CodeQL Analysis
      uses: github/codeql-action/analyze@v2
      with:
        category: "/language:${{matrix.language}}"

As we continue to explore the capabilities of CodeQL, it’s worth noting that you can easily customize the workflow to fit your specific needs and programming language. In fact, you can even run multiple analyses in parallel if you specify many programming languages in the language matrix. To further customize the analysis, you can also add custom CodeQL queries or packs to the workflow by adding the following parameters in the "Initialize CodeQL" stage.

• packs to install one or more CodeQL query packs and run the default query suite or queries for those packs.
• queries to specify a single .ql file, a directory containing multiple .ql files, a .qls query suite definition file, or any combination.

This will come in handy as we delve deeper into the topic in the following sections. For more information about CodeQL integration and possible customizations you can visit GitHub’s official documentation.

At Mercari, we use Golang and Typescript extensively and have CodeQL integrated into most of our repositories, thus, CodeQL has become an essential tool in our toolbox.
We also make sure to frequently review the generated alerts and eliminate the false positives to keep track of the security posture of our repositories.

Maximizing security coverage with CodeQL custom queries

At Mercari, we are always striving to improve our security testing process and stay ahead of new vulnerabilities that emerge in the industry. In this section, we will explore how we are extending and enhancing CodeQL’s capabilities to achieve these goals.

Implementing Mercari-Specific Test Cases:

One of the ways we are leveraging CodeQL is by implementing Mercari-specific test cases. By doing this, we can ensure that our applications are not only secure but also meet the specific requirements of our organization. This approach allows us to identify potential security threats that are unique to our systems and address them accordingly.

Improving Inaccurate Default Query Implementations:

While CodeQL’s default query pack provides a solid foundation for vulnerability detection, we found that some default query implementations can be inaccurate. We will present an example in the next section to demonstrate this case. To address this issue, we started working on improving the default query pack whenever we discover any potential flaws, to increase its effectiveness in detecting vulnerabilities.

Tackling New Rising Vulnerabilities Every Day:

As new vulnerabilities emerge every day, we need to ensure that our applications are protected against them so whenever a significant new vulnerability appears, we add our own checks to CodeQL. Additionally, even though the community is constantly contributing with new interesting queries, it takes some time before they are merged to the default query pack.

Reviewing Large Codebases for Specific Vulnerable Patterns or CVEs:

We have a large codebase at Mercari, which can make it challenging to identify and address vulnerabilities effectively. To streamline this process, we experimented with developing specific queries that can identify vulnerable patterns or CVEs across our codebase.

Next, we will discuss some of our goals related to CodeQL and how we plan to achieve them.

Reducing Manual Testing Time:

Manual testing can be time-consuming and often requires significant resources. By using CodeQL, we can automate many of our testing processes, reducing the amount of manual testing required. This approach saves time and allows us to focus on other critical security tasks.

Extending the Vulnerability Detection Capabilities:

We are also extending CodeQL’s vulnerability detection capabilities by improving and extending the default query pack. Additionally, we are providing more detailed output and warnings, which allows developers to independently investigate and verify alerts.

Writing a Query Every Time We Find a New Vulnerability in a Security Test:

Finally, we aim to use CodeQL as an equivalent way of pre-release security testing. Whenever we find a new vulnerability in a security test, we write a new query to detect it. Our ultimate goal is to have the Product Security team retire from routine work to focus on more interesting projects, while CodeQL will take the security testing task automatically.

A deep dive into CodeQL queries development

In this final section, I will list some examples of custom CodeQL queries we developed to show the overall thought process. Resources about CodeQL are still not popular so you will always end up going through the official documentation which is a good reflex you can gain from CTFs.
While some general templates for Taint Tracking or other CodeQL features can be useful, getting familiar with each language’s libraries and logic still requires time.
A great way to start is by practicing with the GitHub CodeQL CTF and studying pre-built queries in the default pack. Understanding concepts such as Abstract Syntax, Data Flow, and Control Flow is crucial to writing effective CodeQL queries. It’s also important to think about identifying the sources and sinks of the vulnerabilities you’re trying to implement. Enough talk, let’s dive into the exciting part!

Example 1: PostMessage Origin check query

One of the already implemented queries in CodeQL is the PostMessage Origin check which detects if the origin of the incoming event messages is checked securely. However, upon review, we found that the query includes some caveats that could create a false sense of security by not reporting some edge cases that could be exploited by attackers. To address this issue, we decided to refactor the original query and add additional checks with more verbose output.
The original query implemented the following checks:

This CodeQL snippet basically considers checks that are done with startsWith and includes functions as secure which is not the case as shown below:

However, we made some changes to the original query to make it more verbose on detecting more edge cases. The following is the most important function in the new query:

string verboseOut(PostMessageHandler handler) {
  // window.origin == event.origin 
  if
    exists(EqualityTest test | sourceOrOrigin(handler).flowsToExpr(test.getAnOperand()) and 
windowOrigin(DataFlow::TypeTracker::end()).flowsToExpr(test.getAnOperand()) )
  or
  // "safeOrigin.includes(event.origin)" 
    exists(InclusionTest test | sourceOrOrigin(handler).flowsTo(test.getContainedNode()))
  or
  // "safeOrigin".startsWith(event.origin)
    exists(StringOps::StartsWith starts |
  origin(DataFlow::TypeTracker::end(), handler).flowsTo(starts.getSubstring())
  )
  or
  // Regex Expression tests
    exists(StringOps::RegExpTest regex |
  origin(DataFlow::TypeTracker::end(), handler).flowsTo(regex.getStringOperand())
  )
  or
  // "safeOrigin".search(event.origin)
    exists(DataFlow::CallNode fct| fct.getCalleeName()="search" and origin(DataFlow::TypeTracker::end(), 
  handler).flowsTo(fct.getReceiver()) )
  or
  // "safeOrigin".indexOf(event.origin)
    exists(DataFlow::CallNode fct| fct.getCalleeName()="indexOf" and origin(DataFlow::TypeTracker::end(), 
  handler).flowsTo(fct.getReceiver()) )

  then 
    result="Postmessage handler's origin check can be bypassed and is using an unsafe function"
  else if not hasOriginCheck(handler) then 
    result="Postmessage handler has no origin check"
  else 
    result=""
}

In the updated query, edge cases are given due consideration, and a warning message is displayed in case an unsafe function is detected so that the security engineer or developer could investigate.

To have a better understanding of CodeQL syntax, let’s examine the following code snippet that detects the usage of startsWith function:

exists(StringOps::StartsWith starts |
origin(DataFlow::TypeTracker::end(), handler).flowsTo(starts.getSubstring())

In this case, we utilize the exists quantifier in CodeQL to encapsulate our logic. The exists syntax, generally written as exists(<variable declarations> | <formula>), evaluates to true if there is at least one combination of values for the variables that satisfy the formula.

Within the formula, we employ the origin predicate (predicates are simply CodeQL functions), which retrieves the reference to .origin from a postMessage event. It returns a DataFlow::SourceNode and relies on DataFlow::TypeTracker to track the value of a given node.

Here’s the definition of the origin predicate:

DataFlow::SourceNode origin(DataFlow::TypeTracker t, PostMessageHandler handler) {
  t.start() and
  result = event(DataFlow::TypeTracker::end(), handler).getAPropertyRead("origin")
  or
  result =
    origin(t.continue(), handler)
        .getAMethodCall([
            "toString", "toLowerCase", "toUpperCase", "toLocaleLowerCase", "toLocaleUpperCase"
          ])
  or
  exists(DataFlow::TypeTracker t2 | result = origin(t2, handler).track(t2, t))
}

To validate if the origin node of the postmessage event flows into the parameter of the startsWith function (we used getSubstring predicate to obtain the value of B in A.startsWith(B)), we utilize the flowsTo predicate. This combination allows us to detect checks performed with the startsWith function and track their flow through the code.

Example 2: react-bootstrap-table unfixed CVE

In addition to improving the accuracy of the default queries, extending the vulnerability detection capabilities of CodeQL can also help tackle specific vulnerabilities in our codebase. For instance, the React-bootstrap-table library used in some of our internal repositories is no longer maintained and contains a known vulnerability CVE-2021-23398. We implemented a custom query in CodeQL that detects the vulnerable pattern and shows a warning. This way, we can proactively identify and remediate such vulnerabilities in our codebase, reducing the risk of security incidents.
To trigger the vulnerability in the React-bootstrap-table library, an invalid React element must be returned through the dataFormat parameter. Therefore, the custom query we implemented focuses on detecting this pattern and providing a warning whenever it is found, as illustrated below.

import javascript
/** Track the data flow from functions that are not returning a React Component to dataFormat attribute*/
class DataFormatFlowConfiguration extends TaintTracking::Configuration {
    DataFormatFlowConfiguration() { this = "DataFormatFlowConfiguration" }

    override predicate isSource(DataFlow::Node source) {
        exists(DataFlow::FunctionNode func| not func.getAReturn().asExpr() instanceof JsxElement and source=func)
    }

    override predicate isSink(DataFlow::Node sink) { 
        exists( JsxAttribute attr| attr.getName()="dataFormat" and attr.getValue()=sink.asExpr())}

}  

from DataFormatFlowConfiguration dataflow, DataFlow::Node source, DataFlow::Node sink
where  dataflow.hasFlow(source, sink)
select "[WARN] If the data used to populate BootstrapTable can be user-controlled, this might be a potential XSS",source

In this CodeQL snippet, we utilize the TaintTracking::Configuration class to perform interprocedural taint tracking analysis. By overriding the isSource and isSink predicates, we define the source and sink points of interest. In this case, our source consists of functions that do not return a React Component. As seen in the CodeQL snippet, we retrieve the return expressions of these functions and check if they are of type JsxElement.

exists(DataFlow::FunctionNode func| not func.getAReturn().asExpr() instanceof JsxElement and source=func)

On the other hand, our sink is represented by the dataFormat attribute. With the help of CodeQL, we can effortlessly track the data flow and identify potential exploitation paths.

Example 3: Detecting race conditions in golang

CodeQL can also be extended to address gaps in its default query library. For example, CodeQL’s default queries currently lack race conditions and time-of-check-to-time-of-use (TOCTOU) vulnerabilities implementation in Golang.

To fill this gap, we are working on implementing a query that can detect these types of vulnerabilities and enable developers to address them proactively.

However, at the time of writing the blogpost, we are still working on this query but likely we will make all our custom queries publicly available once we finish testing them internally.

These examples illustrate some of the custom queries we have developed at Mercari to improve our pre-release security testing.

Conclusion

In conclusion, security testing is an essential part of any software development process. We’ve discussed the challenges of manual testing and the different threat scenarios we focus on in our internal tests and how code scanning with CodeQL can streamline the process. We’ve explored the benefits of using CodeQL and how we, at Mercari, have integrated it into our workflow to detect vulnerabilities, triage them, and reduce false positives.
We have also shown how CodeQL can be customized to meet specific needs, and extended with custom queries to detect and prevent specific vulnerabilities.
If you’re passionate about security and want to work with cutting-edge technologies like CodeQL, we’re hiring! Come join us and be a part of a dynamic team!

Future Improvements

As with any system, there is always room for improvement in the realm of security testing. In this spirit of continuous progress, let’s take a look at some areas where we can enhance our vulnerability detection process:

• In comparison to other solutions such as semgrep, CodeQL is known to have a steeper learning curve when it comes to writing queries. However, once mastered, CodeQL provides a more in-depth analysis and better detection of vulnerabilities. We may need to investigate if we can also implement semgrep to get the best of both worlds.

• Some threat scenarios can’t be implemented in CodeQL, including those related to some external configuration or vulnerabilities that need dynamic testing. it would be effective if we could figure out how to automate these in the future.

• Create a more streamlined process for developers to verify and respond to CodeQL alerts, to make it easier to handle the volume of information generated by the tool.

Final Thoughts

Overall, my internship at Mercari has been an incredibly fruitful experience, I got the chance to work with top-tier security engineers and observe how they are always thinking about new exciting projects. I thoroughly enjoyed the Mercari culture and appreciated the shared mission of safeguarding our assets and continuously enhancing our security posture, all while fostering an enjoyable work environment.

I’m grateful for the trust and support my colleagues have given me and for the chance to participate in making an impact on the company’s security practices.

I’m always grateful for CTF competitions where I sharpened my security skills, but this time, I had the privilege to work with a great mentor, @Eli, who taught me invaluable lessons and helped me grow as a security engineer. While CTFs are an excellent way to learn about security, there’s no substitute for real-world experience and working with proficient mentors. I’m grateful to have had the opportunity to work with @Eli and the Product Security team and gain more practical knowledge that I wouldn’t have been able to learn in a competition.

I look forward to applying what I’ve learned here to my future endeavors, and I’m excited to see how Mercari continues to evolve and innovate in the years to come.