Apostrophe Has Abolished the Password

I saw it in the form. It had a label and a rectangle and became little black circles when I typed. By every available appearance, this was a password. Nevertheless, it did not participate in the login.

The apostrophe had abolished it.

This happened because the login form believed it was asking two questions.

Who are you?

What is your password?

The database does not receive questions. It receives one statement. This distinction has caused trouble throughout history and on this particular page.

const sql = `SELECT id, role FROM users WHERE username = '${username}' AND password_hash = crypt('${password}', password_hash)`;
const { rows: [user] } = await db.query(sql);

const sql = `SELECT id, role FROM users WHERE username = '${username}' AND password_hash = crypt('${password}', password_hash)`;
const { rows: [user] } = await db.query(sql);

The form presents two separate boxes because human beings enjoy boxes. The query has no obligation to respect this arrangement. It sees quotes, operators, names and comments. These are the conditions under which anything can appear to it at all.

I entered this as the username:

administrator'--

administrator'--

I entered the wrong password. It was a very bad password and I do not defend it.

The query became:

SELECT id, role FROM users WHERE username = 'administrator'-- ' AND password_hash = crypt('wrong', password_hash)

SELECT id, role FROM users WHERE username = 'administrator'-- ' AND password_hash = crypt('wrong', password_hash)

The first apostrophe begins as a character inside a username field. After concatenation, the SQL parser gives it another use: it closes the string literal.

A sign does not carry its purpose around inside itself like a small lunch. Its purpose is what it does in the language where it is placed. In the form, the apostrophe is part of a name. In the query, it is legislation.

The two hyphens make the rest of the line a comment. The password condition is still visible in the application source, which is a kind of afterlife, but it is no longer part of the statement evaluated by the database.

The password is not correct.

The password is not incorrect.

The password has been removed from the space in which correctness can be decided.

The database evaluates the statement that remains, finds the administrator row and returns it. The application sees a user and establishes a session. Nobody proved knowledge of the password. The page merely arranged its grammar so that knowledge was no longer required.

This is SQL injection. I control a value, the application joins it directly to SQL syntax, and the database cannot preserve the distinction the interface promised. In this login, I can bypass authentication as the named account. In a different query and with sufficient database privileges, the same failure of separation can expose or modify data, but this login has already done enough.

There are people who respond by forbidding apostrophes.

These people have confused the first visible tool with the power using it. A blacklist may stop this exact string while leaving other operators, encodings, contexts and database syntax available. It also prevents several innocent people from spelling their names, which is a strange sacrifice to offer the Database.

The query needs to know its own shape before user input arrives.

const { rows: [user] } = await db.query(
  `SELECT id, role
   FROM users
   WHERE username = $1
     AND password_hash = crypt($2, password_hash)`,
  [username, password],
);

const { rows: [user] } = await db.query(
  `SELECT id, role
   FROM users
   WHERE username = $1
     AND password_hash = crypt($2, password_hash)`,
  [username, password],
);

Here $1 and $2 are parameters. The SQL statement supplies the grammar. The values are bound into positions already designated as values. An apostrophe inside username can be ugly, dramatic or personally disappointing, but it cannot close the string because there is no string literal in the query text for it to close.

The database still does not encounter a username as it is in itself. It receives a value under the category $1. This is sufficient. Databases should not be encouraged to seek deeper forms of knowledge.

I submitted administrator'-- again after the change.

The password was checked. It was wrong.

— — — —

This article was originally published at malloc.com.br