CODEWITHSUNDEEP
c#asp.netsql-serverasp.net-mvcrazor
1110
1110

Reputation: 6829

How to save HTML content in database

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

Upvotes: 5

Views: 43761

Answers (5)

user610217
user610217

Reputation:

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.)

Upvotes: 1

Mikael &#214;stberg
Mikael &#214;stberg

Reputation: 17156

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.

Upvotes: 1

Sergey Gavruk
Sergey Gavruk

Reputation: 3558

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

Upvotes: 2

ozsenegal
ozsenegal

Reputation: 4133

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

Upvotes: 1

Darin Dimitrov
Darin Dimitrov

Reputation: 1038800

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.

Upvotes: 34

Related Questions