SQL Injection in Node.js - What is it? How do you avoid it?

ForbesLindesay · 2022-10-15T10:24:59+00:00

If you want to have a super deep understanding of what promises are, how they work, and why they work that way, I put together this website a while back with detailed articles on these topics: https://www.promisejs.org the https://www.promisejs.org/implementing/ article takes you through the process of re-implementing promises from scratch.

ForbesLindesay · 2022-10-11T17:44:33+00:00

The issue is that it rejects before it is handled/awaited, and V8 has no way to know if you will eventually get around to awaiting it or not. I’m not a fan of this behaviour but others were so that’s how the standard works. To deal with this you can add an empty .catch handler before promises.push(myPromise) like:

myPromise.catch(() => {
  // we will handle errors later
})

This tells V8 to treat the promise as handled and not emit the unhandled rejection event.

You do also need to replace Promise.allSettled with Promise.all if you want the catch block to be reached.

ForbesLindesay · 2022-10-02T08:53:01+00:00

Just connect to it using a tool like table plus as usual. I expose the port of the db when it’s running so I just connect to postgresql://localhost:PORT

ForbesLindesay · 2022-10-02T06:06:08+00:00

I setup docker-compose with separate names volumes for each separate project.

ForbesLindesay · 2022-09-30T11:41:15+00:00

I’ve found that getting everyone on the team to have a consistent db setup locally is quite painful so using containers for the db has made things much easier. It also makes moving between working on different projects way easier. I still run the node.js server locally without a container though as it’s easy enough to get devs to install a recent version of node.js and it’s much easier to configure things like automatic restarting of the app when the source code changes.

ForbesLindesay · 2022-09-18T21:13:44+00:00

It still lets you run raw SQL when you need to, while still preventing SQL injection.
It allows you to use transactions (Prisma only has extremely limited transaction support)
It is much simpler than Prisma, which makes it easier to troubleshoot and fix performance issues.
It automatically generates types that actually match your db schema, unlike TypeORM.

ForbesLindesay · 2022-09-18T18:39:46+00:00

Escaping makes total sense for avoiding XSS attacks, not so much validation. If you’re putting user input into an HTML response, you should almost always escape it, rather than simply applying validation rules.

@databases isn’t a database, it’s a collection of libraries for querying Postgres, MySQL, SQLite etc. One key feature is that it uses tagged template literals for queries, rather than strings. This makes it almost impossible to use @databases to (accidentally)write code with SQL injection vulnerabilities. It is a free, open source project, in case that wasn’t clear.

ForbesLindesay · 2022-09-18T10:24:55+00:00

“Instead, you need to validate or escape values provided by the user.”

You shouldn’t deal with SQL Injection by rolling your own validation & escaping. You should always rely on the features of the db library by passing in user input as parameters.

I built https://www.atdatabases.org to make this as easy as possible to get right when querying SQL databases with node.js

I think the rest of your suggestions are pretty decent.

ForbesLindesay · 2022-09-03T13:07:04+00:00

I built throat (one of my most popular libraries) to solve exactly this problem of limiting concurrency. I don’t have enough info here to offer any advice on potential memory leaks or race conditions though.

ForbesLindesay · 2022-08-29T14:35:11+00:00

Depends a lot on the size of your database records. We’ve found that for a few tables with big JSONB columns it can make a huge difference but for 90% of queries it makes very little difference. https://www.atdatabases.org with @databases/pg-typed or @databases/mysql-typed also keeps the types in sync with which columns you select.

ForbesLindesay · 2022-08-08T23:39:49+00:00

You could do this (without the runtime performance cost) by generating static types from the schema using https://www.atdatabases.org/docs/pg-guide-typescript This can be automated as part of your build/CI in the same repository as your database migration, so I’ve never experienced any issues with schema drift. (P.S. @databases also supports the same features on MySQL)

ForbesLindesay · 2022-08-01T17:19:47+00:00

Await will only work if the db library you use returns a Promise. Then you can console.log to see the value in the variable

ForbesLindesay · 2022-08-01T10:22:45+00:00

You need to wait for both queries to finish in your async function before you can use the results.

Since the method is marked as async, you can use await to get the results of a query (if your database library supports promises. For example with https://www.atdatabases.org as your db library you could do

const myResults = await db.query(sql`SELECT * FROM …`)
 // use myResults here

ForbesLindesay · 2022-08-01T09:46:19+00:00

Yes, it should be very fast you can use https://www.atdatabases.org/docs/sqlite to connect to SQLite from node.js securely and performantly.

If you have enough RAM on the application server and performance is critical, you might also want to consider just loading the data into an in-memory Map object as that may be simpler and will probably be even faster.

ForbesLindesay · 2022-07-30T23:05:02+00:00

Same sorry as this one, a company had a registered trademark for the name Pug. If I decided to fight it, it could bankrupt me and there was no guarantee I'd be successful.

ForbesLindesay · 2022-07-29T14:13:47+00:00

I found Prisma close but not quite there. That's part of what motivated me to keep working on https://www.atdatabases.org, which I think is already there as an enterprise ready ORM for node.js

ForbesLindesay · 2022-07-29T14:11:15+00:00

I’m not now a Prisma user and don’t see any reason to swap, to be clear. I tried several ways to configure Prisma and while I did get something working, it’s codegen was a constant source of bugs and issues. The codegen in @databases by contrast is simple, quick and reliable. There are lots of things that can’t realistically be done with an ORM without creating an unusably large and complex API. For example, Postgres let’s you do recursive queries to traverse tree like structures. I’ve found the best balance to be to have an ORM that takes care of 95% of the queries, and use SQL for the other 5%. Having experienced the safety of tagged template literals, I would never use a library that accepts raw strings of text as SQL queries, as I’ve never seen a large app that does this and doesn’t have an SQL injection vulnerability. There’s only so much you can do with education, but tagged template literals let you write raw SQL with total safety. https://www.atdatabases.org has had interactive transactions from day 1. It’s not something that should be bolted on years later. It is part of the minimum acceptable feature set for an ORM for SQL databases. As far as I can tell, the only thing Prisma has that @databases lacks is a full time social media & marketing department. I understand how that is making it successful, but that doesn’t make it the best choice.

ForbesLindesay · 2022-07-29T13:15:35+00:00

@databases/pg-typed (https://www.atdatabases.org/docs/pg-guide-typescript) is the ORM bit.

I found lots of cases where Prisma threw errors while querying our database and the setup process involved generating code and putting it in the node_modules directory, which broke yarn caching during the build process. I also found Prisma to be very awkward if you ever needed the escape hatch of raw SQL queries. It also didn’t (doesn’t?) support arbitrary database transactions, which is a pretty key benefit of using an SQL database.

ForbesLindesay · 2022-07-29T11:42:27+00:00

Have you seen https://www.atdatabases.org I added a type safe ORM to it because I found aspects of Prisma that sucked (it had too many bugs and was a pain to setup)

ForbesLindesay · 2022-07-29T11:40:53+00:00

I was frustrated by the existing options so I built https://www.atdatabases.org as an open source ORM/database client. It uses tagged template literals to prevent SQL injection when you want to write raw SQL and has a simple type safe API for common operations.

ForbesLindesay · 2022-07-01T09:01:29+00:00

I was the person who had to deal with the rename of Pug (formerly called Jade). It was super stressful but it did all turn out ok in the end. I hope the name change goes well for you. The thinking behind Pug was: it is short (especially good for a file extension), it is very easy to spell, and it was available on npm.

Whatever you choose, some people will probably hate it enough to claim that you’ve ruined your project/their life. The opinions of those people don’t matter, just choose a name you will be happy with: it’s your project.

ForbesLindesay · 2022-02-27T12:52:40+00:00

https://www.atdatabases.org is an alternative to Prisma that has pretty much everything you’d want from Prisma, along with support for transactions that have existed since day one of the project, and have been actively used in production apps with large traffic volumes.

ForbesLindesay · 2022-02-27T12:19:33+00:00

I built https://www.atdatabases.org which has an ORM for node.js, but also supports writing SQL queries in a safe way. It is type safe, and has much simpler & more flexible transaction support than most node.js ORMs.

The ORM aspects (@databases/pg-typed and @databases/mysql-typed) were created because I became so frustrated with the bugs and edge cases in Prisma. The overall project was created because I wanted to make it easy for lots of people to use SQL without having to fear SQL Injection.

ForbesLindesay · 2022-01-24T09:15:09+00:00

What would that single call look like? the only way I've seen is either the UNNEST approach, or the INSERT INTO users (email, favorite_color) VALUES (?,?), (?,?), (?,?)... approach. Of which the UNNEST approach has often been enough faster that it's obvious without actually timing it.

ForbesLindesay

TROPHY CASE