How to save HTML content in database

Question

I have text area on my page. In that area I have to add some HTML code and save it to database. And it works for simple html, but when I select some text from "wikipedia" for example and paste it and try to save when SQL Query need to be executed I got exception with following error:

Incorrect syntax near 's'.
The identifier that starts with '. Interestingly, old maps show the name as&nbsp;<em>Krakow</em>.</p>
<p>Kragujevac experienced a lot of historical turbulence, ' is too long. Maximum length is 128.
The identifier that starts with '>Paleolithic</a>&nbsp;era. Kragujevac was first mentioned in the medieval period as related to the public square built in a sett' is too long. Maximum length is 128.
The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure.
The label 'http' has already been declared. Label names must be unique within a query batch or stored procedure.
Unclosed quotation mark after the character string '>Belgrade Pashaluk</a>.</p>'

I am using asp mvc and razor engine. I don't know maybe I need to encome html somehow. I have also added this for ArticleText property:

[AllowHtml]        
        public string ArticleText { get; set; }

This is code for saving to database:

string sql = @"insert into tbl_articles 
                               (Text) values 
                               ("'" + article.ArticleText"'"+")";

                SqlCommand cmd = new SqlCommand(sql, conn);

                cmd.ExecuteNonQuery();

Solution 1

Wow, NO, NO, NO. Your code is vulnerable to SQL injection and very bad stuff will happen if you don't use parametrized queries. So use parametrized queries.

using (var conn = new SqlConnection("some conn string"))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "insert into tbl_articles (Text) values (@Text)";
    cmd.Parameters.AddWithValue("@Text", article.ArticleText);
    cmd.ExecuteNonQuery();
}

Everytime you use the + operator to concatenate strings when building a SQL query you are doing something extremely dangerous and wrong.

Solution 2

Try to save this way:

string sqlQuery = "INSERT INTO tbl_articles (Text) VALUES (@text)";
SqlCommand cmd = new SqlCommand(sqlQuery, db.Connection);
cmd.Parameters.Add("@text", article.ArticleText);
cmd.ExecuteNonQuery();

Solution 3

This is a classic example of opening your system to a Sql injection attack.

You need to escape the ' character because if the Html contains the ' character, it will break the SQL Statement when it is executed.

EDIT: Use Darins solution to solve the problem.

Solution 4

Try:

string sql = @"insert into tbl_articles 
                               (Text) values 
                               (@articleText)";

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@articleText",
                Server.HtmlEncode(article.articleText));

                cmd.ExecuteNonQuery();

Solution 5

this should be parameterized:

    public void foo(string connectionString, string textToSave)
    {
        var cmdString = "insert into tbl_articles (text) values (@text)";
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand comm = new SqlCommand(cmdString, conn))
            {
                comm.Parameters.Add("@text", SqlDbType.VarChar, -1).Value = textToSave;
                comm.ExecuteNonQuery();
            }
        }
    }

(this is the gereral idea, it's not completely functional as written.)

View more solutions