SQL Injection

Along with the use of relational databases and SQL comes one very important attack to be aware of - SQL injection. This attack takes advantage of the way many developers write SQL queries within their programs. Consider the simple relational database we laid out earlier. Let’s assume our web application lets us search for people by their last names. To find Mary Cotts, we would then need a SQL query like:

SELECT * FROM people WHERE last='Cotts';

Since we want our users to be able to specify who to look for, we’d probably give them a search form in our HTML, something like:

<form>
    <label for="last">Last Name:</label>
    <input type="text" name="last">
    <input type="submit" value="Search">
</form>

And in our code, extract the name value from the query string and use it to construct the SQL query using string concatenation:

const querystring = require('querystring');

var url = new URL(req.url, "http://localhost");
var qs = querystring.parse(url.search.slice(1));
var name = qs.name;

var query = `SELECT * FROM people WHERE last='${name}';`;

The problem with this approach is a savvy adversary could submit a “name” that completes the SQL command and begins a new one, i.e.:

bob'; UPDATE users SET admin=true WHERE username='saavyhacker

Our naive concatenation approach then creates two SQL commands:

SELECT * FROM people WHERE last='bob'; UPDATE users SET admin=true WHERE username='saavyhacker';

When we run this query, our savvy hacker just made themselves an admin on our site (assuming our database has a users table with an admin column we use to determine their role)!

This is just the tip of the iceberg for SQL injection attacks - there are many, many variations on the theme.

Preventing SQL Injection

Every database driver provides the ability to build parameterized queries, i.e. a preformatted query that has “slots” where you assign values to. The exact mechanism to use these depends on the driver you are using to connect to the database. For example, if we were using the node-sqlite3 driver to connect our Node application to a SQLite database, we would use:

const querystring = require('querystring');

var url = new URL(req.url, "http://localhost");
var qs = querystring.parse(url.search.slice(1));
var name = qs.name;

var query = `SELECT * FROM people WHERE last=?;`;

// assuming a variable db is declared 
db.run(query, name, (err, result) => {
    // do something with result...
});

Because the slot corresponds to a specific column, the database engine converts the supplied argument to that type before applying it In this case, if our canny hacker uses his string, it would be interpreted as the literal last name “bob’; UPDATE users SET admin=true WHERE username=‘saavyhacker”. Which probably wouldn’t exist in our database, because what kind of parent would saddle a child with such a name?