DEV Community

Storing permissions ~ AoaH Nine

Andrew Bone on November 09, 2018

Storing permissions in an SQLite database Opening Hi, this is part nine of an ongoing series where I'm learning to code, the ...

Read full post

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 🙂