segunda-feira, abril 13, 2015

Passar o nome de uma coluna ou tabela como parâmetro num prepared statement

Vou começar por dizer que o que o título diz é parvo, sim é parvo, não porque não dá jeito ou porque a ideia é parva, mas simplesmente porque não é possível fazer. Se quiser fazer algo como o que está em seguida é para esquecer não é possível fazer:

SELECT @column FROM @table

Agora há uma forma mais ou menos elegante de resolver este problema. O que queremos ao termos prepared statements é evitar sql injection, esse é o verdadeiro problema. Então a solução passa por validar se a tabela e a coluna existem na nossa base de dados, e assim até validamos se a query é válida.
       private void AssertValidTableAndColumnName(SqlConnection connection, 
                    SqlCommand cmd, string table, string column)
       {
           cmd.CommandText = "SELECT COUNT(*) FROM SYS.TABLES WHERE NAME = @table";
           SqlParameter tab = new SqlParameter("table", SqlDbType.NVarChar, 500);
           tab.Value = table;
           cmd.Parameters.Add(tab);

           cmd.Prepare();
           int count = (int)(cmd.ExecuteScalar());

           if (count == 0)
           {
               throw new ArgumentException("Invalid table name", table);
           }
           
           cmd.CommandText = @"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                                WHERE  TABLE_NAME = @table
                                AND COLUMN_NAME = @column";
           SqlParameter col = new SqlParameter("column", SqlDbType.NVarChar, 500);
           col.Value = column;
           cmd.Parameters.Add(col);

           cmd.Prepare();
           count = (int)(cmd.ExecuteScalar());

           if (count == 0)
           {
               throw new ArgumentException("Invalid column name", column);
           }
       }

Temos assim a garantia que tanto a tabela como a coluna existem, se não existirem a aplicação enviará uma excepção. No meu caso quis mesmo que a aplicação terminasse, mas podemos não ser tão radicais e simplesmente retornar um booleano indicando o sucesso. Basta agora fazer uma query normal sem haver a preocupação que haja sql injection.

[...]
AssertValidTableAndColumnName(connection, cmd, table, column);
cmd.CommandText = string.Format("SELECT id,{1} FROM {0}", table, column);
[...]

3 comentários:

  1. Não sei se percebi: Se a coluna não existe, vai dar excepção e aí está covered.

    Quando ao injection, ficas então sabendo que só campos que existem na tabela é que podem ser requisitados. Mas isso permite à mesma sql injection.

    Posso passar como table e user o que eu quiser, desde que sejam válidos. Tipo:

    table: "users"
    column: "password"

    Ou tá-me a falhar algo?

    ResponderEliminar
  2. A partir do momento que disponibilizas uma função cujo output é o resultado de um select a uma qualquer coluna de uma qualquer tabela habilitas-te a que isso aconteça...se não encriptares as colunas sensíveis, ou então teres uma black list de colunas/tabelas que não possam ser passadas para fora.

    Posso-te dizer que no meu caso isto está dentro de uma blackbox cujo o utilizador passa nome da tabela e coluna e recebe que a aplicação foi executada com sucesso ou não. Neste caso em particular eu só quero evitar que o utilizador me passe um comando que comprometa a integridade da minha base de dados, porque a nível de output o utilizador nada saberá sobre esse select.

    ResponderEliminar
  3. Estou com o Pedro, não sei se percebi... o facto de validar a existência ou não da tabela e coluna ou sequer do utilizador não se aperceber do output não te protege de ser passado sql "malicioso" que te comprometa a BD.

    Tratar de alguma forma a informação inserida e usar sqlparameters já é um bom passo. Mas caramba... estamos em 2015, ORMs?! Entity Framework,NHibernate... boa?! :P

    ResponderEliminar