How to Add Quotes to a Dynamic SQL Command?

Question

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

Cardinal

Please show the SQL statement you're using. – Spendable

simply escape ' with another ' like Hell's Angels to Hell''s Angels – Bridgid

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

That's what we all thought. Please edit your question to add that information. – Spendable

Don't worry. I'll do it for you. – Spendable

Answer 1

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

Spendable

Answer 2

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

'''Test''' = 'Text'

This is for SQL Server.

Earpiece

It would be much safer to use parameterised SQL instead. – Fahland

Luke: Sure it would, but I am not going to sit here and attempt to guess how he is currently doing his SQL, so I am going to give the most straightforward answer. – Earpiece

@TheTXI: Fair enough, but however he's doing his SQL, the one thing that's certain is that he's not using parameters. Using parameters would avoid this problem altogether. – Fahland

Answer 3

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.

Investigate

Answer 4

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

Tillo

but the problem is that i get the input from the user so it wont be nice to tell the user to add another quote – Cardinal

Answer 5

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.

Erumpent

Answer 6

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.

Largeminded

How about using a parameterized query? – Spendable

Yes, you can do that. The double quote solution will have to be used if you run sql directly, not via the .NET API. – Largeminded