Knex (with MySQL) had a very scary SQL injection – Evert Pot

Knex recently released a new version this week (2.4.0). Before this version,
Knex had a pretty scary SQL injection. Knex currently has 1.3 million weekly
downloads and is quite popular.

The security bug is probably one of the worst SQL injections I’ve seen in recent
memory, especially considering the scope and popularity.

If you want to get straight to the details:

My understanding of this bug

If I understand the vulnerability correctly, I feel this can impact a very
large number of sites using Knex. Even more so if you use Express.

I’ll try to explain through a simple example. Say, you have MySQL table structured
like this:

CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`)

And you have a query that does a SELECT using Knex:

const lookupId = 2; const result = await knex('users') .select(['id', 'name']) .where({ id: lookupId });

You’d expect the query to end up roughly like this

SELECT `id`, `name` FROM `users` WHERE `id` = 2

The issue is when the user controls the value of lookupId. If somehow they
can turn this into an object like this:

const lookupId = { name: 'foo'

You might expect an error from Knex, but instead it generates the following query:

SELECT `id`, `name` FROM `users` WHERE `id` = `name` = 'foo'

This query is not invalid. I don’t fully understand fully understand MySQL’s behavior,
but it causes the WHERE clause to be ignored and the result is equivalent to:


Truncated by Planet PHP, read more at the original (another 8765 bytes)