如何使用列表中的列名进入mysql插入 [英] How to use column names from list into mysql insert

问题描述

我有2个列表，想在mysql插入参数中使用它们：



我尝试了什么：

列表< string> ColumnNames =  new  List< string>（）; 
 List< string> ValueNames =  new  List< string>（）; 
 ColumnNames = names.Keys.ToList（）; 
 ValueNames = names.Values.ToList（）; 

我正在尝试将此列表用于MySql语句：

< pre lang =c＃> ...
MySqlCommand command = conDataBase.CreateCommand（）;
command.CommandText = string .Format（ 插入MyTable {0}值（？参数）， string .Join（ ，，ColumnNames））;
command.Parameters.AddWithValue（ 参数， string .Join（ ，，ValueNames））;
...

但我正在接受语法错误，然后我尝试了很多实验，例如：

 ... 
 command.CommandText =  插入MyTable（ +  string  .Join（ ， ，ColumnNames）+  ）values（' +  string  .Join（ ，，ValueNames）+  '）; 
 ... 

和

 ... 
 command.CommandText =  插入MyTable（ +  string  .Join（ ，，ColumnNames）+  ）values（？parameter）; 
 command.Parameters.AddWithValue（ 参数， string  .Join（ ，，ValueNames））; 
 ... 

我收到错误列数与第1行的值计数不匹配，但是

 ColumnNames.Count = ValueNames.Count 

解决方案

试试这个：

 List< string> ColumnNames = names.Keys.ToList（）
 List< string> ValueNames = names.Values.ToList（）; 
 List< string> RevValueNames = new List< string>（）; 
 ValueNames.ForEach（x => RevValueNames.Add（'+ x +'））; 
 string Query = string.Format（insert into MyTable（{0}）values（{1}），string.Join（，，ColumnNames），string.Join（，，RevValueNames）） ; 
 command.CommandText = Query; 

NB：确保用户无法提供您的列名，否则您将自己开放 SQL注入 [< a href =https://www.troyhunt.com/2013/07/everything-you-wanted-to-know-about-sql.html\"target =_ blank> ^ ]。



这样的事情应该有效：

 command.CommandText =  string  .Format（  INSERT INTO MyTable（{0}）VALUES（？{1}），
  string  .Join（  ，，names.Keys），
  string  .Join（ < span class =code-string>，？，names.Keys））; 
 
  //  例如：INSERT INTO MyTable（col1，col2）VALUES（？col1， ？col2） 
 
  foreach （KeyValuePair< string，string> pair  in 名称）
 {
 command.Parameters.AddWithValue（pair.Key，pair.Value）; 
} 

---

你想知道关于SQL注入的一切（但不敢问）|特洛伊亨特 [ ^ ]

如何在没有技术术语的情况下解释SQL注入？ |信息安全堆栈交换 [ ^ ]

查询参数化备忘单| OWASP [ ^ ]

I have 2 lists and want to use them in mysql insert parameter:

What I have tried:

List<string> ColumnNames = new List<string>();
List<string> ValueNames = new List<string>();
ColumnNames = names.Keys.ToList();
ValueNames = names.Values.ToList();

I'm trying to use this lists into MySql statement:

...
MySqlCommand command = conDataBase.CreateCommand();
command.CommandText = string.Format("insert into MyTable {0} values (?parameter)", string.Join(",", ColumnNames));
command.Parameters.AddWithValue("parameter", string.Join(",", ValueNames));
...

but I'm taking syntax error, then I have tryed many experiments, like:

...
command.CommandText = "insert into MyTable (" + string.Join(",", ColumnNames) + ") values ('" + string.Join(",", ValueNames) + "')";
...

and

...
command.CommandText = "insert into MyTable (" + string.Join(",", ColumnNames) + ") values (?parameter)";
command.Parameters.AddWithValue("parameter", string.Join(",", ValueNames));
...

I'm taking error "Column count doesn't match value count at row 1", but

ColumnNames.Count = ValueNames.Count

解决方案

Try This :

 List<string> ColumnNames = names.Keys.ToList()
      List<string> ValueNames = names.Values.ToList();
      List<string> RevValueNames = new List<string>();
      ValueNames.ForEach(x => RevValueNames.Add("'" + x + "'"));
string Query = string.Format("insert into MyTable ({0}) values ({1})", string.Join(",", ColumnNames), string.Join(",", RevValueNames));
            command.CommandText = Query;

NB: Make sure your column names cannot be provided by the user, otherwise you will leave yourself open to SQL Injection[^].

Something like this should work:

command.CommandText = string.Format("INSERT INTO MyTable ({0}) VALUES (?{1})", 
    string.Join(",", names.Keys), 
    string.Join(",?", names.Keys));

// Eg: "INSERT INTO MyTable (col1, col2) VALUES (?col1, ?col2)"

foreach (KeyValuePair<string, string> pair in names)
{
    command.Parameters.AddWithValue(pair.Key, pair.Value);
}

---

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

这篇关于如何使用列表中的列名进入mysql插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！