附加字符串以匹配SQL添加的值 [英] Appending a string to match with SQL added values

问题描述

我正在尝试根据列名添加sql值。因此，当一个人选择一个特定的表时，添加的值将与该表参数匹配。这是代码。

I am trying to add sql values based upon column names. So, when a person picks a particular table the added values will match to that tables parameters. Here is the code.

Dim cb2 As New StringBuilder("INSERT INTO ")
               cb2.AppendFormat("[" + Form1.TreeView1.SelectedNode.Text.ToString + "]" + " values(@")
               Dim sbOn As New StringBuilder(" @ ")
               Dim andRequired As Boolean = False

               For Each item In Form1.DataGridView1.Columns
                   If andRequired Then
                       sbOn.Append(" , ")
                   End If
                   Dim columnName As String = item.ToString()
                   sbOn.AppendFormat("@", columnName)
                   andRequired = True

               Next

字符串应如下所示：

the string should look like this:

insert into " + "[" + Form1.TreeView1.SelectedNode.Text.ToString + "]" + "values(@ID,@First,@Last,@Middle,@age)

我很感激帮助。



我尝试过：



我一直在使用

I appreciate the help.

What I have tried:

I have been using the

MessageBox.Show(cb2.ToString)

帮我弄清楚我在字符串中的位置。截至目前，我不能按照我需要的方式生成字符串。

to help me figure out where I was at in the string. As of now I can not produce the string the way I need it to come out.

推荐答案

永远不要通过与用户输入连接来构建SQL查询，它被命名为 SQL注入，它对您的数据库很危险并且容易出错。

名称中的单引号和程序崩溃。如果像Brian O'Conner这样的用户输入可能会使您的应用程序崩溃，那么这是一个SQL注入漏洞。

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

Never build an SQL query by concatenating with user inputs, it is named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability.
SQL injection - Wikipedia[^]
SQL Injection[^]

你不是太远了，但你必须注意ppolymorphe的评论关于SQL注入。



要获得正确格式的字符串，请尝试以下方法：[br标签显示的网站错误将很快修复]

You're not too far off, but you must take notice of ppolymorphe's comments about SQL Injection.

To get the string in the right format try this: [br tags showing are a site bug that will be fixed soon]

      Dim cb2 As New StringBuilder("INSERT INTO [@table] VALUES (")<br />
        <br />
        Dim commaRequired As Boolean = False<br />
<br />
        For Each item As DataGridViewColumn In Me.DataGridView1.Columns<br />
            If commaRequired Then<br />
                cb2.Append(" , ")<br />
            End If<br />
            cb2.AppendFormat("@{0}", item.Name)<br />
            commaRequired = True<br />
        Next<br />
<br />
        cb2.Append(")")

你的下一个问题是如何将所有这些值都放入SQL参数中，但是你要将这些信息插入到数据库中。如果我假设实际值在DataGridView的第0行，那么这样的东西可能会起作用

Your next problem is how to get all of those values into the SQL parameters for however you are going to insert this information into the database. If I assume that the actual values are in Row 0 of the DataGridView then something like this might work

        Dim cb2 As New StringBuilder("INSERT INTO [@table] VALUES (")<br />
        Dim sqlcmd As New SqlCommand()<br />
        sqlcmd.Connection = conn<br />
        sqlcmd.Parameters.AddWithValue("@table", Me.TreeView1.SelectedNode.Text.ToString())<br />
<br />
        Dim commaRequired As Boolean = False<br />
<br />
        For Each item As DataGridViewColumn In Me.DataGridView1.Columns<br />
            If commaRequired Then<br />
                cb2.Append(" , ")<br />
            End If<br />
            Dim parmName As String = String.Format("@{0}", item.Name)<br />
            cb2.Append(parmName)<br />
            sqlcmd.Parameters.AddWithValue(parmName, Me.DataGridView1.Rows(0).Cells(item.Name).Value)<br />
            commaRequired = True<br />
        Next<br />
<br />
        cb2.Append(")")<br />
        sqlcmd.CommandText = cb2.ToString()

当然，您应该分离UI和数据库更新的问题......

Of course, you should be separating the concerns of UI and database updates...

这篇关于附加字符串以匹配SQL添加的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！