dev-resources.site

for different kinds of informations.

Let's stop being stupid about security

Published at

9/2/2020

Let me educate you on SQL Injection

Here's an innocent query; let's say we're using Postgres and NodeJS

SELECT firstName, lastName, email From users where email = "[email protected]"

Not bad, but lets see how we can build it in Javascript:

async function lookupUserByEmail(emailAddress) {
    if(!emailAddress) {
        throw "Email address is required!";
    }

  return await db.any(`SELECT firstName, lastName, email From users where email = "${emailAddress}"`)
}

There is a simple method to ensuring we have an email and fetching a user record from the database. The trouble is, we're doing string interpolation, meaning we take that query and inject ANYTHING from the emailAddress variable. No big deal, right?

What if email address is this:

1 OR 1=1;

That means it would match on the first value, 1, which probably won't match anything or on 1=1 which is literally interpreted by SQL as does 1 equal 1? Which is always true. Thus would return EVERY record in the system.

How could someone use SQL Injection to do harm?

Assume the hacker has no knowledge of the system and does this:

1 or 1=1;

Now I've got a list of all the records in the table; great, what if I want to know what options are present in the system? Well, do this:

1'; SELECT c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (‘r’,”) AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’) AND pg_catalog.pg_table_is_visible(c.oid);

Version?

1'; SELECT version();

Postgres users?

1'; SELECT usename FROM pg_user;

Users and password hashes? No problem:

1'; SELECT usename, passwd FROM pg_shadow — priv;

Here's the thing if you don't sanitize your inputs, you are venerable to so many easy hacks. In fact there are many, dozens of different guides on any and all versions of SQL.

How do I protect myself?

Unlike the idiots in Chicago, buying an expensive Cisco Firewall will not do anything to protect you. In fact, it will only cause you to feel more secure thus take more risks. No what you need is real security. Basic security. It's like putting a lock on your door type of security.

Enter Parameterized Queries AKA "Prepared statements"

Just about every SQL library supports Parameterized Queries. If they don't you need to find a new lib to use.

Example in PQ:

Given our example from above, here's what a safe query would look like:

async function lookupUserByEmail(emailAddress) {
    if(!emailAddress) {
        throw "Email address is required!";
    }

  return await db.any(`SELECT firstName, lastName, email From users where email = "$emailAddress"`, {emailAddress});
}

Well, that was simple! Basically $ with out the brackets are treated as a normal string, you can keep the string template literal (the back ticks) or use quotes instead; then you're dumping email address into an object that gets passed to the db's any method which does all the work.

This works:

lookupUserByEmail('[email protected]');

This does not work as the hacker expected:

lookupUserByEmail('1 or 1=1;');

Prepared statements examples

A few examples of the method body in other languages using this safe method:

$stmt = $dbh->prepare("SELECT firstName, lastName, email From users where email = (?)");
$stmt->bindParam(1, $email);
$stmt->execute();

String sqlQuery = "SELECT firstName, lastName, email From users where email = (?)";
PreparedStatement prepStmt = conn.prepareStatement(sqlQuery);
prepStmt.setString(1, "[email protected]");
prepStmt.executeUpdate();
prepStmt.close();

Does your ORM support prepared statements?

Active Record does right out of the box; it's automatic, similar for Sequelize. Should you turn it off, like this idiot says? Hell NO!