如何向动态 SQL 命令添加引号?
我正在存储和编辑数据库中的某个字段,其中涉及一长串一个或多个句子。 每当我在文本框中输入单引号并想要保存它时,它都会抛出一个异常,例如 “‘l’附近的语法不正确。 字符串 '' 后面有未闭合的引号。" 有什么办法可以避免这种情况吗?
编辑: 查询是:
SqlCommand com = new SqlCommand("UPDATE Questions SET Question = '[" +
tbQuestion.Text + "]', Answer = '[" +
tbAnswer.Text + "]', LastEdit = '" +
CurrentUser.Login +
"'WHERE ID = '" + CurrentQuestion.ID + "'");
I am storing and editing some field in a database that involves a long string of one or more sentences. whenever i enter a single quote in the textbox and want to save it it throws an exception like
"Incorrect syntax near 'l'.
Unclosed quotation mark after the character string ''."
is there any idea to avoid that?
EDIT:
The query is:
SqlCommand com = new SqlCommand("UPDATE Questions SET Question = '[" +
tbQuestion.Text + "]', Answer = '[" +
tbAnswer.Text + "]', LastEdit = '" +
CurrentUser.Login +
"'WHERE ID = '" + CurrentQuestion.ID + "'");
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
正如 KM 所说,不要这样做!
改为这样做:
As KM said, don't do this!
Do this instead:
如果要在 SQL 字段中包含单引号,请使用单引号将其转义。
这适用于 SQL Server。
If you want to include a single quote into an SQL field, escape it using single quotes
This is for SQL Server.
编写一个存储过程来进行字段编辑并使用 SQL 参数来保存值。 报价并不重要。 如果您不需要存储过程,至少使用参数标记构建 SQL 文本并使用 SQL 参数。
Write a stored produre to do your field editing and use SQL parameters to save the value. Quotes won't matter. If you don't want a stored proc at least build your SQL text with parameter markers and use SQL parameters with that.
在 MSSQL 中,您可以将引号加倍:
In MSSQL you can double up your quotes:
很难给你一个具体的答案,因为你没有列出你正在使用的数据库或应用程序语言。
您必须动态构建 SQL,并且字符串中的引号被解释为字符串的结尾。 根据您使用的数据库,您需要对要在 sql 命令中使用的每个字符串中的单引号进行转义。 这可以通过在尝试运行查询之前打印查询来看到。
您没有提及调用数据库的应用程序,但是当您构建命令时,您需要使用您编写的或由您的语言提供的 FIX_QUOTES() 命令:
这种类型的动态查询非常简单对于 sql 注入攻击。我建议使用存储过程调用数据库或带有参数列表。
it is difficult to give you a specific answer, because you don't list the database or application language you are using.
You must be building your SQL dynamically, and the quote within the sting is being interpreted as the end of the string. Depending on the database you are using, you need to escape the single quotes within each string you intend to use in your sql command. This can be seen by printing your query before you try to run it.
You do not mention the application that you are calling the database from, but when you build you command you need to use a FIX_QUOTES() command that you write or if provided by your language:
This type of dynamic query is very easy for an sql injection attack. I would recommend calling the database with a stored procedure or with a parameter list.
正如有些人已经说过的,添加额外的引号就可以了。 我可以确认 Oracle 也是如此(其他人给出的答案对于 MSSQL 和 SQL Server 有效)。 我认为使用存储过程对此来说太过分了。
As some have already said, adding an extra quote will do the trick. I can confirm that this is also the case for Oracle (others have given this answer to be valid for MSSQL and SQL Server). I think that using stored procedures is overkill for this.