将数据插入包含撇号的SQL Server数据库

I'm making this program on Vb.net 2012 that has a connection to SQL Server 2012.

我在Vb.net 2012上创建了这个与SQL Server 2012连接的程序。

One of the columns of this database table is Description, and in some cases the date may include apostrophes, for example... 'chainsaw 15'3" X 1 1/2 X .050 X 3/4'

此数据库表的一列是Description，在某些情况下，日期可能包含撇号，例如......'chainsaw 15'3“X 1 1/2 X .050 X 3/4'

When I run the query the apostrophe that is between the data causes an error at the syntax of the query, this is the query line in VB.net.

当我运行查询时，数据之间的撇号会导致查询语法出错，这是VB.net中的查询行。

CMD.CommandText = 
"INSERT INTO Table_ARTICLES 
 (NUMPART, DESCRIPTION, LOCATION, MAX, ACTUAL, MIN, Unidad_de_medida) 
 VALUES ('" & txtNumParte.Text & "', '" & txtDescripcion.Text & "', 
  '" & txtLocaclizacion.Text & "', '" & txtMaximo.Text & "', '" & txtActual.Text & "',
  '" & txtMin.Text & "', '" & cmbUnidad.Text & "')"

Does anybody know how to make this query accept those characters on the query?

有谁知道如何让这个查询接受查询中的那些字符？

2 个解决方案

#1

As @pmbAustin pointed out, is a terrible idea to build sql statements via string concatenation due to SQL Injection attacks and other problems. The approach you should use is called a parametrized query:

正如@pmbAustin所指出的，由于SQL注入攻击和其他问题，通过字符串连接构建sql语句是一个糟糕的主意。您应该使用的方法称为参数化查询：

CMD.CommandText = "INSERT INTO (NUMPART, DESCRIPTION, LOCATION, MAX, ACTUAL, 
MIN, Unidad_de_medida) 
VALUES (@NUMPART, @DESCRIPTION,@LOCATION,@MAX,@ACTUAL,@MIN,@UNIDAD_DE_MEDIDA)"

And then:

接着：

CMD.Parameters.Add("@NUMPART",txtNumParte.Text);
CMD.Parameters.Add("@DESCRIPTION",txtDescripcion.Text);
//...and so on
CMD.ExecuteNonQuery();

#2

Please use parameterized SQL (i.e. stored procedures) to prevent SQL injection and the like.

请使用参数化SQL（即存储过程）来防止SQL注入等。

As for your question, you would want to replace the single quote (apostrophe) with two single quotes before you add it as a parameter. This way the first one acts as an escape character which will allow for the apostrophe to be inserted into the database.

至于你的问题，在将它作为参数添加之前，你需要用两个单引号替换单引号（撇号）。这样，第一个作为转义字符，允许将撇号插入数据库。

Example: txtNumParte.Text.Replace("'", "''")

示例：txtNumParte.Text.Replace（“'”，“''”）

#1