如何使用VB.NET防止数据库中的重复条目？ [英] How do I prevent duplicate entries in my database using VB.NET ?

问题描述

Dim ds AS String = ""

ds = sqlFunction.ExecuteSQL("INSERT INTO employee.empdata (empid,fname,lname,cnumber) VALUES ('" & empid.text & "','" & fname.text & "', '" & name.text & "','" & cnumber.text & "') ",sqlFunction.connstring)

我尝试过：



我想要的是确保我不在我的数据库中插入相同的empid

What I have tried:

What i want is to ensure that i don't insert the same empid in my database

推荐答案

1 。如果EmpID是主键，则不允许您插入副本。这应该抛出反映约束违规的错误。



2. Insert，Update和Delete语句只返回一个反映受影响行数的整数。如果值为0，则未插入行。



3.永远不要将字符串连接在一起以发出命令。您应该使用参数化语句来避免SQL注入，这是20年前发现的，并且仍然位于前5个网站漏洞中。即使是非恶意的条目也会破坏你的命令;尝试使用O'Rourke的姓氏，看看会发生什么



现在回答一些简单的问题

A.尝试运行一个带有正在检查的EmpID的WHERE子句的SELECT命令

B.使用存储过程，它可以包含来自（A）的SELECT语句并且仅在不存在时插入。存储过程还可以包含OUTPUT参数，您可以将更多信息传递回程序

1. If EmpID is the Primary Key, you will not be allowed to insert a duplicate. This should throw an error reflecting the constraint violation.

2. Insert, Update, and Delete statements will only return an integer reflecting the amount of rows affected. If the value is 0, the row was not inserted.

3. NEVER EVER concatenate a string together to make your command. You should be utilizing a parameterized statement to avoid SQL Injection, which was identified 20 years ago and is still in the top 5 website vulnerabilities. Even a non-malicious entry will break your command; try using a last name of O'Rourke and see what happens

Now for some simple answers to your question
A. Try running a SELECT command with a WHERE clause for the EmpID you are checking
B. Use a Stored Procedure, which can contain the SELECT statement from (A) and only insert if it does not exist. A stored procedure can also contain OUTPUT parameters which you can pass more information back to the program

不清楚您的应用程序是如何构建的，但有几种方法可以实现，

1.尝试使用catch语句，假设插入重复键时插入查询将会出错：例如使用Try ... Catch ......，最后！ [ ^ ]

2.在插入之前写下另一个select语句来检查empid是否存在



此外，代码可能容易受到SQL注入/跨站点脚本攻击。以下是上述漏洞如何运作的示例。

SQL注入和跨站点脚本 [ ^ ]



尽可能尝试使用参数化查询

在VB.NET数据库应用程序中使用参数化查询和报告 [ ^ ]

Not clear how your application being structure, but there are several way to do it,
1. Try, catch statement, assuming the insert query will complaint if inserting duplicate key: example Using Try... Catch..., Finally![^]
2. Write another select statement to check if the empid exists before inserting

Also the code might vulnerable to SQL injection/ cross site scripting attack. Here is an example how the mentioned vulnerability works.
SQL Injection and Cross-Site Scripting[^]

Try use Parameterized Queries if possible
Using Parameterized Queries and Reports in VB.NET Database Applications[^]

这篇关于如何使用VB.NET防止数据库中的重复条目？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！