CODEWITHSUNDEEP
sqldynamic-sql
Ahmad Farid
Ahmad Farid

Reputation: 14774

How to Add Quotes to a Dynamic SQL Command?

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 + "'");

Upvotes: 8

Views: 62932

Answers (6)

awe
awe

Reputation: 22462

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.

Upvotes: 0

John Saunders
John Saunders

Reputation: 161821

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();
        }
    }
}

Upvotes: 11

KM.
KM.

Reputation: 103667

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.

Upvotes: 2

cjk
cjk

Reputation: 46465

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'''

Upvotes: 3

TheTXI
TheTXI

Reputation: 37905

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

'''Test''' = 'Text'

This is for SQL Server.

Upvotes: 9

n8wrl
n8wrl

Reputation: 19765

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.

Upvotes: 3

Related Questions