c# , Mysql data comparison issue

Question

I am using mysql with asp.net to save data in DB . When I save data in DB after using server.HTMLEncode(), the data is saved after removing \ . This is how I am saving data

INSERT INTO Users(ID,Name) Values(1,Server.HTMLEncode(User.Identity.Name))

In this case if name is XXX\ABC , it is being saved as XXXABC. Slashes are removed while saving in DB.

Next time when I read the same , I need to check if logged in user is the one against whom I saved data so I do following

if ( existingRowEditor == Server.HtmlEncode(User.Identity.Name))
{

}

but the issue is that the above condition is always false because I have following values existingRowEditor="XXXABC" and Server.HtmlEncode(User.Identity.Name) =XXX\\ABC.

So how can I check if the above condition is true?

even if I do not HTMLEncode the name , it still saves XXXABC in DB i.e. removes the \\ from name — khurram
– khurram, Commented Apr 9, 2013 at 6:11
The "code" you've given for how you're saving data is very strange - it's a mixture of C# and SQL. Please show what the code actually looks like. If you're not using parameterized SQL, that could very easily be the problem. — Jon Skeet
– Jon Skeet, Commented Apr 9, 2013 at 6:18

Guffa · Accepted Answer · 2013-04-09 06:07:38Z

HTML encoding is not suitable for encoding data for storage in the database.

The reason that the backslashes disappears is that you are pasting together SQL code, and encoding the text for display in the web page instead of escaping it for being text in an SQL query. The backslash is used as escape character in MySQL, so any bacslashes in the string will escape the next character.

Preferrably you should use parameterised queries instead of concatenating the data into the SQL query. If that is not possible, you must escape the text properly to be in a string literal in SQL code, so you have to replace every backslash with double backsashes, and prepend every apostrophe with a backslash. If you fail to escape it properly, you application will be wide open for SQL injection attacks.

HTML encoding values should be done when you display it in a web page, not before you put it in the datbase.

Andre · Accepted Answer · 2013-04-09 06:23:05Z

0

You could use the following:

var sqlQueryString = "INSERT INTO Users(ID,Name) Values(@Id,@Name)";
var sqlCommand = new SqlCommand(sqlQueryString, sqlConnection);
sqlCommand.Parameters.Add(new SqlParameter("Id", 1);
sqlCommand.Parameters.Add(new SqlParameter("Name", User.Identity.Name);
sqlCommand.ExecuteNonQuery();

answered Apr 9, 2013 at 6:23

Andre

1,0441 gold badge11 silver badges23 bronze badges

Collectives™ on Stack Overflow

c# , Mysql data comparison issue

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related