New Relic : Detect SQL injection with New Relic’s Pixie integration

Think about the possible security vulnerabilities in the code that you ship. What keeps you up at night? You'd be wise to answer SQL Injection. After all, it's been on the top 10 list of common vulnerabilities and exposures (CVEs) since 2003.

We won't link to any news stories about devastating hacks. Instead, we're here to demonstrate how you can use New Relic's Pixie integration to proactively detect and report SQL injection attempts while your application is live.

Why detect injection attempts? Why not just block them actively? Because we, your humble authors, also happen to be developers. As developers, we want to understand our code in action.

What we can observe, we can understand. Although SQL injection sounds like a bogeyman, when we take the time to look, it is often more like a weed. It's an obnoxious, inevitable part of growing our codebase and can run rampant. However, if we have the tools to see it for what it is and not be afraid of it, we can nip it in the bud.

We made a simple Python script that uses Community Cloud for Pixie to flag suspicious SQL queries and report them to New Relic One.

This script is a proof of concept of a grander vision. We're really excited to make a security product using New Relic's Pixie integration, one that will cover the OWASP (Open Web Application Security Project®) Top 10 vulnerabilities.

In the short term, we'll be contributing the SQL injection rule dictionary to the open source Pixie project as a part of Pixie's built-in SQL parser. We are also extending our proof of concept to cross-site scripting (XSS) and server-side request forgery (SSRF) attacks.

In the mid-term, we want to replace our rule set approach with machine learning detection. In the long term, we are planning to design an observability-based security product that will run on open source building blocks.

Because that long-term vision will take a while, we'll leave you with the recipe for our SQL injection proof of concept. At the end of the workflow, you'll have some pretty charts in New Relic One that illustrate SQL injection attempts!

So spin up your development environment and get ready to turn monsters into dandelions.

What if I just want to use Pixie, not New Relic One?

If you don't want to get into New Relic One right now, check out this SQL injection demo repo for an all-in-one PxL (Pixie language) script to identify possible SQL injections and push them to tables in the Pixie UI. This is the same demo that the Pixie team demoed this year at KubeCon.

After you get it working, consider trying New Relic. The Community Cloud for Pixie retains data for 24 hours. With New Relic's Pixie integration, you have long-term data storage. New Relic accounts are free forever and you pay based on your data usage.

Reporting SQL injection queries to New Relic One

This Python script gathers data from the Pixie table, uses a simple rule list of regexes to identify queries that are potential SQL injections, and reports the suspicious queries to New Relic One. The code for this experimental plugin can be found here: newrelic-experimental/nr-pixie-security-plugin. All of the code discussed in this post can be found in the identify_sql_injections.py file.

The top-level logic looks like this: