在C#中构建动态查询(SQL注入攻击) [英] Building a dynamic query in C# (SQL Injection Attack)

问题描述

我有以下代码

public DataSet GetProject(string projectID)
{
   DataSet dataTable = new DataSet(); 
   DataAccess dataAccess = new DataAccess();
   OracleCommand commandOb = new OracleCommand();
   strQuery.Append("select projectName, managerName");
   strQuery.Append("from project ");
   strQuery.Append("where projectID = '" + projectID + "'");

   cmd.CommandText = strQuery.ToString();
   dataTable = dataAccess.ExecuteDataAdapter(commandOb);

   return dataTable;
}

这是建立查询并执行查询的一种好方法吗?这对SQL注入攻击很有用吗?

Is this an okay way to build a query and execute it? Would this be vulunerable to SQL injection attacks?

在动态构建查询时，是否存在推荐的方法.任何帮助将不胜感激.

Is there a recommended approach when it comes to building queries dynamically. Any help would be appreciated.

推荐答案

以这种方式构建查询确实使其容易受到SQL注入攻击，除非您手动将输入转义(IE使'projectID'的值不可能变为通过使用数据库引擎特定的转义序列来更改查询的结构).但是，推荐的方法是使用参数化查询(有时称为"Prepared Statements").使用参数化查询，您只需定义查询的结构，然后分别提供输入值作为参数，从而防止查询结构通过SQL注入而发生更改.

Building a query this way does make it vulnerable to SQL injection attacks unless you have manually escaped your input (I.E. Made it impossible for the value of 'projectID' to change the structure of the query by using database engine specific escape sequences). However, the recommended way to do this is using parameterized queries (sometimes called "Prepared Statements"). With parameterized queries, you simply define the structure of the query and then provide the input values separately as parameters, preventing the structure of your query from ever being changed via SQL injection.

这是您的示例，更改为使用参数化:

Here is your example, changed to use parameterization:

public DataSet GetProject(string projectID)
{
   DataSet dataTable = new DataSet(); 
   DataAccess dataAccess = new DataAccess();
   OracleCommand commandOb = new OracleCommand();
   strQuery = @"select projectName, managerName
                  from project
                  where projectID = :ProjectID"

   cmd.CommandText = strQuery;
   cmd.Parameters.AddWithValue("ProjectID", projectID);
   dataTable = dataAccess.ExecuteDataAdapter(commandOb);

   return dataTable;
}

查询中的参数':ProjectID'将替换为'AddWithValue'方法中给定的值.无论'projectID'变量中的值是什么，都将始终将其作为WHERE子句的一部分进行评估.之前的值类似于[';通过将查询更改为以下内容，可以从项目DELETE FROM project;-]中产生不良影响:

The parameter ':ProjectID' in the query will be replaced with the value given in the 'AddWithValue' method. No matter what value is in the 'projectID' variable, it will always be evaluated as part of the WHERE clause. Whereas, before, a value similar to ['; DELETE FROM project;--] could have undesirable affects by changing your query to read as follows:

select projectName, managerName
  from project
  where projectID = ''; DELETE FROM project;--'

这篇关于在C#中构建动态查询(SQL注入攻击)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！