如何使用VB.NET将常用文本插入SQL数据库 [英] How can I insert common text to the SQL database using VB.NET

问题描述

我有数据表，我想在数据表列中插入一些Common文本，因为我不希望它获取NULL。



为此列我没有窗口形式的任何文本框或标签。

是否可以插入任何文字???

如果是的话，请指教我怎么样？



我尝试了什么：

 Cmd.CommandText =插入StaffLdgr（EntryDate，StaffName，RefNo，TransactionType，Currency，CrAmt，ExRate）值（'& Dtpcashdb.Text &，& Tbdbkname.Text&'，'&DGL / CASH-0000&'，'&Credit&'，'&INR &'，'& Tbamtcashdb.Text&'，'&1.00&'）
 

解决方案

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

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

SQL注入 - 维基百科 [ ^ ]

SQL注入 [ ^ ]

这是一个示例

 cmd.CommandText =Insert进入StaffLdgr（EntryDate，StaffName，RefNo，TransactionType，Currency，CrAmt，ExRate）值（@ Dtpcashdb，@ Tbdbkname，'DGL / CASH-0000'，'Credit'，'INR'，@ Tbamtcashdb，1.00）
 cmd.Parameters.AddWithValue（@ dtpcashdb，Dtpcashdb.Text）
 cmd.Parameters.AddWithValue（@ Tbdbkname，Tbdbkname.Text）
 cmd.Parameters.AddWithValue（@ Tbamtcashdb， Tbamtcashdb.Text）

注意事项：

1.常用文字 fer to ...

列 refno 得到普通文本'DGL / CAH-000'

列 TransactionType 获取普通文本'信用'

列货币正在获取普通文本'INR'



2.您试图以字符串形式输入Column ExRate 类型变量。那应该是数字列类型（decimal，double，float）。将数据库更改为正确的列类型。



3.我的解决方案以及您在此处看到的所有其他解决方案使用参数化查询。这不仅有助于防止SQL注入，它还可以帮助克服数据类型和不匹配的单引号问题。所以将来使用参数化查询！我不能强调你注意到这个建议是多么重要（我不是唯一一个这样说过的人）



4.我已经摆脱了在

&这样的地方不必要的字符串连接'，'& DGL / CASH-0000& '，'& 信用& '，'& INR& '，

这与

& '，'DGL / CASH-0000'，'信用'，'INR'，

更短，更容易阅读。如果您正在考虑用变量替换硬编码值，那么它很简单就变成

& ，@引用号，@ TranType，@货币，

I have data table, i want to insert some Common text to the data table column, as i don't want it to get NULL.

For this column i don't have any text box or label in window form.
Is it possible to insert any text???
if yes, please advice how can i ??

What I have tried:

Cmd.CommandText = "Insert Into StaffLdgr (EntryDate, StaffName, RefNo, TransactionType, Currency, CrAmt, ExRate) Values('" & Dtpcashdb.Text & ",'" & Tbdbkname.Text & "','" & "DGL/CASH-0000" & "','" & "Credit" & "', '" & "INR" & "','" & Tbamtcashdb.Text & "','" & "1.00" & "')"

解决方案

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

Here is an example

cmd.CommandText = "Insert Into StaffLdgr (EntryDate, StaffName, RefNo, TransactionType, Currency, CrAmt, ExRate) Values(@Dtpcashdb,@Tbdbkname,'DGL/CASH-0000','Credit', 'INR',@Tbamtcashdb,1.00)"
cmd.Parameters.AddWithValue("@dtpcashdb", Dtpcashdb.Text)
cmd.Parameters.AddWithValue("@Tbdbkname", Tbdbkname.Text)
cmd.Parameters.AddWithValue("@Tbamtcashdb", Tbamtcashdb.Text)

Things to note:
1. "common text" as you refer to ...
Column refno is getting the "common text" 'DGL/CAH-000'
Column TransactionType is getting the "common text" 'Credit'
Column Currency is getting the "common text" 'INR'

2. You are trying to enter Column ExRate as a string-type variable. That should be a numeric column type (decimal, double, float). Change your database to the correct column type.

3. My solution, and all of the other solutions you have seen here, uses a parameterised query. Not only does that help to prevent SQL Injection it can also help overcome issues with data types and mismatched single quotes. So use parameterized queries in future! I cannot emphasise enough how important it is that you take note of this advice (I am not the only one who has said this)

4. I have got rid of the unnecessary string concatenation in places like

& "','" & "DGL/CASH-0000" & "','" & "Credit" & "', '" & "INR" & "',"

It is exactly the same as

& "','DGL/CASH-0000','Credit','INR',"

which is shorter and easier to read. If you are thinking of replacing the hard-coded values with variables then it simple becomes

& "',@RefNo,@TranType,@Currency,"

这篇关于如何使用VB.NET将常用文本插入SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！