A formatação SQL e a cláusula IN que parte a produção

Porque é que a parametrização corrige a injeção estruturalmente

Uma base de dados recebe uma instrução como texto e transforma-a num plano por etapas: tokeniza, analisa numa árvore, liga nomes a objetos, otimiza e só então executa. Quando cola valores dentro do texto, tudo isso acontece depois de os seus valores já terem sido fundidos com a sua sintaxe, pelo que é o analisador que decide o que significam os seus dados. É a vulnerabilidade inteira. Não é uma questão de aspas nem de apóstrofos; é uma questão de a fronteira entre código e dados ser apagada antes de a base sequer ver a instrução.

Uma instrução parametrizada inverte a ordem. O texto que envia contém marcadores e nenhum valor, pelo que a base analisa e planeia uma instrução cuja forma já é definitiva. Os marcadores são ranhuras nesse plano acabado, cada uma com um tipo declarado, e ligar um valor é preencher uma ranhura tipada dentro de um objeto compilado — não acrescentar caracteres a uma string que será analisada mais tarde. Não há análise posterior. É por isso que a garantia é estrutural: um valor ligado não tem caminho nenhum para se tornar um operador, um nome de tabela, um comentário ou uma segunda instrução, porque a etapa que o poderia ter interpretado assim terminou antes de o valor existir no mundo da base.

Escapar tenta obter o mesmo resultado transformando o valor em vez de mover a fronteira, e não é uma defesa equivalente. Tem de estar correto em cada contexto, e os contextos não são iguais: um literal de string, um literal numérico, um identificador, o padrão de um LIKE, um valor dentro de uma expressão de caminho JSON e um valor dentro de um ORDER BY construído dinamicamente precisam de tratamentos diferentes, e alguns não podem ser escapados com segurança de todo. Tem de estar correto sob cada codificação de caracteres, porque a relação entre bytes e caracteres é precisamente aquilo sobre que uma função de escape raciocina. E tem de ser aplicado em cada ponto, para sempre, incluindo aquele que um colega acrescentar no próximo trimestre com o prazo em cima. A parametrização tem de estar certa uma vez, na forma do código; o escape tem de estar certo todas as vezes, na disciplina da equipa. Onde o valor genuinamente não pode ser um parâmetro — um nome de tabela, um nome de coluna, um sentido de ordenação — a resposta não é escapá-lo mas validá-lo contra uma lista branca fixa dos identificadores que o seu esquema realmente contém.

Os tetos que cada base documenta realmente

Os números que circulam nas publicações de blogue sobre isto estão muitas vezes desatualizados: leia-os nos manuais. O limite do PostgreSQL vem do protocolo de rede: a mensagem Bind codifica o número de valores de parâmetros num campo de dezasseis bits, e o libpq recusa tudo acima de 65 535 com uma mensagem que nomeia o intervalo explicitamente. O MySQL tem o mesmo teto prático pela mesma razão — a contagem de marcadores no protocolo cliente-servidor ocupa dois bytes — e ultrapassá-lo produz o erro de servidor 1390, cujo texto diz que a instrução preparada contém marcadores a mais. O próprio rastreador de erros do MySQL tem um pedido de melhoria pendente para elevar a fronteira dos 64 k, o que indica bem que é uma restrição real e não teórica.

O SQL Server é bem mais apertado, e é o que apanha as equipas de surpresa. As suas especificações de capacidade publicadas indicam 2 100 parâmetros por procedimento armazenado e 2 100 por função definida pelo utilizador. Um lote parametrizado vindo de um controlador cliente é executado através do sp_executesql, que é um procedimento armazenado, pelo que herda exatamente esse teto — e uma dessas ranhuras é consumida pela mecânica, daí que os praticantes costumem citar 2 099 ou 2 098 como o número de valores que conseguem mesmo ligar. Dois mil identificadores não são um lote grande pelos padrões atuais: no SQL Server o limite não é um caso extremo teórico, é uma restrição de conceção a prever desde o início.

A Oracle limita a própria lista de expressões em vez do número de parâmetros, e o valor mudou recentemente o bastante para que a maior parte do que lerá na Internet esteja errado. Até ao 19c a SQL Language Reference indica que uma lista de expressões separadas por vírgulas não pode conter mais de 1 000 expressões, e ultrapassá-lo levanta ORA-01795. A partir do 23ai a mesma página indica 65 535. Uma nuance sobrevive à mudança: uma lista de conjuntos de expressões separados por vírgulas — a forma multicoluna do IN — pode conter qualquer número de conjuntos, mas cada conjunto individual continua limitado a 1 000 expressões. Verifique a versão que executa realmente antes de dimensionar um lote em torno de qualquer um dos números.

A poluição da cache de planos, calculada

Uma base guarda em cache planos compilados para não otimizar duas vezes a mesma instrução, e a chave de cache deriva do texto da instrução. Uma consulta com três marcadores e uma com quatro são textos diferentes, logo chaves diferentes, logo entradas diferentes — embora sejam a mesma consulta com outro comprimento de lista. É todo o mecanismo, e a aritmética sai diretamente dele: se a sua aplicação alguma vez enviar comprimentos de 1 a N, gera N planos distintos em vez de um.

Calcule-o para uma gama realista. Um ponto de entrada que obtém encomendas acima de 500 € para um conjunto de identificadores de cliente pode ver desde um identificador até algumas centenas. Servir ingenuamente comprimentos de 1 a 100 produz 100 planos distintos; de 1 a 1 000 produz 1 000; de 1 a 5 000 produz 5 000. Atribua uma dimensão plausível a um plano — 50 KB é um valor intermédio razoável para uma consulta com um par de junções — e a pegada da cache ronda os 5 MB com 100 comprimentos, 49 MB com 1 000 e 244 MB com 5 000. A 200 KB por plano, valor que uma consulta analítica complexa alcança sem esforço, 1 000 comprimentos custam 195 MB e 5 000 custam 977 MB. Essa memória não é grátis: sai do mesmo depósito que a sua cache de buffers, e em motores onde a cache de planos é limitada expulsa os planos do resto da sua carga, pelo que o sintoma é que consultas sem relação ficam mais lentas.

O próprio texto da instrução também cresce, e viaja em cada pedido. Uma consulta da forma SELECT ... WHERE id IN com marcadores numerados ronda os 525 bytes com 100 valores, 5 926 bytes com 1 000, 68 927 bytes com 10 000 e 513 207 bytes com 65 535. Meio megabyte de texto SQL por pedido, analisado do zero de cada vez porque nenhum plano em cache voltará a corresponder-lhe, é a forma do precipício. Vale a pena ser preciso sobre os dois custos: o trabalho de análise e otimização é pago em cada chamada porque a cache falha sempre, e a memória é consumida por entradas que nunca são reutilizadas.

As correções que escalam: array, junção VALUES, tabela temporária

A melhor correção colapsa toda a família de instruções numa só. Passe a lista como um único parâmetro de tipo array ou tabela e o texto da instrução deixa de depender do comprimento: um só texto distinto, um só plano em cache, uma só análise, em cada chamada. O PostgreSQL oferece-o diretamente com um parâmetro de array comparado através de ANY, e é por isso que as aplicações PostgreSQL raramente chegam sequer ao teto dos 65 535 — um parâmetro de array conta como um parâmetro, contenha os elementos que contiver. O SQL Server tem parâmetros com valores de tabela, que servem o mesmo fim e são a resposta padrão ao seu limite de 2 100. A Oracle tem tipos de coleção que se podem consultar como uma tabela.

A versão portável da mesma ideia é uma junção contra uma lista VALUES, suportada por todos os motores correntes e que o otimizador pode tratar como uma relação pequena em vez de uma disjunção longa. Continua a fazer variar o texto da instrução com o número de linhas, pelo que não corrige a cache de planos sozinha, mas muitas vezes produz um plano melhor do que um OR de mil ramos porque o otimizador o pode juntar por hash. Uma tabela temporária é a versão que escala sem limite: insira os identificadores por lotes, junte contra a tabela, e o texto da instrução é constante independentemente de quantos identificadores existam. Custa uma ida e volta e alguma escrita, portanto ganha o seu lugar acima de alguns milhares de valores e não abaixo.

Se está preso por agora a uma lista IN literal, o preenchimento é um remendo barato que elimina a maior parte do estrago na cache. Arredonde a lista a um degrau e preencha as ranhuras sobrantes com um valor que não possa casar — repetir o primeiro identificador é o truque correto mais simples, já que nada muda no resultado. Arredondar à potência de dois seguinte transforma 1 000 comprimentos possíveis em 11 instruções distintas, mas desperdiça em média 174 parâmetros por chamada nessa gama, 34,8 % de sobrecusto. Arredondar ao múltiplo de dez seguinte dá 100 instruções distintas e desperdiça apenas 4,5 parâmetros em média, 0,9 %. Esse segundo compromisso costuma ser o certo: cem planos em cache não são nada, e um por cento de sobrecusto na contagem de parâmetros é invisível.

Lotear acima do teto

Quando a lista ultrapassa mesmo o teto, divida-a. O número de idas e voltas sai diretamente dos limites: 100 000 identificadores exigem 2 chamadas em PostgreSQL, MySQL ou Oracle 23ai, 48 chamadas em SQL Server com 2 100 parâmetros, e 100 chamadas em Oracle 19c com 1 000 expressões. Essa amplitude é a razão de um tamanho de lote que funciona lindamente no contentor PostgreSQL de um programador se desmoronar no SQL Server do cliente, e convém derivar o tamanho do bloco da base em vez de fixar um número no código.

Dois pormenores importam quando divide. Divida de forma determinista — ordene os identificadores antes de partir — para que uma nova tentativa envie os mesmos blocos e os seus registos sejam comparáveis entre execuções. E decida explicitamente se a leitura tem de ser consistente: várias instruções separadas veem vários instantâneos separados, portanto se os dados podem mudar debaixo dos seus pés, envolva o lote numa só transação ou aceite que a união dos resultados não é instantâneo de nada. Esse segundo ponto produz o erro que ninguém consegue reproduzir, porque só aparece sob escritas concorrentes.

A formatação não é cosmética

Duas coisas decorrem do facto de a cache de planos ser indexada pelo texto da instrução. A primeira: a formatação importa onde a escreve, não onde a envia. Uma instrução legível no seu ficheiro de origem, com as cláusulas nas suas próprias linhas e a lista repartida por várias, é a diferença entre uma revisão que apanha um erro e outra que passa ao lado. A segunda: não reformate o SQL à saída. Se um invólucro embeleza, minifica ou normaliza os espaços em tempo de execução, e o faz de forma inconsistente — digamos, diferente sob carga ou diferente após uma mudança de configuração — produz novas entradas de cache para uma instrução de que o motor já tinha plano. Formate para humanos, no repositório; envie uma string estável.

Há mais uma razão para manter a instrução legível, e é a razão de este artigo existir. Uma lista IN longa desenhada como uma linha enorme esconde tudo: se os valores estão ligados ou interpolados, se a contagem é a que esperava, se um valor estranho se infiltrou de outra consulta. Repartida por várias linhas com os marcadores visíveis, as três coisas são óbvias num relance, e também o momento em que a lista deixou de ser um punhado de identificadores e se tornou discretamente um milhar.

Perguntas frequentes

Escapar é alguma vez um substituto aceitável da ligação?

Não, e a razão não é que as funções de escape estejam mal escritas: é que resolvem outro problema. A ligação move a fronteira entre código e dados de modo que um valor não tenha rota para a sintaxe. Escapar deixa a fronteira onde está e tenta neutralizar todo o valor que a possa atravessar, o que obriga a estar correto em cada contexto, sob cada codificação, em cada ponto de chamada, para sempre. Os contextos diferem mesmo: um literal de string, um literal numérico, um identificador, um padrão de LIKE e um valor dentro de uma cláusula montada dinamicamente precisam cada um de tratamento diferente, e alguns não têm escape seguro de todo. Um único ponto esquecido repõe o buraco inteiro, e os pontos esquecidos são o resultado normal de uma base de código com mais de um autor. Há um caso em que um valor verdadeiramente não pode ser um parâmetro, porque a base não aceita um marcador aí: um nome de tabela, um nome de coluna, um sentido de ordenação, um LIMIT nalguns motores. A resposta aí continua a não ser escapar. Valide o valor contra uma lista branca fixa dos identificadores que o seu esquema realmente contém, e mapeie a entrada do utilizador para uma constante em vez de a deixar passar.

O que faço quando a lista é maior do que a minha base permite?

Três opções, por ordem crescente de trabalho e de capacidade de escalar. Primeiro, um parâmetro de array ou com valores de tabela, se o seu motor o tiver: a lista inteira viaja como um só parâmetro, pelo que o teto deixa de se aplicar e o texto da instrução torna-se constante. É a resposta certa em PostgreSQL e em SQL Server, onde o limite dos 2 100 morde cedo. Segundo, uma tabela temporária: insira os identificadores por lotes e depois junte contra a tabela. O texto da instrução é constante seja qual for o tamanho da lista, o otimizador obtém informação real de cardinalidade, e não há teto nenhum — paga uma ida e volta e alguma escrita por isso. Terceiro, a divisão em blocos: reparta a lista, execute a consulta uma vez por bloco e funda os resultados na sua aplicação. Derive o tamanho do bloco do limite da própria base em vez de fixar um número, porque a mesma lista exige 2 idas e voltas em PostgreSQL e 48 em SQL Server. Ordene antes de dividir para que as novas tentativas sejam reproduzíveis, e ponha todo o lote numa transação se o resultado fundido tiver de representar um único momento coerente — caso contrário várias instruções separadas veem vários instantâneos separados, que é a origem do erro intermitente que ninguém consegue reproduzir.

Porque é que a consulta fica mais lenta à medida que a lista cresce, mesmo bem abaixo do limite?

Acumulam-se três efeitos, e vale a pena separá-los porque têm correções diferentes. O primeiro é a compilação: o texto da instrução muda a cada comprimento de lista, pelo que a cache de planos falha sempre e o otimizador volta a analisar e a planear do zero em cada chamada. Com 1 000 valores o texto já ronda os 5 926 bytes; com 10 000, 68 927. O tempo de otimização cresce com o número de predicados, portanto não é um sobrecusto constante. O segundo é a estratégia do otimizador. Uma lista IN é logicamente uma cadeia de OR, e passado certo comprimento o otimizador deixa de a tratar como um conjunto de procuras por índice e considera uma varredura — o MySQL documenta-o explicitamente para o otimizador de intervalos, que segue a memória que um método de acesso por intervalo consumiria e o abandona se um limite configurado fosse ultrapassado, caindo numa varredura completa de tabela. Atravessar esse limiar é um precipício, não um declive. O terceiro é a pressão de cache descrita acima: os seus mil planos de uso único expulsam os que outras consultas usavam, pelo que a lentidão aparece em consultas que não tocou. Um parâmetro de array ou uma tabela temporária ataca os três de uma vez, porque torna constante o texto da instrução e dá ao otimizador uma relação que pode juntar.

Usar um ORM significa que não tenho de pensar em nada disto?

Trata da metade de segurança e normalmente não da metade operacional. Todos os ORM correntes ligam valores em vez de os interpolar, pelo que o risco de injeção desaparece mesmo para os construtores de consultas e as instruções geradas — com a exceção de qualquer saída de emergência para SQL cru, onde volta a escrever os parâmetros e onde vivem esmagadoramente as vulnerabilidades das bases de código com ORM. A metade de desempenho é outra história. A maioria dos ORM desenha uma cláusula WHERE sobre uma coleção como uma lista IN literal com um marcador por elemento, que é exatamente o padrão que muda o texto da instrução a cada comprimento e inunda a cache de planos. Muitos geram também de bom grado uma lista maior do que a sua base permite e só falham na execução, e é por isso que o teto de 2 100 do SQL Server aparece como incidente de produção e não como falha de teste. O que verificar na sua própria pilha é concreto: se o seu ORM consegue emitir um parâmetro de array ou com valores de tabela em vez de uma lista IN, se divide automaticamente e com que tamanho de bloco, e se expõe o SQL gerado num registo legível. Se não fizer nada disso, uma chamada de carregamento antecipado sobre uma coleção grande é um problema de cache de planos à espera de uma segunda-feira.

Reformatar o meu SQL muda o seu desempenho?

O plano não, mas possivelmente a procura na cache, e essa distinção vale a pena reter. O otimizador trabalha sobre a árvore de análise, pelo que os espaços e as quebras de linha não têm efeito nenhum sobre o plano que escolhe — uma instrução formatada em vinte linhas e a mesma minificada numa só executam de forma idêntica. O que não partilham necessariamente é uma entrada de cache, porque a cache de planos é indexada pelo texto da instrução e os motores diferem em quanto o normalizam antes. A regra prática é pois simples: formate o SQL no seu repositório, onde uma pessoa o lê, e envie o que o seu controlador produzir sem um embelezador nem um minificador no caminho. Um invólucro que reformata de forma inconsistente — diferente sob carga, ou depois de alguém mudar um sinalizador de configuração — pode criar uma segunda entrada de cache para uma instrução de que o motor já tinha plano: uma regressão pequena, silenciosa e muito irritante. Formatar no repositório tem um segundo benefício que importa mais: uma lista IN longa repartida por linhas torna visível num relance se os valores estão ligados ou interpolados, e se a contagem é a que esperava.