程序里嵌入sql语句的坏处_PAEVISS –程序员反对SQL语句中的嵌入式值

程序里嵌入sql语句的坏处

First there was

首先有

PAFSO – Programmers Against the File System Object PAFSO –程序员反对文件系统对象

Then there was PAUES – Programmers Against the Use of the End Statement

然后是PAUES –程序员反对使用End语句

Now Experts Exchange brings you PAEVISS - Programmers Against Embedded Values In SQL Statements

现在，Experts Exchange为您带来PAEVISS-程序员反对SQL语句中的嵌入式值

With ADO.NET there is sometimes a need to insert, update or query the database using a command object.

使用ADO.NET有时需要使用命令对象来插入，更新或查询数据库。

Using Connection As SqlConnection = New SqlConnection(ConnectionString)
	Connection.Open()
	Dim CmdText As String = "INSERT INTO [Person] (FirstName, LastName) " & _
                "VALUES ('" & txtFirstname.Text & "', '" & txtLastname.Text & ")"

	Using cmd As SqlCommand = New SqlCommand(CmdText, Connection)
		cmd.ExecuteNonQuery()
	End Using
	Connection.Close()
End Using

If either of the textbox values contains an apostrophe, then it requires escaping by doubling up.

如果两个文本框值中的任何一个都包含撇号，则需要通过加倍来进行转义。

By entering SQL statements in the textboxes, a user could perform a

通过在文本框中输入SQL语句，用户可以执行

SQL injection attack. SQL注入攻击 。

Values such as numbers and dates will be coerced into strings, which are subject to culture rules, which can cause problems.  For example, there is always much confusion over which date string format is used when sending dates to SQL server using formatted strings.  The US format of MM/dd/yyyy will often cause confusion in environments expecting a little endian date format (dd/MM/yyyy.)

诸如数字和日期之类的值将被强制转换为字符串，这取决于文化规则，这可能会引起问题。 例如，当使用格式化字符串将日期发送到SQL Server时，总是会混淆使用哪种日期字符串格式。 美国格式的MM / dd / yyyy通常会在期望使用小端日期格式（dd / MM / yyyy）的环境中引起混乱。

Instead, let's look at how you can use parameterized queries.

相反，让我们看看如何使用参数化查询 。

Using Connection As SqlConnection = New SqlConnection(ConnectionString)
	Connection.Open()
	Dim CmdText As String = "INSERT INTO [Person] (FirstName, LastName) " & _
		"VALUES (@lastname, @firstname)"

	Using cmd As SqlCommand = New SqlCommand(CmdText, Connection)
		With cmd.Parameters
			.AddWithValue("@lastname", txtLastname.Text)
			.AddWithValue("@firstname", txtFirstname.Text)
		End With
		cmd.ExecuteNonQuery()
	End Using
	Connection.Close()
End Using

@lastname and @firstname as placeholders or parameters, which then must be added, along with their values, to the parameters collection of the command object.  The "AddWithValue" method is convenient for adding parameters with their values.  .NET 1.x users will not have access to this method.  There is an overload of "Add" that allows you to specify a parameter value, but this was deprecated by the AddWithValue method as explained in the MSDN documentation.

@lastname和@firstname作为占位符或参数，然后必须将其及其值连同其值一起添加到命令对象的参数集合中。 使用“ AddWithValue”方法可以方便地将参数及其值添加在一起。 .NET 1.x用户将无法访问此方法。 “ Add”的重载允许您指定参数值，但是如MSDN文档中所述 ，AddWithValue方法已弃用该值。

You may notice that this looks similar to the syntax that is used for stored procedures.   In fact, the use of parameters can have an effect at the server level to increase execution plan reuse, and thus boost performance slightly.  (However, if your SQL Server database has forced parameterization, then every query submitted to the database is compiled with parameters.)

您可能会注意到，这看起来类似于用于存储过程的语法。 实际上，参数的使用可以在服务器级别产生影响，从而增加执行计划的重用性，从而稍微提高性能。 （但是，如果您SQL Server数据库具有强制参数化，则提交给数据库的每个查询都将使用参数进行编译。）

There are so many benefits, and so few drawbacks, that I recommend parameterized queries as a best practice.  Avoid the messy, dangerous practice of embedding values into SQL command strings.

我有很多优点，而有很少的缺点，因此我建议将参数化查询作为最佳实践。 避免将值嵌入SQL命令字符串的混乱而危险的做法。

翻译自: https://www.experts-exchange.com/articles/2611/PAEVISS-Programmers-Against-Embedded-Values-In-SQL-Statements.html

程序里嵌入sql语句的坏处