如何向动态 SQL 命令添加引号？

发布于 2024-07-27 02:53:52 字数 440 浏览 6 评论 0原文

我正在存储和编辑数据库中的某个字段，其中涉及一长串一个或多个句子。每当我在文本框中输入单引号并想要保存它时，它都会抛出一个异常，例如 “‘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 + "'");

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

终难愈 2024-08-03 02:53:52

正如 KM 所说，不要这样做！

改为这样做：

private static void UpdateQuestionByID(
    int questionID, string question, string answer, string lastEdited)
{
    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        const string QUERY =
            @"UPDATE Questions " +
            @"SET Question = @Question, Answer = @Answer, LastEdit = @LastEdited " +
            @"WHERE ID = @QuestionID";
        using (var cmd = new SqlCommand(QUERY, conn))
        {
            cmd.Parameters.AddWithValue("@Question", question);
            cmd.Parameters.AddWithValue("@Answer", answer);
            cmd.Parameters.AddWithValue("@LastEdited", lastEdited);
            cmd.Parameters.AddWithValue("@QuestionID", questionID);
            cmd.ExecuteNonQuery();
        }
    }
}

As KM said, don't do this!

Do this instead:

private static void UpdateQuestionByID(
    int questionID, string question, string answer, string lastEdited)
{
    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        const string QUERY =
            @"UPDATE Questions " +
            @"SET Question = @Question, Answer = @Answer, LastEdit = @LastEdited " +
            @"WHERE ID = @QuestionID";
        using (var cmd = new SqlCommand(QUERY, conn))
        {
            cmd.Parameters.AddWithValue("@Question", question);
            cmd.Parameters.AddWithValue("@Answer", answer);
            cmd.Parameters.AddWithValue("@LastEdited", lastEdited);
            cmd.Parameters.AddWithValue("@QuestionID", questionID);
            cmd.ExecuteNonQuery();
        }
    }
}

回复收藏 0 原文

两仪 2024-08-03 02:53:52

如果要在 SQL 字段中包含单引号，请使用单引号将其转义。

'''Test''' = 'Text'

这适用于 SQL Server。

If you want to include a single quote into an SQL field, escape it using single quotes

'''Test''' = 'Text'

This is for SQL Server.

回复收藏 0 原文

孤檠 2024-08-03 02:53:52

编写一个存储过程来进行字段编辑并使用 SQL 参数来保存值。报价并不重要。如果您不需要存储过程，至少使用参数标记构建 SQL 文本并使用 SQL 参数。

回复收藏 0 原文

枫以 2024-08-03 02:53:52

在 MSSQL 中，您可以将引号加倍：

my dodg'y test          -> 'my dodg''y test'
my 'quoted' string      -> 'my ''quoted string'''
'first and last quotes' -> '''first and last quotes'''

In MSSQL you can double up your quotes:

my dodg'y test          -> 'my dodg''y test'
my 'quoted' string      -> 'my ''quoted string'''
'first and last quotes' -> '''first and last quotes'''

回复收藏 0 原文

傲影 2024-08-03 02:53:52

很难给你一个具体的答案，因为你没有列出你正在使用的数据库或应用程序语言。

您必须动态构建 SQL，并且字符串中的引号被解释为字符串的结尾。根据您使用的数据库，您需要对要在 sql 命令中使用的每个字符串中的单引号进行转义。这可以通过在尝试运行查询之前打印查询来看到。

您没有提及调用数据库的应用程序，但是当您构建命令时，您需要使用您编写的或由您的语言提供的 FIX_QUOTES() 命令：

SqlCommand com = new SqlCommand("UPDATE Questions SET Question = '[" + FIX_QUOTES(tbQuestion.Text) + "]', Answer = '[" + FIX_QUOTES(tbAnswer.Text) + "]', LastEdit = '" + FIX_QUOTES(CurrentUser.Login) + "'WHERE ID = '" + FIX_QUOTES(CurrentQuestion.ID) + "'"); – A

这种类型的动态查询非常简单对于 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:

SqlCommand com = new SqlCommand("UPDATE Questions SET Question = '[" + FIX_QUOTES(tbQuestion.Text) + "]', Answer = '[" + FIX_QUOTES(tbAnswer.Text) + "]', LastEdit = '" + FIX_QUOTES(CurrentUser.Login) + "'WHERE ID = '" + FIX_QUOTES(CurrentQuestion.ID) + "'"); – A

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.

回复收藏 0 原文