SQL Formatting and the IN Clause That Breaks Production

Why parameterisation fixes injection structurally

A database receives a statement as text and turns it into a plan in stages: it tokenises, it parses into a tree, it binds names to objects, it optimises, and only then does it execute. When you paste values into the text, all of that happens after your values have already been merged with your syntax, so the parser is deciding what your data means. That is the entirety of the vulnerability. It is not about quotation marks or apostrophes; it is about the boundary between code and data being erased before the database ever sees the statement.

A parameterised statement inverts the order. The text you send contains placeholders and no values at all, so the database parses and plans a statement whose shape is already final. The placeholders are slots in that finished plan, each with a declared type, and when you bind a value you are filling a typed slot in a compiled object — not appending characters to a string that will later be parsed. There is no later parse. That is why the guarantee is structural: a bound value has no path by which it could become an operator, a table name, a comment, or a second statement, because the stage that could have interpreted it as any of those is over before the value exists in the database's world.

Escaping tries to achieve the same outcome by transforming the value instead of moving the boundary, and it is not an equivalent defence. It has to be correct in every context, and the contexts are not the same: a string literal, a numeric literal, an identifier, the pattern of a LIKE, a value inside a JSON path expression and a value inside a dynamically built ORDER BY all need different treatment, and some of them cannot be escaped safely at all. It has to be correct under every character encoding, because the relationship between bytes and characters is what an escaping function is reasoning about. And it has to be applied at every single site, forever, including the one a colleague adds next quarter under deadline. Parameterisation needs to be right once, in the shape of the code; escaping needs to be right every time, in the discipline of the team. Where the value genuinely cannot be a parameter — a table name, a column name, a sort direction — the answer is not to escape it but to validate it against a fixed allow-list of the identifiers your schema actually contains.

The ceilings each database actually documents

The numbers circulating in blog posts about this are frequently out of date, so read them from the manuals. PostgreSQL's limit comes from the wire protocol: the Bind message encodes the number of parameter values in a sixteen-bit field, and libpq refuses anything above 65,535 with a message that names the range explicitly. MySQL has the same practical ceiling for the same reason — the placeholder count in the client-server protocol is two bytes — and exceeding it produces server error 1390, whose text is that the prepared statement contains too many placeholders. MySQL's own bug tracker carries a standing feature request to raise the 64k boundary, which is a fair indication that it is a real constraint and not a theoretical one.

SQL Server is far tighter, and this is the one that catches teams by surprise. Its published capacity specifications list 2,100 parameters per stored procedure and 2,100 per user-defined function. A parameterised batch from a client driver is executed through sp_executesql, which is a stored procedure, so it inherits exactly that ceiling — and one of those slots is consumed by machinery, which is why practitioners usually quote 2,099 or 2,098 as the number of values they can actually bind. Two thousand identifiers is not a large batch by modern standards, so on SQL Server the limit is not a theoretical edge case; it is a design constraint you have to plan around from the beginning.

Oracle limits the expression list itself rather than the parameter count, and the figure changed recently enough that most of what you will read online is wrong. Through 19c the SQL Language Reference states that a comma-delimited list of expressions can contain no more than 1,000 expressions, and exceeding it raises ORA-01795. From 23ai the same page states 65,535. One nuance survives the change: a comma-delimited list of sets of expressions — the multi-column form of IN — can contain any number of sets, but each individual set is still capped at 1,000 expressions. Check the version you are actually running before you size a batch around either number.

Plan-cache pollution, computed

A database caches compiled plans so it does not have to optimise the same statement twice, and the cache key is derived from the statement text. A query with three placeholders and a query with four placeholders are different text, therefore different keys, therefore different entries — even though they are the same query with a different list length. That is the whole mechanism, and the arithmetic falls straight out of it: if your application ever sends list lengths from 1 to N, you generate N distinct plans instead of one.

Compute it for a realistic range. An endpoint that fetches orders over $500 for a set of customer identifiers might see anything from one identifier to a few hundred. Serving lengths 1 to 100 naively produces 100 distinct plans; lengths 1 to 1,000 produces 1,000; lengths 1 to 5,000 produces 5,000. Put a plausible size on a plan — 50 KB is a reasonable middle figure for a query with a couple of joins — and the cache footprint is about 5 MB at 100 lengths, 49 MB at 1,000 and 244 MB at 5,000. At 200 KB per plan, which a complex analytical query easily reaches, 1,000 lengths costs 195 MB and 5,000 costs 977 MB. That memory is not free: it is taken from the same pool as your buffer cache, and on engines where the plan cache is bounded it evicts the plans for the rest of your workload, so the symptom is that unrelated queries get slower.

The statement text itself grows too, and it travels on every request. A query of the form SELECT ... WHERE id IN with numbered placeholders runs to about 525 bytes at 100 values, 5,926 bytes at 1,000, 68,927 bytes at 10,000 and 513,207 bytes at 65,535. Half a megabyte of SQL text per request, parsed from scratch every time because no cached plan will ever match it again, is the shape of the cliff. It is worth being precise about the two costs: the parse and optimise work is paid on every single call because the cache always misses, and the memory is consumed by the entries that never get reused.

The fixes that scale: array, VALUES join, temporary table

The best fix collapses the whole family of statements to one. Pass the list as a single parameter of an array or table type and the statement text stops depending on the list length: one distinct text, one cached plan, one parse, for every call. PostgreSQL offers this directly with an array parameter compared using ANY, and it is the reason PostgreSQL applications rarely meet the 65,535 ceiling at all — a single array parameter counts as one parameter regardless of how many elements it holds. SQL Server has table-valued parameters, which serve the same purpose and are the standard answer to its 2,100 limit. Oracle has collection types that can be selected from as a table.

The portable version of the same idea is a join against a VALUES list, which every mainstream engine supports and which the optimiser can treat as a small relation rather than a long disjunction. It still changes the statement text with the number of rows, so it does not fix the plan cache on its own, but it very often produces a better plan than a thousand-way OR because the optimiser can hash-join it. A temporary table is the version that scales without limit: insert the identifiers in batches, join against the table, and the statement text is constant no matter how many identifiers there are. It costs a round trip and some write activity, so it earns its place above a few thousand values rather than below.

If you are stuck with a literal IN list for now, padding is a cheap stopgap that removes most of the cache damage. Round the list up to a bucket and fill the spare slots with a value that cannot match — repeating the first identifier is the simplest correct trick, since it changes nothing about the result. Rounding to the next power of two turns 1,000 possible lengths into 11 distinct statements, but it wastes on average 174 parameters per call over that range, a 34.8% overhead. Rounding to the next multiple of ten gives 100 distinct statements and wastes only 4.5 parameters on average, 0.9%. That second trade is usually the right one: a hundred cached plans is nothing, and a one-percent overhead on the parameter count is invisible.

Batching above the ceiling

When the list is genuinely longer than the ceiling, split it. The number of round trips falls straight out of the limits: 100,000 identifiers take 2 calls on PostgreSQL, MySQL or Oracle 23ai, 48 calls on SQL Server at 2,100 parameters, and 100 calls on Oracle 19c at 1,000 expressions. That spread is the reason a batch size that works fine on a developer's PostgreSQL container falls over on the customer's SQL Server, and it is worth deriving the chunk size from the database rather than hard-coding one number.

Two details matter when you chunk. Chunk deterministically — sort the identifiers before splitting — so a retry sends the same chunks and your logs are comparable across runs. And decide explicitly whether the read has to be consistent: several separate statements see several separate snapshots, so if the data can change underneath you, wrap the batch in a single transaction or accept that the union of the results is not a snapshot of anything. That second point is the one that produces the bug nobody can reproduce, because it only shows up under concurrent writes.

Formatting is not cosmetic

Two things follow from the fact that the plan cache is keyed on statement text. The first is that formatting matters where you write it, not where you send it: a readable statement in your source file, with the clauses on their own lines and the list broken across several, is the difference between a review that catches a mistake and one that skims past it. The second is that you should not reformat SQL on the way out. If a wrapper prettifies, minifies or normalises whitespace at runtime, and it does so inconsistently — say, differently under load, or differently after a config change — it produces new cache entries for a statement the engine already had a plan for. Format for humans, in the repository; send a stable string.

There is one more reason to keep the statement readable, and it is the reason this article exists. A long IN list rendered as one enormous line hides everything: whether the values are bound or interpolated, whether the count is what you expected, whether a stray value slipped in from another query. Broken across lines with the placeholders visible, all three are obvious at a glance, and so is the moment the list stopped being a handful of identifiers and quietly became a thousand.

Frequently asked questions

Is escaping ever an acceptable substitute for binding?

No, and the reason is not that escaping functions are badly written — it is that they solve a different problem. Binding moves the boundary between code and data so that a value has no route into the syntax. Escaping leaves the boundary where it is and tries to neutralise every value that could cross it, which means it has to be correct in every context, under every character encoding, at every call site, forever. The contexts genuinely differ: a string literal, a numeric literal, an identifier, a LIKE pattern and a value inside a dynamically assembled clause each need different handling, and some of them have no safe escaping at all. A single missed site restores the entire hole, and missed sites are the normal outcome of a codebase with more than one author. There is one case where a value truly cannot be a parameter, because the database will not accept a placeholder there: a table name, a column name, a sort direction, a LIMIT in some engines. The answer there is still not escaping. Validate the value against a fixed allow-list of the identifiers your schema actually contains, and map user input to a constant rather than passing it through.

What do I do when the list is longer than my database allows?

Three options, in increasing order of how much work they are and how well they scale. First, an array or table-valued parameter, if your engine has one: the entire list travels as a single parameter, so the ceiling stops applying and the statement text becomes constant. This is the right answer on PostgreSQL and on SQL Server, where the 2,100 limit otherwise bites early. Second, a temporary table: insert the identifiers in batches, then join against the table. The statement text is constant regardless of list size, the optimiser gets real cardinality information, and there is no ceiling at all — you pay a round trip and some write activity for it. Third, chunking: split the list, run the query once per chunk, and merge the results in your application. Derive the chunk size from the database's own limit rather than hard-coding a number, because the same list needs 2 round trips on PostgreSQL and 48 on SQL Server. Sort before splitting so retries are reproducible, and put the whole batch in one transaction if the merged result has to represent a single consistent moment — several separate statements otherwise see several separate snapshots, which is the source of the intermittent bug nobody can reproduce.

Why does the query get slower as the list grows, even well under the limit?

Three effects stack, and they are worth separating because they have different fixes. The first is compilation: the statement text changes with every list length, so the plan cache misses every time and the optimiser re-parses and re-plans from scratch on each call. At 1,000 values the text is already about 5,926 bytes; at 10,000 it is 68,927. Optimisation time grows with the number of predicates, so this is not a constant overhead. The second is optimiser strategy. An IN list is logically a chain of ORs, and past a certain length the optimiser stops treating it as a set of index lookups and considers a scan instead — MySQL documents this explicitly through the range optimiser, which tracks the memory a range access method would consume and abandons it if a configured limit would be exceeded, falling back to a full table scan. The list crossing that threshold is a cliff, not a slope. The third is the cache pressure described above: your thousand single-use plans evict the plans other queries were using, so the slowdown shows up in queries you did not change. An array parameter or a temporary table addresses all three at once, because it makes the statement text constant and gives the optimiser a relation it can join.

Does using an ORM mean I do not have to think about any of this?

It handles the security half and usually not the operational half. Every mainstream ORM binds values rather than interpolating them, so the injection risk is genuinely gone for query builders and generated statements — the exception being any raw-SQL escape hatch, where you are back to writing parameters yourself and where the vulnerabilities in ORM-based codebases overwhelmingly live. The performance half is a different story. Most ORMs render a WHERE clause on a collection as a literal IN list with one placeholder per element, which is exactly the pattern that changes the statement text with every list length and floods the plan cache. Many will also happily generate a list longer than your database allows and only fail at execution, which is why the SQL Server 2,100 ceiling turns up as a production incident rather than a test failure. The things to check in your own stack are concrete: whether your ORM can emit an array parameter or a table-valued parameter instead of an IN list, whether it batches automatically and with what chunk size, and whether it exposes the generated SQL in a log you can read. If it does none of those, an eager-loading call on a large collection is a plan-cache problem waiting for a Monday.

Does reformatting my SQL change how it performs?

Not the plan, but possibly the cache lookup, and that is a distinction worth holding on to. The optimiser works on the parse tree, so whitespace and line breaks have no effect whatsoever on the plan it chooses — a statement pretty-printed across twenty lines and the same statement minified to one produce identical execution. What they do not necessarily share is a cache entry, because the plan cache is keyed on the statement text and engines differ in how much they normalise it first. The practical rule is therefore simple: format the SQL in your source repository, where a human reads it, and send whatever your driver produces without a runtime beautifier or minifier in the path. A wrapper that reformats inconsistently — differently under load, or differently after someone flips a configuration flag — can create a second cache entry for a statement the engine already had a plan for, which is a small, silent and very annoying regression. Formatting in the repository has a second payoff that matters more: a long IN list broken across lines makes it visible at a glance whether the values are bound or interpolated, and whether the count is what you expected.