SQL: SQL Injection

Welcome to the SQL Injection lesson!

This lesson is shown as static text below. However, it's designed to be used interactively. Click the button below to start!

At first glance, SQL databases' bind parameters seem unnecessary. (Bind parameters are the ? in SELECT * FROM users WHERE id = ?.) Can't we just use string concatenation instead?
Imagine that we're writing an account registration form for a web app. The user enters their email address into our form. Then we insert the email address into our database.
In the next example, look at how we build the INSERT statement. We're concatenating strings to build the SQL rather than using bind parameters.

exec(`CREATE TABLE users (email TEXT UNIQUE)`);

function register(email) {
  exec(`INSERT INTO users (email) VALUES ('` + email + `')`);
}

register('amir@example.com');
exec(`SELECT * FROM users`);

Result:

That code example will technically work... if all of our users are very nice people who don't know about SQL injection attacks!
In any real-world system, that code is very, very bad. It's the worst code in this course! It contains a security hole that will allow an attacker to execute any SQL that they want. They can pull data out of our database, or change it, or delete it. All they have to do is enter a carefully built string into the account registration form.
Let's start by examining the way that we build the query: by combining strings with +. We'll forget about the database itself for a moment and just focus on the SQL.
Be careful with the next few code examples; it's easy to make a mistake with the quotes. You may find it useful to copy and paste the strings together, emulating what JavaScript will do.

const email = 'amir@example.com';
const combined = "INSERT INTO users (email) VALUES ('" + email + "')";

combined;

Result:

const email = "' oh no '";
const combined = "INSERT INTO users (email) VALUES ('" + email + "')";

combined;

Result:

"INSERT INTO users (email) VALUES ('' oh no '')"

const email = "'); DROP TABLE users; --";
const combined = "INSERT INTO users (email) VALUES ('" + email + "')";

combined;

Result:

"INSERT INTO users (email) VALUES (''); DROP TABLE users; --')"

What will happen if the user types that last email string into our registration form's email field? It won't result in a user with an email column of ''); DROP TABLE users; --. Instead, the combined string shown above will be executed as SQL... including the DROP TABLE after the semicolon. It will actually drop the entire users table in our production database!
Let's see the SQL injection run for real, deleting the table. (Remember that you can type error if the code will error.)

exec(`CREATE TABLE users (email TEXT UNIQUE);`);
exec(`INSERT INTO users (email) VALUES (''); DROP TABLE users; --');`);
exec(`SELECT * FROM users;`);

Result:

Error: no such table: users

And here's a more complete version, using our original register function:

exec(`CREATE TABLE users (email TEXT UNIQUE)`);

function register(email) {
  exec(`INSERT INTO users (email) VALUES ('` + email + `')`);
}

register("'); DROP TABLE users; --");

exec(`SELECT * FROM users`);

Result:

Error: no such table: users

The key to this attack is the quote (') that closes the string in our INSERT. After that point, the rest of the user's "email address" string is executed as SQL code.
This technique is one of the most common security exploits on the Internet. It's called a "SQL injection attack" because the attacker injects their own SQL code into our database query. That makes this the most important lesson in this SQL course!
SQL injection is still a huge real-world problem "that will never go away". Some people keep lists of data leaks caused by SQL injection attacks. When this lesson was written, the four most recent entries in the list included: data stolen from the government of India, patient records from a medical company, and users' financial data.
One interesting question about the attack string: why is there a comment (--) at the end? Because otherwise there would be a dangling ') at the end of the combined SQL string, causing a syntax error in the SQL. The attacker adds the comment to prevent that syntax error, which would stop their attack code from running.
(For the next two examples, remember that successful CREATEs and INSERTs return no rows: []. And you can answer "error" if the code will result in an error.)
This is valid SQL:

exec(`CREATE TABLE users (email TEXT UNIQUE)`);
exec(`INSERT INTO users (email) VALUES (''); DROP TABLE users; --')`);

Result:

[]

But this is not, because of the mismatched single quote (') at the end:

exec(`CREATE TABLE users (email TEXT UNIQUE)`);
exec(`INSERT INTO users (email) VALUES (''); DROP TABLE users;')`);

Result:

Error: unrecognized token: "')"

This form of SQL injection has been made even more famous by an XKCD comic about it. Take a look at the code in that comic: it's exactly the kind of attack that we just saw! It closes off a string, uses a semicolon to start a new statement, drops a table, and has a comment to avoid a syntax error.
Here's a code problem:
Drop the students table by executing a "Bobby Tables" SQL injection attack, as made famous by XKCD. (If you get stuck, try using a text editor to manually combine your attack string with the other strings in the register function. Make sure that that combined SQL string looks right.)
exec(`CREATE TABLE students (name TEXT)`);

function register(name) {
exec(`INSERT INTO students (name) VALUES ('` + name + `')`);
}

register(
"Robert'); DROP TABLE students; --"
);

exec(`SELECT * FROM students`);
Goal:
Error: no such table: students
Yours:
Error: no such table: students
Show Author's Answer
We've used DROP TABLE to show that an attacker can run SQL maliciously. However, deleting data isn't their only option. For example, they might UPDATE our database to become an administrator without us noticing.
Like before, let's start by seeing how the attack SQL is constructed. Note that the next code example doesn't execute any SQL. It only builds up a SQL query inside of a string. (And like before, you may want to copy and paste the pieces of this string together.)

const email = "attacker@example.com'); UPDATE users SET admin = 1 WHERE email = 'attacker@example.com';--";
const query = "INSERT INTO users (email) VALUES ('" + email + "')";

query;

Result:

"INSERT INTO users (email) VALUES ('attacker@example.com'); UPDATE users SET admin = 1 WHERE email = 'attacker@example.com';--')"

Here's a code problem:
Use a SQL injection attack to make attacker@example.com an administrator. (Note: our code here also inserts an innocent user. After the attack is executed, the innocent user shouldn't be an admin, so you'll need a WHERE on your UPDATE. The final SELECT here will return both users: attacker and innocent.)
exec(`CREATE TABLE users (email TEXT, admin INTEGER NOT NULL DEFAULT 0)`);
exec(`INSERT INTO users (email) VALUES ('innocent@example.com')`);

function register(email) {
exec(`INSERT INTO users (email) VALUES ('` + email + `')`);
}

register(
"attacker@example.com'); UPDATE users SET admin = 1 WHERE email = 'attacker@example.com';--"
);

exec(`SELECT * FROM users`);
Goal:
[{email: 'innocent@example.com', admin: 0}, {email: 'attacker@example.com', admin: 1}]
Yours:
[{email: 'innocent@example.com', admin: 0}, {email: 'attacker@example.com', admin: 1}]
Show Author's Answer
Great! Now please never do that to anyone else's web app!
SQL injection attacks are subtle, but the practical takeaway from this lesson is simple. Never concatenate SQL strings, even if it seems safe. Write your SQL in a single, self-contained string. If you need to pass dynamic data to your INSERTs, SELECTs, etc., use bind parameters. Bind parameters are carefully implemented by the database authors to inject values securely.
Please don't let the direness of this warning scare you away from SQL! Avoiding SQL injection is relatively easy if you never concatenate SQL strings. And in practice, most projects use object-relational mappers or other higher-level libraries that generate SQL. When using those libraries, you'll still need to understand the concepts in this course. But the library will help you to avoid SQL injection vulnerabilities.
A final note: this kind of attack can happen in other languages, too; it's not specific to SQL. If your application ever executes user input as code, then there's a potential security vulnerability. This is why most teams' linter configurations disallow the eval function. Always treat user input with care!