如何复杂的参数化查询OLEDB？ [英] How to parameterize complex OleDB queries?

问题描述

我试图重构正在使用字符串拼接来创建SQL命令（这使得它脆弱的SQL注入）的一些代码。基本上所有我想要做的是，以取代所有的字符串sqlToExecute =的String.Format（..）用SQL命令和OLEDB参数列表语句。

I'm trying to refactor some code that is using strings concatenation for creating SQL commands (which makes it vulnerable for a SQL injection). Basically all I'm trying to do is to replace all the string sqlToExecute = String.Format(..) statements with a SQL command and a List of OleDB parameters.

我理解这种简单的情况下做过类似的String.Format（SELECT * FROM myTable的其中id = {0}，ID ）。但是，我找不到了一套更加复杂的SQL查询很好的例子。

I understand how this can be done for simple cases like String.Format("Select * from myTable where id = {0}", id). However, I could not find a set of good examples for more complex SQL queries.

下面是一些我不知道我究竟可以参数化查询：

Here are some of the queries that I'm not sure how exactly I can parameterize:

1。参数用于为列名和别名;参数包括两个变量：

    selQueryBldr.AppendFormat("SELECT * FROM {0} {1} 
    INNER JOIN ColChange CC ON CC.TableRecordID = {1}.{2} and CC.EntityID='{3}'",
    entity.StageTableName, stageTableAlias, entity.PrimaryKey, entity.EntityID);

2。相同的参数在多个SQL使用IN子句

SQL查询：

      SELECT A.TablePrefix ...
      FROM Entity E
      INNER JOIN App A
      ON A.AppID = E.AppID
      WHERE E.AppID in (#APPIDS#)

      UNION

      SELECT A.TablePrefix ...
      FROM EntityB EB
      INNER JOIN App A
      ON A.AppID = EB.AppID
      WHERE EB.AppID in (#APPIDS#)

目前的参数在代码中使用与string.replace（）方法中加入：

Currently the parameter is added in the code by using String.Replace() method:

    sqlQuery = sqlQuery.Replace("#APPIDS#",idList);

3。使用变量作为参数名称和参数值：

    StringBuilder dataQuery = new StringBuilder("Select * from {0} WHERE {1}='{2}'",
    tableName, primaryKey[0], changeRow["TableRecordID"]);

4。变量使用unicode的参数的一部分：

    sSQL = string.Format("SELECT name FROM sysobjects WHERE id = object_id(N'[dbo].[{0}]')",
    sSPName);

此外，所有这些例子都使用的OleDb类（的OleDbConnection / OleDbCommand的等），因此，据我了解命名参数不能在这里使用。

Also, all of these examples are using OleDb classes (OleDbConnection/OleDbCommand etc.), thus as far as I understand named parameters can not be used here.

推荐答案

不同的后端允许（或​​没有）无论是命名参数，或？占位符的参数，所以你会做的是构建查询类似

Different back-ends allow (or not) either named parameters, or "?" place-holders for parameters, so what you would do is build your query something like

OleDbCommand oCmd = new OleDbCommand( YourConnection, "select * from someTable where yourColumn = ? and otherColumn = ?" );

oCmd.Parameters.AddWithValue( "parm1", YourVariable.FormattedHoweverNeeded );
oCmd.Parameters.AddWithValue( "parm2", anotherVariable.FormattedHoweverNeeded );

如果列期待弦，确保字符串。如果预期数字（INT，双，浮法，等等），留下作为类型来说，或其他（日期/时间等）

If the columns are expecting strings, ensure a string. If expecting numeric (int, double, float, etc), leave as that type too, or other (date/time, etc)

只要注意...如果不是做命名参数（如我有？占位符），参数必须在同一顺序的？被添加放置在SQL命令

Just note... if not doing named parameters (as I have with "?" place-holder), the parameters must be added in the same sequence as the "?" are placed in the SQL command.

这篇关于如何复杂的参数化查询OLEDB？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！