C# 参数化查询 MySQL 与`in` 子句 [英] C# Parameterized Query MySQL with `in` clause

问题描述

我正在将几个硬编码到应用程序中并即时构建的查询转换为参数化查询.我遇到了一个带有 in 子句的特定查询的问题:

I am in the process of converting several queries which were hard-coded into the application and built on the fly to parameterized queries. I'm having trouble with one particular query, which has an in clause:

UPDATE TABLE_1 SET STATUS = 4 WHERE ID IN (1, 14, 145, 43);

第一个参数很简单，因为它只是一个普通参数:

The first parameter is easy, as it's just a normal parameter:

MySqlCommand m = new MySqlCommand("UPDATE TABLE_1 SET STATUS = ? WHERE ID IN (?);");
m.Parameters.Add(new MySqlParameter("", 2));

然而，第二个参数是一个整数列表，表示需要更新的行的 id.如何为单个参数传递整数列表?或者，您将如何设置此查询，以便您不必每次调用它时都完全构建它，并且可以防止 SQL 注入攻击?

However, the second parameter is a list of integers representing the ids of the rows that need updating. How do I pass in a list of integers for a single parameter? Alternatively, how would you go about setting up this query so that you don't have to completely build it each and every time you call it, and can prevent SQL injection attacks?

推荐答案

您可以基于(可能)可变数量的参数即时"构建参数化查询，并对其进行迭代以将它们传入.

You could build up the parametrised query "on the fly" based on the (presumably) variable number of parameters, and iterate over that to pass them in.

所以，例如:

List foo; // assuming you have a List of items, in reality, it may be a List<int> or a List<myObject> with an id property, etc.

StringBuilder query = new StringBuilder( "UPDATE TABLE_1 SET STATUS = ? WHERE ID IN ( ?")
for( int i = 1; i++; i < foo.Count )
{   // Bit naive 
    query.Append( ", ?" );
}

query.Append( " );" );

MySqlCommand m = new MySqlCommand(query.ToString());
for( int i = 1; i++; i < foo.Count )
{
    m.Parameters.Add(new MySqlParameter(...));
}

这篇关于C# 参数化查询 MySQL 与`in` 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！