lose \ when using ExecuteNonQuery

Question

I'm trying to insert a single value into an SQL Database. It works fine as long as I don't insert a "\". If I do, then I lose a "\" in the database.

For example, in the debugger I see this Command Text:

Insert into tblProjekte (Projektbezeichnung) values ('\\bla\\bla\\bla')

But in the SQL Server Profiler I always find this Insert Statement:

Insert into tblProjekte (Projektbezeichnung) values ('\bla\bla\bla')

My source code:

public void InsertProjekt(string Projektbezeichnung)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "Server=localhost; Database=myProjekt; UID=user; PWD=pwd";
    con.Open();

    SqlCommand com = new SqlCommand();
    com.Connection = con;
    com.CommandText = String.Format("Insert into tblProjekte (Projektbezeichnung) values ('{0}')",@Projektbezeichnung);
    int rows = com.ExecuteNonQuery();        
}

After I changed my Source Code to:

SqlCommand com = new SqlCommand("INSERT INTO tblProjekte (Projektbezeichnung) VALUES (@Projektbezeichnung)");
            com.Parameters.AddWithValue("@Projektbezeichnung", Projekt.Projektbezeichnung);

I get this information during debugging: enter image description here

The Value is "\\Tesafilm" the SQLValue is "\Tesafilm"

You really should be using parameters and not inline formatting. This is wide open for SQL injection. Use @param and then add the parameter with value to your command and you should be good. — tsells, Jul 31 '13 at 11:39
The fact that you put `@` before your variable name doesn't mean you're using a parameterized query. You should use a real parameter. — , Jul 31 '13 at 11:39
@hvd I use the @ not as a parmeter I use it for C# not to lose my "\" — Rene Ries, Jul 31 '13 at 13:06
@Ehsan Ullah In SQLServer I received a different string than I expected, I always miss a "\" in my my SQLServer data. — Rene Ries, Jul 31 '13 at 13:08
@CodeCaster thanks for this really helpfull comment. I think this will not really helps me fixing my issue and it also does not help me to understand what I made wrong. — Rene Ries, Jul 31 '13 at 13:10
Other people are responding to your original problem. It is a comment and not an answer for a reason. [Hungarian notation](http://en.wikipedia.org/wiki/Hungarian_notation) is something of the eighties and should be avoided. — CodeCaster, Jul 31 '13 at 13:12
@ReneRies That's not what `@` means before a variable name. Before a variable name, `@` means "whatever follows is not a keyword", and can be used to create variables with names such as "object". `@Projektbezeichnung` and `Projektbezeichnung` mean *exactly* the same thing in C#, since `Projektbezeichnung` already isn't a keyword. — , Jul 31 '13 at 13:17
I think this can help: http://stackoverflow.com/questions/4879152/c-sharp-before-a-string — sickUnit, Aug 01 '13 at 08:39

score 4 · Answer 1 · answered Jul 31 '13 at 11:42

4

Use a parametrized query instead:

public void InsertProjekt(string Projektbezeichnung)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = "Server=localhost; Database=myProjekt; UID=user; PWD=pwd";
    con.Open();

    SqlCommand com = new SqlCommand();
    com.Connection = con;
    com.CommandText = "Insert into tblProjekte (Projektbezeichnung) values (@value)"
    com.Parameters.AddWithValue("@value", Projektbezeichnung);

    int rows = com.ExecuteNonQuery();

}

answered Jul 31 '13 at 11:42

Bas

26,772
8
53
86

1

I think you forgot the '@' into the AddWithValue – Tobia Zambon Jul 31 '13 at 11:43
2

@trippino it works with and without the @. I added it to follow MSDN guidelines. – Bas Jul 31 '13 at 11:45
ok sorry, I usually put the '@' so I thought that it was necessary – Tobia Zambon Jul 31 '13 at 11:50
I'm sorry, but that does not fix my issue, I receive the same information in SQL Server like before. I'm using SQL Server 2012 and run this function as a wcf service. I filled in my TestClient \\After tip from Stackoverflow and received \After tip from Stackoverflow. I lost a \ again. – Rene Ries Jul 31 '13 at 12:07

score 3 · Answer 2 · answered Jul 31 '13 at 11:43

As several comments have pointed out, the \ character is an "escape character" in SQL. When you're inserting it without using a properly escaped string, SQL will strip them out as it interprets them as just being escape-characters.

You're using string.Format() to simulate a parameterized query, and that won't really cut it. If you use SqlCommand.Parameters.AddWithValue(), however, it should fix your issue:

SqlCommand com = new SqlCommand("INSERT INTO tblProjekte (Projektbezeichnung) VALUES (@Projektbezeichnung)");
com.Parameters.AddWithValue("@Projektbezeichnung", Projektbezeichnung);
com.Connection = con;
int rows = com.ExecuteNonQuery();

For more information regarding SqlCommand.Parameters collection, check it out on the MSDN here. It offers a handful of 'add' methods that may be more appropriate for different scenarios - though regular .AddWithValue() should work fine in this case.

update: changed my original .Add() to .AddWithValue() as the MSDN states SqlParameterCollection.Add() has been deprecated in favor of .AddWithValue().

Add is obsolete in your answer. It has been replaced by AddWithValue — Bas, Jul 31 '13 at 11:46
@BasBrekelmans I literally just read that on the MSDN; I'm updating my answer now =] — newfurniturey, Jul 31 '13 at 11:47
I'm sorry, but that does not fix my issue, I receive the same information in SQL Server like before. I'm using SQL Server 2012 and run this function as a wcf service. I filled in my TestClient \\After tip from Stackoverflow and received \After tip from Stackoverflow. I lost a \ again. — Rene Ries, Jul 31 '13 at 12:06

lose \ when using ExecuteNonQuery

2 Answers2