Andrew Bone

Posted on Nov 9, 2018 • Updated on Nov 13, 2018

Storing permissions ~ AoaH Nine

#sqlite #learning #node #help

Storing permissions in an SQLite database

Opening

Hi, this is part nine of an ongoing series where I'm learning to code, the aim is to make a PWA to help manage Active Directories and, I feel, we're making great progress. As I said this is part nine, there are now dots, you may have noticed them, above and below the post to let you go through my older post. Alternatively here is an index page.

What was this weeks project?

Some of you may be aware that I wanted to have a configuration file for storing details that can't be easily stored in a remote database, like the database's location. This week I've been looking at SQLite rather than a config file. SQLite is a lite database, with a simple SQL like syntax, that is stored as just a file, meaning it doesn't need a service to run the database.

ignis-pwa / permissions_helper

Create and modify an SQLite file for managing permissions

permissions_helper

Create and modify an SQLite file for managing permissions

View on GitHub

I've linked the GitHub link above, I've tried to comment my code a bit better this week let me know if it's still hard to understand and give me tips on how to tidy up my style.

How does it work?

It's a helper class, called Permissions, the idea is it binds or creates, the database then, using a bunch of functions, can query that database. Passwords are, of course, encrypted. I used bcrypt for this.

An example of functionality is:

const ph = new Permissions();
ph.checkPassword('admin', 'default').then((match) => {
  console.log(match);
}).catch(err => {
  console.log(err)
})

This would then return true or false depending on whether admin's password is "default" or not. The checkPassword() function returns this promise:

/**  
 * Checks password against database version.
 * @param {string} username The users username
 * @param {string} password The users password
 * @return {boolean} Passwords match
*/
checkPassword(username, password) {
  return new Promise(async (res, rej) => {
    if (!this.sql) await this._init();
    const dbPassword = await this.sql.get(`SELECT user_password FROM users WHERE username = "${username}"`);
    res(this.bcrypt.compare(password, dbPassword ? dbPassword.user_password : ""));
  })
}

Why is this marked with 'help'?

As you can see I've been using promises, both await and .then, but the constructor cannot be async, which is why I've made an _init() function. My question is when a class needs to wait before it can be used is it ok to .then even though it's messy or is there a better way I don't know?

The way I've got around this, which feels very hacky, is to make each function check if init has been done and if it hasn't been done do it and wait for it to finish.

Signing off

The code is up on GitHub feel free to head over there to tell me what I've done wrong or what you think I could do better. You can leave a comment on here too if you like, more comments and hearts/unicorns means more people read the posts and I get more input so I'm so grateful for those of you that do that.

Thank you so much for reading this far and putting up with my ramblings.

🦄❤🦄🦄❤

Top comments (12)

Evaldas Buinauskas • Nov 9 '18 • Edited

`SELECT user_password FROM users WHERE username = "${username}"`

This is such a sweet spot for SQL injection!

If you would pass a name, such as: hax0r"OR"1"="1 you would end up with the following query:

SELECT user_password FROM users WHERE username = "hax0r"OR"1"="1"

That could become a big problem.

Andrew Bone • Nov 9 '18

So I'd need some sort of validation?

I think in this instance they'd just get false as the array is never passed back but is compared to the password string, though, I see your point stands.

Thomas Werner • Nov 9 '18

In this case prevalidation or filtering of user input is not a good idea, since you cannot know the wide range of quirks and exploits to guard against and you will make a mistake. It's better not having to worry about the effectiveness of an attack like that. There's a solution for that. Look into "parameterized" or "prepared" statements. Instead of baking user input right into your query string you use placeholders instead (pseudo code):

const statement = sql.prepare("SELECT user_password FROM users where username = :username")

...which are safely populated by calling a setter method on the prepared statement:

statement.setParameter("username", unfilteredNastyUserInput);

That's just pseudo code for presenting the idea. You need to look up how this is done with your library or framework

Andrew Bone • Nov 13 '18

Thank you, I've started using sqlite3 which has prepared statements built in 😀

You were very helpful

github.com/ignis-pwa/permissions_h...

Evaldas Buinauskas • Nov 9 '18

Thomas has summed it up quite nicely.

Maybe this exact query wouldn't leak your data. I really wanted to point out that these kind of queries are potentially dangerous. 😉👍

Avalander • Nov 9 '18 • Edited

My question is when a class needs to wait before it can be used is it ok to .then even though it's messy or is there a better way I don't know?

I assume you need a Promise because you are initialising a connection to the database or something similar. In that case, I would just have a setup function for your app that would run after the connection is established and inject the connection to whatever object needs it.

This is an example of what I mean.

initDb()
  .then(db => {
    const getPonies = makeGetPonies(db)
    const insertPony = makeInsertPony(db)

    app(getPonies, insertPony).start()
  })

If you want to see it in the real world, I initialise an express server using this method here.

That being said, since the queries to the database are asynchronous and will return a Promise anyway, it doesn't make a big difference. I think that waiting for the connection to the database to be resolved and then initialise your application is cleaner and easier in the long run, but that's just my personal preference.

Andrew Bone • Nov 9 '18

Is that better than having an init function with the class?

Avalander • Nov 9 '18

It's hard to say what is objectively better without being familiar with your code. I don't even know why you need a class at all if all it's doing is retrieving a user and their password from the database.

I like my approach because I can treat the connection to the database as a synchronous value in my entire application, which means less asynchronous code. Any code that has access to the connection, can use it as a regular object, no need to await for anything in case it hasn't been initialised.

With your approach, any function that queries the database needs to check if the connection object exists, and if it doesn't, call the function that creates it, and wait for the promise to resolve. If you only query the database in one place, it's fine, but I can imagine it becoming harder the more different queries you need to do against the database.

Then again, I might have entirely misunderstood your code.

Andrew Bone • Nov 9 '18

I was only using a class as a place to store lots of functions. I guess it makes sense to have a bunch of functions that you pass the database to as an argument.

Thank you 🙂