Create MySQL string that is "safe" (escape double quotes)

Question

I need to insert text into a MySQL table via C#. dbConnect is an adapter I have created myself and ProcessNonQuery only takes a string.

I can't insert text that contain the characters " and '.

This is my attempt:

public void InsertArticle(string name, string title, string keywords, string desc, string content, int idCategory,bool featured)// int level, int typeArt
{                        
    this.dbConnect.ProcessNonQuery(" set global sql_mode=\"NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\"");
    string strFeatured;

    if (featured)
        strFeatured = "1";
    else
        strFeatured = "0";
    content = content.Replace("'", "\'");
    StringBuilder sbInsertArticle = new StringBuilder("INSERT INTO Article(NameArt, TitleArt, keywordsArt, DescArt, ContentArt, idCategory, idLevel, idTypeArt, Featured) VALUES('");
    sbInsertArticle.Append(name); sbInsertArticle.Append("', '");
    sbInsertArticle.Append(title); sbInsertArticle.Append("', '");
    sbInsertArticle.Append(keywords); sbInsertArticle.Append("', '");
    sbInsertArticle.Append(desc); sbInsertArticle.Append("', '");
    sbInsertArticle.Append(content); sbInsertArticle.Append("', '");
    sbInsertArticle.Append(idCategory.ToString()); sbInsertArticle.Append("', '");
    sbInsertArticle.Append(1); sbInsertArticle.Append("', '");
    sbInsertArticle.Append(1); sbInsertArticle.Append("', '");
    sbInsertArticle.Append(strFeatured); sbInsertArticle.Append("')");

    string strInsertArticle = sbInsertArticle.ToString();
    this.dbConnect.ProcessNonQuery(strInsertArticle);

}

Use placeholders/parameters. Then there is no issue with "those characters". Brief examples are given [by Bobby Tables](http://bobby-tables.com/csharp.html). Many more examples are given with the appropriate ADO/.NET documentation. There are many duplicate "questions" as well. — , Dec 20 '12 at 17:54
Try http://stackoverflow.com/questions/6745393/how-to-add-like-special-characters-in-mysql-varchar-data-type-column — sajanyamaha, Dec 20 '12 at 17:56
There are ways to do what you need using escaping. However, listen to @pst and _use parameterization_. You'll be glad you did down the road... — PinnyM, Dec 20 '12 at 17:58
While many people think that SQL injection is "only" for attackers, not using proper parameterization can also lead to subtle bugs and edge-cases (as you have found!), like this. Here are some SO posts that provide solutions to writing robust insertion code (they also result in *much prettier code*): http://stackoverflow.com/questions/681583/sql-injection-on-insert , http://stackoverflow.com/questions/13932068/which-sql-query-is-more-secure-in-terms-of-sql-injection , http://stackoverflow.com/questions/7174792/does-using-parameterized-sqlcommand-make-my-program-immune-to-sql-injection — , Dec 20 '12 at 17:58
everybody who wants to help ; forget asp.net, C# and php and others .Please suggest only MySQl solution — Oumdaa, Dec 20 '12 at 18:06
@pst can you please forget that there is a C# program!how to insert a text containg such characters in mysql table ??? The DotNet env will simply send the string to mysql to treat it ok ! — Oumdaa, Dec 20 '12 at 18:12
@pst I do know ho to deal with Mysqll, I have succeed inserting selecting and updating tables in mysql so don't send me Mysql tutorials — Oumdaa, Dec 20 '12 at 18:14
@pst dbConnect is a DBConnect object that I have created myself to hadle with the Mysql connectivity :OpenConnection() CloseConnection() ProcessQuery() ProcessNonQuery() and ProcessScalar() methods — Oumdaa, Dec 20 '12 at 18:16
@Oumdaa I'm sorry then. The application is broken because the connector is broken. I suggest using an existing connection infrastructure *or* learning how to design a robust database connector. Good luck. — , Dec 20 '12 at 18:17
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/21470/discussion-between-oumdaa-and-pst) — Oumdaa, Dec 20 '12 at 18:17

score 2 · Answer 1 · 2013-01-02T14:52:24.857

There's probably nothing wrong with the connector. The output of the posted code won't properly escape content.

if content = it's don"t working then output is something like:

INSERT INTO Article(NameArt, TitleArt, keywordsArt,
  DescArt, ContentArt, idCategory, idLevel, idTypeArt, Featured)
  VALUES('foo', 'bar', 'mysql escaping', 'hmph',
    'it's don"t working', '12', '1', '1', '1');

That should fail in any connector.

content = content.Replace("'", "\\'");

would be a start, but you should probably be using something like mysql_real_escape_string() at the very least.

EDIT: further explanation

Run the following lines in a test program:

Console.WriteLine("'");
Console.WriteLine("\'");
Console.WriteLine("\\'");

The first two will output '. The last will output \'.

Finally, this problem cannot be solved in MySQL, nor can it be solved in your connector due to the interface you have chosen to provide to callers. void ProcessNonQuery(string strParam) is too simple to allow for parametrized queries which are necessary for pushing the "escaping" work into the API.

So, any solution you get with this "connector" is going to be specific to the language you are calling it from (C#) since the escaping problem will have to be solved before getting to the DB or API layers. Many people consider this poor methodology which is why they were pushing you towards direct use of ANY of the standard MySQL connectors rather than trying to wrap one of them in your own API.

Thanks. but I have specified that I don't need anu php based solution: only Mysql solution or a c# trick ! — Oumdaa, Dec 22 '12 at 12:07
@Oumdaa This line of code does not work as intended: `content = content.Replace("'", "\'");`. The whole point of this answer is to show what could fix that one problem. There certainly may be other problems in your setup. `"\'"` is equivalent to `"'"`. — , Jan 02 '13 at 14:17

Create MySQL string that is "safe" (escape double quotes)

1 Answers1