SQL injection and CFML 101

SQL injections are among the first things you learn about web application security.

It’s such a simple concept that when it’s explained, everyone goes “Oh, yeah, well duh” yet we’ve all written vulnerable code at some point in our lives.

Sure, someone learning web development might allow the vulnerability without realizing, but even seasoned professionals do it accidentally.

If we’re using third party libraries, it’s an even bigger risk because we can’t or won’t always take the time to analyze the library code in depth.

I did a quick search and found this post about a common WordPress plugin https://thehackernews.com/2017/06/wordpress-hacking-sql-injection.html

Or this, which granted wasn’t just SQLi but included it as an attack vector https://nakedsecurity.sophos.com/2018/02/19/hackers-sentenced-for-sql-injections-that-cost-300-million/

And of course, there’s code curmudgeon SQLi hall of shame https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/

As we all know, CFML is a powerful language, but it’s just as vulnerable to SQLi as any other programming language when using SQL and NoSQL databases.

This post will focus only on SQL injections, I’ll talk about NoSQL injections in another post.

If you’re new to CFML or web development, it might not be obvious at first what the problem is, so here’s an example.

It’ll be a little contrived to keep things simple, this is just to get the point across.

Let’s say you have a user settings page, with a form post such as this

<form action="user_info.cfm" method="post">
    <input type="hidden" name="userid" value="#session.user.id#">
    <input type="password" name="password"><br>
    <input type="submit" value="Submit">
</form>

In the user_info.cfm you have this

<cfquery datasource="users">
    UPDATE users
    SET password = '#form.password#'
    WHERE id = #form.userid#
</cfquery>

Let’s say that session.user.id evaluates to 123. So the userid input on the page will have value="123"​, if you were to modify that to something like value="123 OR id <> 0" it should be obvious what’s going to happen.

The SQL statement that runs is this

UPDATE users
SET password = 'your_password_here'
WHERE id = 123 OR id <> 0

And suddenly we’re updating the password for every user.

By the way, passwords should always be salted and heavily hashed, but that’s another post.

So how can we defend against SQLi?

safe

 

Luckily, we have built-in tools in CFML to mitigate SQLi attacks, so our sites and our clients site don’t end up on code curmudgeons lovely list of shame.

First off, the basic rule is

Never trust data

It doesn’t matter if it comes from the browser or your own database. If you’re using it to build queries, treat it as if it’s dangerous.

Because it is.

Every time you put together a sql statement and use #useful_variable#, you should use the tools available to secure your application, even if you think the source of the data is safe.

In cfquery that means using cfqueryparam and in cfscript the param variable.

When using cfqueryparam make sure to always use cfsqltype, the more specific you can be the better.

Here’s how to secure the example above using cfqueryparam

<cfquery datasource="users">
 UPDATE users
 SET password = <cfqueryparam value="#form.password#" cfsqltype="CF_SQL_VARCHAR">
 WHERE id = <cfqueryparam value="#session.user.id#" cfsqltype="CF_SQL_INTEGER">
</cfquery>

And if we take a look at the same in cfscript

Bad

var data = QueryRun("users", "UPDATE users SET password = #form.password# WHERE id = #form.userid#");

Good

var params = [
    {value: form.password, cfsqltype: "CF_SQL_VARCHAR"},
    {value: session.user.id, cfsqltype: "CF_SQL_INTEGER"}
];
var data = QueryRun("users", "UPDATE users SET password = ? WHERE id = ?", params);

As you can see, we’re now using cfqueryparam and queryrun params properly for the data we’re getting from the form, and we’re using the session.user.id data without it passing through the browser.

And we’re still not trusting it.

Never trust data

Depending on the query you’re building, it can be possible to do everything from getting and setting data, to dropping or creating tables and databases.

This post is just about injections, but it’s always a good idea to read up on general database security, it’s a pretty big topic and goes far beyond injections.

thumbsup

I created a tool several years ago called mScan, it’s an open source static code analyzer and comes with a few tests for CFML source code, including SQLi tests.

It’s not a horribly robust set of tests though, so I’ve started working on a new tool called SQLHuntress which is specialized on finding SQL and NoSQL vulnerabilities in CFML code. As opposed to mScan it’s not a CFML based web application, but an executable you run on the commandline/terminal.

It’s not ready for release yet, but getting there.

However, even though my tools are fantastic and awesome in every single way, you shouldn’t rely only on tools, you need to inspect your application code and look for potential issues yourself.

A quick way is to do a global search in your project for “cfquery” and “queryrun”, and simply inspect the code.

This is very much a brief introduction to SQL injection, but I hope it helps someone.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Blog at WordPress.com.

Up ↑

Create your website at WordPress.com
Get started
%d bloggers like this: