在SQL查询中转义值（带有SQL连接器的C＃）

I know I can use the parameters, but what is the right way to escape string sequences? The query could be like this:

我知道我可以使用参数,但是什么是转义字符串序列的正确方法?查询可能是这样的:

"INSERT INTO records (ReferenceID,Name,Note,Author) VALUES ('" + ID+ "','" + addlevel.textBox1.Text + "','"+addlevel.textBox2_note.Text+ "','"+Program.Username+"')";

I am ONLY curious, just want to know :)

我只是好奇,只想知道:)

EDIT: But what about that? "CREATE TABLE "+string" .... parameters cannot be used here!

编辑:那怎么样? “CREATE TABLE”+ string“....参数不能在这里使用!

5 个解决方案

#1

If you need to perform database operations, such as creating tables, then you should use SQL Server Management Objects instead of executing SQL strings.

如果需要执行数据库操作(如创建表),则应使用SQL Server管理对象而不是执行SQL字符串。

For CRUD operations parameters is absolutely the only true path.

对于CRUD操作,参数绝对是唯一的真实路径。

UPDATE: It appears that the MySQL client library contains a helper method for this ill-advised task. You can call MySqlHelper.EscapeString(string).

更新:似乎MySQL客户端库包含一个帮助这种不明智的任务的方法。您可以调用MySqlHelper.EscapeString(string)。

#2

The right way is to use parameters.

正确的方法是使用参数。

"Just Say No" to trying to do the escaping yourself - it's far too easy to get wrong. Why do you think you'd want to escape them manually instead of using parameters?

“只是说不”试图逃避自己 - 这很容易出错。为什么你认为你想要手动转义它们而不是使用参数?

#3

If you really, really, really need to do the escaping yourself (of which there is no sign in your example):

如果你真的,真的,真的需要自己逃避(在你的例子中没有迹象):

string EncodeMySqlString(string value) {
   return value.Replace(@"\", @"\\").Replace("'", @"\'")
}

#4

I think the only thing you need to do is value = value.Replace("'", "''")

我认为你唯一需要做的就是value = value.Replace(“'”,“''”)

Of course you shouldn't do this, but you know that.

当然你不应该这样做,但你知道。

Edit: Apparantly this is all wrong for MySQL. It should work for PostgreSQL, MS SQL and Oracle, though.

编辑:显然这对MySQL来说都是错误的。它应该适用于PostgreSQL,MS SQL和Oracle。

#5

use commnd parameters instead. It takes care of escaping itself. It's the solution also against sql injections.

改用commnd参数。它需要自己逃避。这也是针对sql注入的解决方案。

#1