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 pgsql_events 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:
def identify_sql_injections():
sql_queries, http_requests = _get_data_from_pixie(settings)
filtered_sql_queries = _filter_data(sql_queries)
sql_injections = _identify_sql_injections(filtered_sql_queries)
_submit_nr_events(settings, sql_injections + xss_events)You need the following for this tutorial:
- Python 3
- Pixie installed on your Kubernetes cluster
- A New Relic One account
- The following keys, which you will export to the environment where you’ll deploy your Python script:
NR_INSERT_KEYNR_ACCOUNT_IDPIXY_API_TOKENPIXY_CLUSTER_ID
It’s also helpful if you already have experience using Pixie’s API and Application Performance Monitoring in New Relic One.
1. Load SQL queries from Pixie
The following PxL script grabs the pgsql_events from the last minute. There is no pre-filtering in the PxL script, since this is at the proof of concept stage.
import px
fields = ['time_', 'req', 'resp', 'latency']
df = px.DataFrame(table='pgsql_events', start_time = '-1m')[fields]
px.display(df, 'psql_table')
fields = ['time_', 'req_path', 'req_body']
df = px.DataFrame(table='http_events', start_time = '-1m')
px.display(df, 'http_table')At this point, you can filter the data. For example, you can filter out BEGIN, COMMIT, and ROLLBACK commands initiated by the Postgres database.
2. Identify SQL injection queries
Next, create a SQL_INJECTION_RULE_DICT with a list of regexes that will identify SQL injection queries. This is a basic list for our proof of concept, so we recommend enhancing your own dictionary further. In defense of our ruleset, attackers usually start with attempts like these to see if vulnerabilities are present. This ruleset captures many attackers’ first attempts. At the very least, this will help you determine if someone is probing your system for vulnerabilities before trying something more sophisticated. That’s why we’re planning on building these rules into the Pixie SQL parser.
SQL_INJECTION_RULE_DICT = {
"script_tag": re.compile(r"(<|%3C)\s*script", flags=re.IGNORECASE),
"comment_dashes": re.compile(r"--"),
"comment_slash_star": re.compile(r"\/\*"),
"semicolon": re.compile(r";"),
"unmatched_quotes": re.compile(r"^([^']*'([^']*'[^']*')*[^']*')[^']*'[^']*$"),
"always_true": re.compile(r"OR\s+(['\w]+)=\1", flags=re.IGNORECASE),
"union": re.compile(r"UNION"),
"char_casting": re.compile(r"CHR(\(|%28)", flags=re.IGNORECASE),
"system_catalog_access": re.compile(r"FROM\s+pg_", flags=re.IGNORECASE),
}Next, you need logic for gathering the injection events that match the ruleset and then transforming them into New Relic One events.
def _identify_sql_injections(filtered_sql_queries):
""" Returns an array of dicts representing injection events. """
sql_injections = []
for query in filtered_sql_queries:
for rule, regex in SQL_INJECTION_RULE_DICT.items():
if regex.search(query["req"]):
sql_injections.append(_create_injection_event(query, rule))
logger.info(f"{query['req']} matched {rule} rule.")
return sql_injectionscreate_injection_event packages the Pixie Row item into a New Relic Event.
Note that Pixie provides timestamps in nanoseconds, whereas New Relic wants timestamps in seconds.
def _create_injection_event(query_row, rule):
"""
Returns a dict representing a SQLInjection event with the given query string and rule.
"""
return {
"eventType": "SQLInjection",
"query": query_row["req"],
"baseQueryType": _identify_base_query(query_row["req"]),
"dangerWords": _identify_danger_words(query_row["req"]),
"rule": rule,
"timestamp": query_row["time_"] / 10 ** 9,
}3. Report SQL injection queries to New Relic One
Now that you have your array of SQL injection events, let’s push them to New Relic. Our script converts the array of injection events into a temporary JSON file to post to New Relic. You’ll need your New Relic account ID and a New Relic Insert Key. For a detailed guide, check out the New Relic Event API.
def _submit_nr_events(settings, events):
""" Submit array of custom events to NR using the Event API. """
events_json = json.dumps(events)
nr_key = settings["NR_INSERT_KEY"]
nr_account_id = settings["NR_ACCOUNT_ID"]
temp_name = None
with tempfile.NamedTemporaryFile(delete=False) as events_json_file:
events_json_file.write(bytes(events_json, encoding="utf-8"))
temp_name = events_json_file.name
# Reference: https://docs.newrelic.com/docs/telemetry-data-platform/ingest-apis/introduction-event-api/#submit-event
os.system(
f'gzip -c {temp_name} | curl -X POST -H "Content-Type: application/json" '
f'-H "X-Insert-Key: {nr_key}" -H "Content-Encoding: gzip" '
f"https://insights-collector.newrelic.com/v1/accounts/{nr_account_id}"
f"/events --data-binary @-"
)
os.remove(temp_name)4. Testing your script with sqlmap
Before we make a chart in New Relic One, we’ll need to do some penetration testing. We’ll use sqlmap, an open source penetration-testing tool that allows you to test SQL injection queries on your system. In this case, we’re using it to make sure our proof of concept works and that our script’s integration with New Relic One is tracking suspicious queries. Here, the vulnerable endpoint is https://example.com/api/host?id=1 where the id query parameter is vulnerable to SQL injection.
git clone https://github.com/sqlmapproject/sqlmap
cd sqlmap
python sqlmap.py -u "https://example.com/api/host?id=1" -a
sqlmap will run a bunch of attack strings against an endpoint you determine and prompt you as it does so with a series of questions regarding what kind of attacks to run. A minute or so after the first attack string, you should start seeing data inside New Relic One.
5. Make pretty charts in New Relic One
You should see the events in New Relic as SQLInjection type events. Now for the fun part: making charts! Here’s a visualization of all the `sqlmap` injections grouped by rules in our regex rule dictionary. If you haven’t created a custom dashboard in New Relic before, see Add custom visualizations to your dashboards in New Relic’s docs.
SELECT count(*) FROM SQLInjection SINCE 1 hour ago FACET ruleGreat! New Relic One is picking up all of sqlmap’s “suspicious” queries.
Here’s another grouping by baseQueryType, the top-level database action. Our anger words filtered for state-changing actions and actions that involve executables, including UPDATE, DELETE, and SCRIPT.
SELECT count(*) FROM SQLInjection FACET baseQueryType WHERE baseQueryType IS NOT NULLSince sqlmap is primarily a tool to probe for vulnerabilities, it makes sense that the majority of the injection attempts are SELECTs.
Finally, here’s a view of SQLInjection events over time.
SELECT count(*) FROM SQLInjection TIMESERIES since 1 week agoThe spike on the left side of the chart corresponds to manually running sqlmap for a demo. Hopefully, these visualizations can help you both as a proactive tool for development and as an ongoing monitoring tool!
Next steps
Become an open source contributor to Community Cloud for Pixie.
If you’re not already a New Relic customer, then request a demo or sign up for a free trial today.
This blog post contains “forward-looking” statements, as that term is defined under the federal securities laws, including but not limited to statements regarding expected features and benefits of New Relic’s Pixie Integration, New Relic’s contribution of the SQL injection rule dictionary to the open source Pixie project, New Relic’s replacement of our rule set approach with machine-learning detection, including any anticipated benefits, results and future opportunities related thereto. The achievement or success of the matters covered by such forward-looking statements are based on New Relic’s current assumptions, expectations, and beliefs and are subject to substantial risks, uncertainties, assumptions, and changes in circumstances that may cause New Relic’s actual results, performance, or achievements to differ materially from those expressed or implied in any forward-looking statement. Further information on factors that could affect New Relic’s financial and other results and the forward-looking statements in this post is included in the filings New Relic makes with the SEC from time to time, including in New Relic’s most recent Form 10-Q, particularly under the captions “Risk Factors” and “Management’s Discussion and Analysis of Financial Condition and Results of Operations.” Copies of these documents may be obtained by visiting New Relic’s Investor Relations website at http://ir.newrelic.com or the SEC’s website at www.sec.gov. New Relic assumes no obligation and does not intend to update these forward-looking statements, except as required by law
The views expressed on this blog are those of the author and do not necessarily reflect the views of New Relic. Any solutions offered by the author are environment-specific and not part of the commercial solutions or support offered by New Relic. Please join us exclusively at the Explorers Hub (discuss.newrelic.com) for questions and support related to this blog post. This blog may contain links to content on third-party sites. By providing such links, New Relic does not adopt, guarantee, approve or endorse the information, views or products available on such sites.
