ADO.NET: Conexões, Comandos Parametrizados e o Perigo do SQL Injection

Montar um comando SQL colando o que o usuário digitou é entregar a ele o teclado do banco de dados; os parâmetros existem para que dados nunca sejam confundidos com código.

Em "SQL Essencial com SQLite" aprendemos a criar tabelas, inserir, consultar e agregar dados digitando cada comando no terminal. A Livraria, porém, é um programa C#, e é ele quem precisa conversar com o banco. A forma mais direta de fazer isso no .NET é o ADO.NET, o conjunto de classes que existe desde o .NET Framework 1.0 e sobre o qual todas as ferramentas mais sofisticadas, como o Dapper e o Entity Framework, são construídas. Nesta aula escrevemos SQL dentro do C#, lemos os resultados, fazemos transações e, sobretudo, aprendemos a nunca cometer o erro que fez do SQL injection uma das falhas de segurança mais exploradas de todos os tempos.

As peças do ADO.NET

O ADO.NET define um conjunto de classes abstratas, no namespace System.Data.Common, que todo banco de dados implementa:

• DbConnection: a conexão com o banco, aberta e fechada explicitamente;
• DbCommand: um comando SQL a executar, com seus parâmetros;
• DbDataReader: um cursor que percorre as linhas de um resultado, uma por vez;
• DbParameter: um valor enviado junto com o comando;
• DbTransaction: uma transação.

Cada banco tem um provedor que implementa essas classes: Microsoft.Data.Sqlite para o SQLite, Npgsql para o PostgreSQL, MySqlConnector para o MySQL e Microsoft.Data.SqlClient para o SQL Server. Aprender o ADO.NET com um deles é aprender com todos: as classes mudam de prefixo (SqliteConnection, NpgsqlConnection), mas o modelo é o mesmo.

Para o SQLite:

dotnet add src/Livraria.Dados package Microsoft.Data.Sqlite

Esse pacote traz também a biblioteca nativa do SQLite, compilada para cada plataforma, e a camada de P/Invoke que a chama. É exatamente o tipo de ponte que construímos à mão em "Interop com C", pronta e testada. A partir desta aula, o acesso a dados mora num projeto próprio, Livraria.Dados, que referencia o domínio. É a arquitetura em camadas de "Do Arquivo Único à Solução": o domínio continua sem saber que existe um banco.

Conexões

Uma conexão é descrita por uma cadeia de conexão (connection string), um texto com pares chave=valor:

using Microsoft.Data.Sqlite;

const string CadeiaDeConexao = "Data Source=livraria.db;Foreign Keys=True";

await using var conexao = new SqliteConnection(CadeiaDeConexao);
await conexao.OpenAsync();

Foreign Keys=True é a forma de ligar a verificação de chaves estrangeiras em toda conexão, resolvendo de uma vez a armadilha do PRAGMA de "SQL Essencial com SQLite". Uma conexão é um recurso externo, IDisposable e IAsyncDisposable, e o await using de "async e await" garante que ela seja fechada. Nos bancos com servidor, cada conexão aberta ocupa recursos no servidor, e esquecer de fechá-las é um dos vazamentos mais comuns em sistemas de produção.

Abrir uma conexão a cada operação pode parecer caro, mas os provedores mantêm um pool de conexões: ao "fechar" uma conexão, ela volta ao pool, e a próxima abertura a reaproveita. O padrão recomendado é justamente abrir tarde e fechar cedo: uma conexão por operação, dentro de um using.

Executando comandos

Um comando é criado a partir da conexão, recebe o texto SQL e é executado de uma de três formas, conforme o que se espera de volta.

ExecuteNonQuery, para comandos que não devolvem linhas, como CREATE, INSERT, UPDATE e DELETE. Ele devolve o número de linhas afetadas:

await using var comando = conexao.CreateCommand();
comando.CommandText = "UPDATE produtos SET estoque = estoque + 5 WHERE id = 6";
int afetadas = await comando.ExecuteNonQueryAsync();

ExecuteScalar, para consultas que devolvem um único valor:

comando.CommandText = "SELECT COUNT(*) FROM produtos";
long total = (long)(await comando.ExecuteScalarAsync())!;

O resultado chega como object, porque o provedor não sabe de antemão o tipo. O SQLite devolve inteiros como long. Quando a consulta pode não ter resultado (um SUM sobre nenhuma linha, por exemplo), o valor devolvido é DBNull.Value, a representação do NULL do banco no ADO.NET, e não o null do C#. É preciso tratar os dois casos.

ExecuteReader, para consultas que devolvem linhas. O DbDataReader é um cursor que avança uma linha por vez, como o IEnumerator de "Sequências Sob Demanda":

comando.CommandText = "SELECT id, titulo, preco_centavos, estoque FROM produtos ORDER BY titulo";
await using var leitor = await comando.ExecuteReaderAsync();

while (await leitor.ReadAsync())
{
    int id = leitor.GetInt32(0);
    string titulo = leitor.GetString(1);
    decimal preco = leitor.GetInt64(2) / 100m;
    int? estoque = leitor.IsDBNull(3) ? null : leitor.GetInt32(3);

    Console.WriteLine($"#{id} {titulo,-35} {preco,8:C} {estoque?.ToString() ?? "digital"}");
}

Os métodos GetInt32, GetString e seus irmãos leem a coluna pela posição, contada a partir de zero na lista do SELECT. Colunas que podem ser nulas precisam de IsDBNull antes da leitura: GetInt32 numa coluna nula lança exceção. É a fronteira entre o NULL do SQL e o int? de "Nullable Reference Types", e o ADO.NET a deixa por sua conta. Para não depender da posição, leitor.GetOrdinal("titulo") descobre o índice de uma coluna pelo nome.

Uma nota honesta sobre os métodos assíncronos neste provedor específico: o SQLite roda dentro do processo e grava num arquivo local, sem espera de rede, e o Microsoft.Data.Sqlite implementa os métodos Async executando o trabalho de forma síncrona. Usamos a versão assíncrona assim mesmo, porque o código fica pronto para PostgreSQL e MySQL, cujos provedores fazem entrada e saída de rede de verdade e se beneficiam integralmente de "async e await".

O perigo: montar SQL com texto do usuário

Imagine uma busca de livros pelo título, digitado pelo cliente. A forma ingênua de escrevê-la é colar o texto no comando:

// NUNCA faça isto
string busca = Console.ReadLine() ?? "";
comando.CommandText = "SELECT titulo, preco_centavos FROM produtos WHERE titulo = '" + busca + "'";

Com a entrada Vidas Secas, o comando vira ... WHERE titulo = 'Vidas Secas' e funciona. Agora observe o que acontece com esta entrada:

' OR '1'='1

O comando resultante é:

SELECT titulo, preco_centavos FROM produtos WHERE titulo = '' OR '1'='1'

A aspa digitada pelo usuário fechou a string do SQL, e o resto da entrada passou a ser interpretado como código. '1'='1' é sempre verdadeiro, e a consulta devolve todos os produtos. Numa tela de login, uma entrada parecida dá acesso sem senha. Com outras construções, dependendo do banco e do provedor, é possível ler tabelas inteiras de outros clientes, alterar preços ou apagar dados. Esse ataque se chama SQL injection. Ele é conhecido desde o fim dos anos 1990 e continua aparecendo nas listas das vulnerabilidades mais exploradas, porque a tentação de montar SQL com texto nunca some. Ele é tão famoso que virou até tirinha: a xkcd nº 327 conta a história de uma mãe que batizou o filho de Robert'); DROP TABLE Students;-- e apagou o cadastro da escola.

"Mas eu posso escapar as aspas, duplicando-as." Não faça isso. Escapar à mão é frágil: depende de cada banco, de cada contexto (texto, número, nome de coluna) e de nunca esquecer nenhum ponto do sistema. É exatamente o princípio que perseguimos desde o Markup.Escape de "Do Arquivo Único à Solução" e o escape do CSV de "Reflexão e Atributos": dados nunca devem ser interpretados como estrutura. No SQL, a ferramenta que garante isso tem nome próprio.

Parâmetros: a defesa definitiva

Um comando parametrizado separa o código SQL dos valores. O texto do comando contém marcadores, e os valores são enviados à parte:

comando.CommandText = "SELECT titulo, preco_centavos FROM produtos WHERE titulo = $titulo";
comando.Parameters.AddWithValue("$titulo", busca);

O banco recebe o comando e o valor por canais separados. Ele analisa o SQL antes de conhecer o valor, e o valor é tratado sempre como dado, nunca como parte do código. Com a entrada ' OR '1'='1, o banco procura um livro cujo título seja literalmente ' OR '1'='1, e não encontra nenhum. Não há escape a esquecer, porque não há nada a escapar.

O SQLite aceita marcadores com $, @ ou :. O PostgreSQL, via Npgsql, e o SQL Server usam @. Os parâmetros trazem ainda dois benefícios. Desempenho: o banco pode reaproveitar o plano de execução de um comando que muda só nos valores. Corretude: datas, números decimais e textos com acentos são enviados no formato nativo, sem as armadilhas de cultura e de formatação de "Operadores, Texto e Conversa com o Usuário".

Há um limite importante: parâmetros representam valores, e não partes da estrutura do SQL. Nomes de tabelas, de colunas e a direção de uma ordenação não podem ser parâmetros. Quando essas partes dependem da escolha do usuário, como numa ordenação escolhida numa tela, use uma lista fechada de opções conhecidas, nunca o texto recebido:

string colunaDeOrdem = ordemEscolhida switch
{
    "titulo" => "titulo",
    "preco"  => "preco_centavos",
    _        => "id"
};
comando.CommandText = $"SELECT titulo, preco_centavos FROM produtos ORDER BY {colunaDeOrdem}";

A interpolação aqui é segura porque o valor inserido só pode ser um dos três textos escritos pelo próprio programador. O que o usuário digitou nunca chega ao SQL.

Transações no ADO.NET

A transação de "SQL Essencial com SQLite", que baixa o estoque e registra a venda juntos, fica assim em C#:

await using var transacao = conexao.BeginTransaction();

await using (var baixa = conexao.CreateCommand())
{
    baixa.Transaction = transacao;
    baixa.CommandText = "UPDATE produtos SET estoque = estoque - $q WHERE id = $id";
    baixa.Parameters.AddWithValue("$q", quantidade);
    baixa.Parameters.AddWithValue("$id", produtoId);
    await baixa.ExecuteNonQueryAsync();
}

// ... o INSERT da venda, também com Transaction = transacao ...

await transacao.CommitAsync();

Dois detalhes. Cada comando executado dentro da transação deve ter a propriedade Transaction apontando para ela. No Microsoft.Data.Sqlite a atribuição é redundante: o CreateCommand já associa o comando à transação ativa da conexão, e um UPDATE feito sem ela é desfeito pelo rollback do mesmo jeito. Mas outros provedores, como o Microsoft.Data.SqlClient do SQL Server, recusam com exceção um comando sem ela, e escrevê-la deixa o código pronto para trocar de banco. E, se o método terminar sem chegar ao CommitAsync, por causa de uma exceção por exemplo, o await using descarta a transação, e o descarte sem confirmação equivale a um rollback. Não é preciso escrever um catch só para desfazer: a combinação de using e Commit no fim do caminho feliz já garante o "tudo ou nada".

Juntando tudo: uma camada de dados para a Livraria

Uma classe que encapsula o acesso ao banco, com uma consulta filtrada e o registro de uma venda à prova de corridas:

using Microsoft.Data.Sqlite;

namespace Livraria.Dados;

public sealed record LinhaProduto(int Id, string Tipo, string Titulo, string Autor, decimal Preco, int? Estoque);

public sealed class BancoLivraria(string cadeiaDeConexao)
{
    public async Task<IReadOnlyList<LinhaProduto>> ListarDisponiveisAsync(
        decimal? precoMaximo = null, CancellationToken ct = default)
    {
        await using var conexao = new SqliteConnection(cadeiaDeConexao);
        await conexao.OpenAsync(ct);

        await using var comando = conexao.CreateCommand();
        comando.CommandText = """
            SELECT p.id, p.tipo, p.titulo, a.nome, p.preco_centavos, p.estoque
            FROM produtos p
            JOIN autores a ON a.id = p.autor_id
            WHERE (p.estoque IS NULL OR p.estoque > 0)
              AND ($precoMaximo IS NULL OR p.preco_centavos <= $precoMaximo)
            ORDER BY p.preco_centavos
            """;
        comando.Parameters.AddWithValue("$precoMaximo",
            precoMaximo is null ? DBNull.Value : (long)Math.Round(precoMaximo.Value * 100));

        var produtos = new List<LinhaProduto>();
        await using var leitor = await comando.ExecuteReaderAsync(ct);
        while (await leitor.ReadAsync(ct))
        {
            produtos.Add(new LinhaProduto(
                Id: leitor.GetInt32(0),
                Tipo: leitor.GetString(1),
                Titulo: leitor.GetString(2),
                Autor: leitor.GetString(3),
                Preco: leitor.GetInt64(4) / 100m,
                Estoque: leitor.IsDBNull(5) ? null : leitor.GetInt32(5)));
        }
        return produtos;
    }

    public async Task RegistrarVendaAsync(int produtoId, int quantidade, DateOnly data, CancellationToken ct = default)
    {
        ArgumentOutOfRangeException.ThrowIfNegativeOrZero(quantidade);

        await using var conexao = new SqliteConnection(cadeiaDeConexao);
        await conexao.OpenAsync(ct);
        await using var transacao = conexao.BeginTransaction();

        await using (var baixa = conexao.CreateCommand())
        {
            baixa.Transaction = transacao;
            baixa.CommandText = """
                UPDATE produtos
                SET estoque = estoque - $quantidade
                WHERE id = $id AND (estoque IS NULL OR estoque >= $quantidade)
                """;
            baixa.Parameters.AddWithValue("$quantidade", quantidade);
            baixa.Parameters.AddWithValue("$id", produtoId);

            if (await baixa.ExecuteNonQueryAsync(ct) == 0)
                throw new InvalidOperationException(
                    $"Produto {produtoId} inexistente ou sem estoque para {quantidade} unidade(s).");
        }

        await using (var registro = conexao.CreateCommand())
        {
            registro.Transaction = transacao;
            registro.CommandText = "INSERT INTO vendas (produto_id, quantidade, data) VALUES ($id, $quantidade, $data)";
            registro.Parameters.AddWithValue("$id", produtoId);
            registro.Parameters.AddWithValue("$quantidade", quantidade);
            registro.Parameters.AddWithValue("$data", data.ToString("yyyy-MM-dd"));
            await registro.ExecuteNonQueryAsync(ct);
        }

        await transacao.CommitAsync(ct);
    }
}

Três decisões merecem destaque.

Um filtro opcional com um único comando. A condição $precoMaximo IS NULL OR p.preco_centavos <= $precoMaximo faz o filtro valer só quando o parâmetro tem valor. DBNull.Value é como se envia um NULL ao banco. O preço máximo é convertido para centavos com arredondamento, pela convenção de dinheiro da aula anterior.

A venda à prova de corridas. Em "Concorrência" protegemos a venda com um lock, o que só funciona dentro de um único processo. Com dois caixas em computadores diferentes, a trava de um não existe para o outro. A condição estoque >= $quantidade dentro do UPDATE faz o banco verificar e alterar numa operação indivisível. Se duas vendas disputarem o último exemplar, uma delas encontra o estoque já baixado, e o UPDATE não afeta nenhuma linha. O número de linhas afetadas diz se a venda pode prosseguir. Não existe janela entre "consultar" e "alterar", porque não existe consulta separada.

O tudo ou nada. Se a baixa falhar, a exceção sai antes do INSERT, e o descarte da transação desfaz tudo. Se o INSERT falhar (uma chave estrangeira inválida, por exemplo), a baixa de estoque também é desfeita.

O uso:

var banco = new BancoLivraria("Data Source=livraria.db;Foreign Keys=True");

foreach (var p in await banco.ListarDisponiveisAsync(precoMaximo: 40m))
    Console.WriteLine($"{p.Titulo,-25} {p.Autor,-20} {p.Preco,8:C}");

try
{
    await banco.RegistrarVendaAsync(produtoId: 6, quantidade: 3, data: DateOnly.FromDateTime(DateTime.Today));
}
catch (InvalidOperationException ex)
{
    Console.WriteLine($"Venda recusada: {ex.Message}");   // Grande Sertão tem só 2 exemplares
}

Olhe de novo para o método ListarDisponiveisAsync. Das trinta linhas, só o SELECT expressa a pergunta de negócio. Todo o resto é cerimônia: abrir a conexão, criar o comando, adicionar parâmetros um a um, percorrer o leitor, lembrar a posição de cada coluna, testar IsDBNull, converter centavos e montar o record campo a campo. Multiplique isso por cinquenta consultas e o código de acesso a dados vira o maior e mais repetitivo trecho do sistema, com um erro de posição de coluna esperando em cada canto. Existe uma biblioteca pequena, criada pela equipe do Stack Overflow, que elimina quase toda essa cerimônia sem esconder uma linha sequer do SQL.

Fontes e leituras recomendadas

• Visão geral do Microsoft.Data.Sqlite (Microsoft Learn): https://learn.microsoft.com/dotnet/standard/data/sqlite/. É a documentação do provedor, com conexões, comandos, parâmetros e tipos.
• Transações no Microsoft.Data.Sqlite: https://learn.microsoft.com/dotnet/standard/data/sqlite/transactions. Explica o uso de transações e os níveis de isolamento suportados.
• Limitações assíncronas do Microsoft.Data.Sqlite: https://learn.microsoft.com/dotnet/standard/data/sqlite/async. Explica por que os métodos assíncronos deste provedor executam de forma síncrona.
• Visão geral do ADO.NET: https://learn.microsoft.com/dotnet/framework/data/adonet/ado-net-overview. Apresenta a arquitetura de provedores, conexões, comandos e leitores.
• OWASP, "SQL Injection Prevention Cheat Sheet": https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html. É o guia de referência sobre como prevenir SQL injection em qualquer linguagem.
• xkcd nº 327, "Exploits of a Mom": https://xkcd.com/327/. É a tirinha que tornou o SQL injection conhecido até fora da área de tecnologia.

Exercícios

Exercício 1

Escreva um método que devolva, com um ExecuteReader, a quantidade de produtos de cada tipo, num Dictionary<string, int>.

static async Task<Dictionary<string, int>> ContarPorTipoAsync(string cadeia, CancellationToken ct = default)
{
    await using var conexao = new SqliteConnection(cadeia);
    await conexao.OpenAsync(ct);

    await using var comando = conexao.CreateCommand();
    comando.CommandText = "SELECT tipo, COUNT(*) FROM produtos GROUP BY tipo";

    var contagem = new Dictionary<string, int>();
    await using var leitor = await comando.ExecuteReaderAsync(ct);
    while (await leitor.ReadAsync(ct))
        contagem[leitor.GetString(0)] = leitor.GetInt32(1);

    return contagem;
}

Exercício 2

Escreva um método que devolva o faturamento total em reais com ExecuteScalar. O que acontece se a tabela de vendas estiver vazia, e como tratar?

static async Task<decimal> FaturamentoTotalAsync(string cadeia, CancellationToken ct = default)
{
    await using var conexao = new SqliteConnection(cadeia);
    await conexao.OpenAsync(ct);

    await using var comando = conexao.CreateCommand();
    comando.CommandText = """
        SELECT SUM(v.quantidade * p.preco_centavos)
        FROM vendas v JOIN produtos p ON p.id = v.produto_id
        """;

    object? resultado = await comando.ExecuteScalarAsync(ct);
    return resultado is null or DBNull ? 0m : (long)resultado / 100m;
}

Exercício 3

O método abaixo tem duas vulnerabilidades de injeção. Aponte-as e corrija o método.

static string MontarBusca(string autor, string ordenarPor) =>
    $"SELECT titulo FROM produtos p JOIN autores a ON a.id = p.autor_id " +
    $"WHERE a.nome LIKE '%{autor}%' ORDER BY {ordenarPor}";

static void ConfigurarBusca(SqliteCommand comando, string autor, string ordenarPor)
{
    string coluna = ordenarPor switch
    {
        "titulo" => "p.titulo",
        "preco"  => "p.preco_centavos",
        _        => "p.id"
    };

    comando.CommandText = $"""
        SELECT p.titulo
        FROM produtos p JOIN autores a ON a.id = p.autor_id
        WHERE a.nome LIKE '%' || $autor || '%'
        ORDER BY {coluna}
        """;
    comando.Parameters.AddWithValue("$autor", autor);
}

Exercício 4

Explique o que aconteceria, sem a transação, se o programa fosse encerrado entre o UPDATE do estoque e o INSERT da venda em RegistrarVendaAsync. E com a transação?

Exercício 5

Dois caixas tentam vender, ao mesmo tempo, o último exemplar de Grande Sertão. Compare o que aconteceria com a implementação da aula e com esta alternativa:

// Alternativa: consulta, depois altera
long estoque = (long)(await Escalar("SELECT estoque FROM produtos WHERE id = 6"))!;
if (estoque >= 1)
    await Executar("UPDATE produtos SET estoque = estoque - 1 WHERE id = 6");