You’re very welcome
I always prefer learning resources that are language agnostic. I don’t want to read about securing Spring applications, I want to learn how to secure any application, no matter in what language/framework it was written. On this basis, I can definitely recommend one book - “The Tangled Web: A Guide to Securing Modern Web Applications” by Michał Zalewski. It’s not a comprehensive guide, but this book explains how the web works and what are possible security flaws (and how you could solve them). But it is not written specifically for Node.js developers or Ruby on Rails developers, this book is for all web developers that would like to learn a bit about security.
This is interesting in the context of this FAQ. I would like to show how the problem of SQL injection is handled by the sqlite3 library itself. I will not explain what SQL injection is, there are a lot of articles about this on the web. I would prefer to focus on a simple example.
I have a database called test_database
that currently has a single table test_table
in which I store ids from 1 to 8. Nothing useful, but it’s just for the sake of example. We have a website with a form where users can type a single id and record with this id will be selected, deleted, does not really matter.
SQLite always sanitizes placeholders, contrary to what Axel wrote. How does it work? Take a look at this code:
const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('./db/test_database.db', (err) => {
if (err) {
console.error(err.message);
}
console.log('Connected to the test_database.');
});
const maliciousParameter = '1 OR TRUE';
const query = 'SELECT * FROM test_table WHERE id = ' + maliciousParameter;
db.each(query, (err, row) => {
if (err) {
console.error(err.message);
}
console.log(row);
});
db.close((err) => {
if (err) {
console.error(err.message);
}
console.log('Closed the database connection.');
});
maliciousParameter
is supposed to store value sent in the form. So we see that our user would like to get some more information from our table, not only a single record, but all the records that are in the queried table. This might be dangerous for example when we are retrieving information about the user - we would not like to return information (email, username, hashed password) about all the users, right?
This code will result in this output:
Connected to the test_database.
{ id: 1 }
{ id: 2 }
{ id: 3 }
{ id: 4 }
{ id: 5 }
{ id: 6 }
{ id: 7 }
{ id: 8 }
Closed the database connection.
So the attack was successful. Let’s see what happens if we use placeholders. I decided to use an object with named properties to simply show that this type of placeholder will also be sanitized:
const maliciousParameter = '1 OR TRUE';
const query = 'SELECT * FROM test_table WHERE id = $id';
db.each(query, {
$id: maliciousParameter
}, (err, row) => {
if (err) {
console.error(err.message);
}
console.log(row);
});
And the result is:
Connected to the test_database.
Closed the database connection.
This query was executed, but it did not return any records. Because the placeholder was used the query was trying to select a record from test_table
with id
equal to "1 OR TRUE"
. This is how sqlite3
prevents from SQL injection when placeholders are used, although there are many different policies that you should incorporate to prevent injection attacks.
I wanted to show that sometimes all you have to do is read the documentation… but this is only possible when you know what you are looking for