使用String Builder在SQL SERVER中插入问题 [英] Problem in Insertion in SQL SERVER using String Builder

问题描述

我无法使用以下代码插入数据库：

I am unable to insert in database using the following code :

public static int Insert(string query)
       {
           int i=0;
           try
           {
               DBConnection.InitializeConnection(cmd);
               cmd.CommandText = query;
               cmd.CommandType = CommandType.Text;
               if (cmd.Connection.State != ConnectionState.Open)
               {
                   cmd.Connection.Open();
                   i = cmd.ExecuteNonQuery();
               }
           }
           catch (Exception)
           {
               i = 0;
           }
           finally
           {
               if (cmd.Connection.State != ConnectionState.Closed)
               {
                   cmd.Connection.Close();
               }
          }
           return i;
       }

try
{
query.Append("INSERT INTO Suppliers ");
query.Append("(SupName,ConcernedPerson,MobileNum,PostalAddr,Email) ");
query.Append("VALUES("+txtSupplier.Text.ToString());
query.Append(","+txtConcerned.Text.ToString());
query.Append(","+Convert.ToInt64(txtMobile.Text));
query.Append(","+txtAddress.Text.ToString());
query.Append(","+txtEmail.Text.ToString()+");");
int i=POS.DbOperation.Insert(query.ToString());
MessageBox.Show(i.ToString());
}
catch(Exception ex)
{
MessageBox.Show(ex+""+ex.InnerException);
}

这种方法是否适合插入任何其他方法？

谢谢，

Hritik

Is this method perfect to do insertion over any other method?
Thanks,
Hritik

推荐答案

NO！

它是开放的SQL注入攻击，可以破坏或破坏您的数据库。请改用参数化查询！

NO!
It's wide open to SQL Injection attack which can damage or destroy your database. Use a parametrised query instead!

这不是将数据插入数据库的好方法，因为它非常容易受到攻击，特别是对于SQL注入攻击。



但是，为了让你的代码能够工作，你的所有字符串都必须在单引号内，看起来你的代码似乎并非如此。



如果您将其更改为：

This is not a good approach to inserting data into a database as it is highly vulnerable, especially to SQL Injection attacks.

However, to get your code to work all of your string need to be within single quotes, which looking at your code appears not to be the case.

If you changed it to:

query.Append("INSERT INTO Suppliers ");
query.Append("(SupName,ConcernedPerson,MobileNum,PostalAddr,Email) ");
query.Append("VALUES('"+txtSupplier.Text.ToString());
query.Append("','"+txtConcerned.Text.ToString());
query.Append("','"+Convert.ToInt64(txtMobile.Text));
query.Append("','"+txtAddress.Text.ToString());
query.Append("','"+txtEmail.Text.ToString()+"');");

那么它应该可以工作。



但是我建议你看看使用参数。



如果您更改了Insert方法以获得此定义：

Then it should work.

However I would suggest you look at using Parameters.

If you changed your Insert method to have this definition:

public static int Insert(string query, Dictionary<string,> parameters)

然后，而不是如何生成您的查询字符串，您可以：

Then instead of how you generate your query string you could have:

var insertQuery = @"INSERT INTO Suppliers (SupName, ConcernedPerson, MobileNum, PostalAddr, Email) Values (@name, @concernedPerson, @mobile, @addr, @email)

然后按如下方式创建参数字典：

Then create you parameters dictionary as follows:

var paramDic = new Dictionary<string,>();
paramDic.Add("@name", txtSupplier.Text); //Repeat this line for each parameter

调用新的插入方法

插入（insertQuery，paramDic）



然后你需要调整你的插入命令，如下所示：

Call your new insert method as
Insert(insertQuery, paramDic)

Then you need to tweak your insert command as follows:

cmd.CommandText = query;
foreach(var p in parameters){
cmd.Parameters.AddWithValue(p.Key, p.Value);
}

您应该查看 DbCommand [ ^ ]和 DbParameter [ ^ ]。

比使用它更安全串联字符串。



配置参数和参数数据类型 [ ^ ]

You should look into DbCommand[^] and DbParameter[^] instead.
It is much safer than using concatenated strings.

Configuring Parameters and Parameter Data Types[^]

这篇关于使用String Builder在SQL SERVER中插入问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！