如何在VB.NET中的表视图中插入数据？我试图在视图中插入虚拟数据... [英] How to insert data in a view for a table in VB.NET? I am trying to insert a dummy data in a view...

问题描述

我正在尝试在vb.net的视图中插入虚拟数据，我的代码如下...



Dim test As字符串= INSERT INTO Abc.dbo.Dbe（帐户，SecurityID，数量OriginalCost，承诺，AdvisorName，CLIENTNAME，ARIMarketValue）值（' &安培; moAccount.AccountID&安培; &安培; moSecurity.SecurityID&安培;， & 0&，& 1021&，& 0&，&abc&，&cde&，& 1021& '）





但我在上面的代码中收到异常错误



'。'附近的语法不正确。



请帮忙，我在哪里弄错了。



我尝试过：



我试图以上述方式插入数据但是我得到例外... ...



'。'附近的语法不正确。

I am trying to insert dummy data in the view in the vb.net, and my code is as below...

Dim test As String = "INSERT INTO Abc.dbo.Dbe(AccountID,SecurityID,Quantity,OriginalCost,Commitment,AdvisorName,ClientName,ARIMarketValue) vALUES('" & moAccount.AccountID & "," & moSecurity.SecurityID & "," & 0 & "," & 1021 & "," & 0 & "," & "abc" & "," & "cde" & "," & 1021 & "')"


but I am getting the exception error in the above code which is

Incorrect syntax near '.'.

Please Help, where am I making a mistake.

What I have tried:

I have tried to insert a data in the way mentioned above but I am getting exception like...

Incorrect syntax near '.'.

推荐答案

你的代码容易受到 SQL注入 [ ^ ]。 从不使用字符串连接来构建SQL查询。 总是使用参数化查询。



修复该安全漏洞几乎肯定会同时解决您的问题。

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Fixing that security vulnerability will almost certainly fix your problem at the same time.

Using conn As New SqlConnection("...")
    Using cmd As New SqlCommand("INSERT INTO Abc.dbo.Dbe (AccountID, SecurityID, Quantity, OriginalCost, Commitment, AdvisorName, ClientName, ARIMarketValue) VALUES (@AccountID, @SecurityID, @Quantity, @OriginalCost, @Commitment, @AdvisorName, @ClientName, @ARIMarketValue)", conn)
        cmd.Parameters.AddWithValue("@AccountID", moAccount.AccountID)
        cmd.Parameters.AddWithValue("@SecurityID", moSecurity.SecurityID)
        cmd.Parameters.AddWithValue("@Quantity", 0)
        cmd.Parameters.AddWithValue("@OriginalCost", 1021)
        cmd.Parameters.AddWithValue("@Commitment", 0)
        cmd.Parameters.AddWithValue("@AdvisorName", "abc")
        cmd.Parameters.AddWithValue("@ClientName", "cde")
        cmd.Parameters.AddWithValue("@ARIMarketValue", 1021)
        
        conn.Open()
        cmd.ExecuteNonQuery()
    End Using
End Using

---

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

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

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

---

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[^]

您的代码没有生成有效的SQL语句，请查看调试器中test的内容，它将类似于



。 .. values（'123,456,0,121''）



这不是你如何构造值，如果它们是文本，你会在各个字段值周围放置撇号，否则你不需要它们，你已经在整个值数据周围放了撇号。所以你需要sql看起来像这样



values（123,456， 0,1021,0，'John'，'Dave'..



等等。但是，如果你的一个名字有撇号，你会遇到问题，比如O'Neil。



而不是处理由自己制作SQL引起的这些问题，而是使用已建议的参数化查询。它使代码更易于阅读，如果abc等来自用户输入那么你当前的代码会让你对sql注入攻击开放。

Your code isn't making a valid SQL statement, look at the content of "test" in the debugger and it'll be like

"... values ('123, 456, 0, 121 .. ')

That's not how you construct values, you put apostrophes around individual field values if they are text, otherwise you don't need them, you have put apostrophes around the whole values data. So you need the sql to look like this

values (123, 456, 0, 1021, 0, 'John', 'Dave' ..

and so on. However you'll hit problems if one of your names has an apostrophe, like "O'Neil".

Rather than dealing with these issues caused by making SQL yourself, use the parameterised queries as already advised. It makes the code simpler to read, and if "abc" etc come from user inputs then your current code leaves you open to sql injection attacks.

这篇关于如何在VB.NET中的表视图中插入数据？我试图在视图中插入虚拟数据...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！