Are SQL placeholders safe?

Question

Are SQL placeholders safe?

Answer

Placeholders are a very efficient way to pass values to a SQL query but they can be a target to value injection which simply is when someone, not the user will use the placeholder to insert a value that can recover sensitive data. For that reason, masking placeholders with ? can be a safer approach, for example, instead of:

//using node SQLite
const selectByGenre = genre => {
  db.all('SELECT title FROM Song WHERE genre = $genre', {
    $genre: genre
  }, (err, row)=>{
    console.log(row);
  })
}

//this would slightly better

const selectByGenre = g => {
  db.all('SELECT title FROM Song WHERE genre = ?', 
    g, (err, row)=>{
    console.log(row);
  })
}

It is a slight difference but it keeps the query cleaner ('sanitized' by SQLite as it is called) which means it does not specify what kind of property it is, the only thing to watch for is that the amount and order of the ? placeholders have to match the values passed.

3 Likes

Why is it “safer” to use the ? placeholders instead of the $property_name placeholders?

1 Like

It literally explains the reason in the first two sentences. Placeholders can be exploited to inject malicious code. Additionally, a placeholder often describes, what kind of data input is expected (in our above example: genre). A simple “?” does not.

It’s always advisable to question statements about security.

In this case, I have to say that the question made by @masakudamatsu was in place. This article is simply wrong. There is no difference if you define parameters via ? or via an object with named properties.

I think that I would be able to guess that from WHERE genre = ? :smiley: And if I don’t have access to the source code then I would not even know that the parameter $genre was used.

No difference.


This is a very wrong and very disturbing definition of “sanitization”.

4 Likes

Wait, so this whole explanation in the original post is wrong?

Does using ? instead of a placeholder make any difference at all?

If not, I am of course sorry for answering prematurely…

2 Likes

Unfortunately, yes.

No. Use what you prefer, what feels nicer. This is just a matter of preference. I usually prefer to use named placeholders - for me, it is easier to see which value corresponds to a given placeholder.

Nothing bad happened, this is not your fault (this is the fault of the author of this article) :slight_smile: I just wanted to step in and clarify.

I will add this article to my list and will try to correct it as soon as possible. Honest apologies for the confusion.

5 Likes

Thank you for your response, @factoradic! It’s very helpful.

Talking about security, CodeCademy’s Skill Path on back-end web development does not teach anything about it, even though it’s obviously a very important topic for back-end development. Would you mind pointing to where we can learn how to, for example, prevent “value injection” and other programming techniques for security? Do you have any recommended books, websites, JS libraries, etc.?

2 Likes

You’re very welcome :slight_smile:

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 :slight_smile:

7 Likes

Thank you, @factoradic, (sorry for my delayed response) for your suggestion on a book on security. I’ll definitely have a look. I agree with you that language-agnostic resources is the best way to learn something new.

For value injection, I’m a bit puzzled by the maliciousParameter set to be '1 OR TRUE' in your example. But this article by W3Schools helped me understand it. SQL’s WHERE keyword evaluates whether the following statement is true or false. 1 OR TRUE is always true for all id’s. So you get all users. But once the maliciousParameter is the value for a placeholder, it cannot be a true or false statement. So it doesn’t work. Am I correct to understand your example this way?

3 Likes

Sorry, that so late :slight_smile:

You are correct. SQLite is quite problematic because there are no strict column types. So the only thing that might be not completely accurate is:

This query will be executed in the database and it might even give us a result. So it works, but not in a malicious way. The query will look for any record from the test_table with id equal to 1 OR TRUE (literally, this has to be the value, this is not an expression). And because SQLite does not have strict types - you can add a row with id "1 OR TRUE" (text) even if id is a numerical column.

2 Likes

Just want to say thanks to @factoradic and @masakudamatsu for the back and forth, it helped me understand something, and clear things up for me.

cheers guys!

1 Like