CODEWITHSUNDEEP
c#sqlbackslash
user1094315
user1094315

Reputation: 95

Can not read a string that includes a backslash from an sql table

I am trying to read a string that includes a backslash in it from a sql table. For some reason I can not pass the back slash within the string. My string is the user name and its in this format: Domain\username I send it to the table as so:

    SqlCommand scomm = new SqlCommand();
    SqlDataReader sr;
    string nameOfUser = "Domain\userName";
        scomm.CommandText = "SELECT * FROM tableOfUsers WHERE UserName=" + nameOfUser;
sr = scomm.ExecuteReader();

During the debug I can see that another backslash is added as an escape character however I don't know what happens with the sql part and why an exception is thrown. I tried using @ but that didn't help either. I would appreciate any help you can provide me.

Upvotes: 3

Views: 6447

Answers (3)

KV Prajapati
KV Prajapati

Reputation: 94645

Use SqlParameter.

string nameOfUser = @"Domain\userName";
scomm.CommandText = "SELECT * FROM tableOfUsers WHERE UserName=@username";
scomm.Parameters.Add("@username",SqlDbType.VarChar,30).Value=nameOfUser;
//open the connection
SqlDataReader sr=scomm.ExecuteReader();
if(sr.Read())  // use while loop when one or more exists
 {
    Console.WriteLine(sr["columnName"] + " or use column ordinal : " + sr[0]);
 }

Upvotes: 10

Ravi Gadag
Ravi Gadag

Reputation: 15861

Use SqlParameter.Sql Parameter Documentation. as the SqlPrameter Class will provide the functionality to avoid Sql Injections and other issues, which db not allowed. 

string UserName = @"Domain\userName";
scomm.CommandText = "SELECT * FROM tableOfUsers WHERE UserName=@username";
scomm.Parameters.AddWithValue("@username",UserName);

Upvotes: 2

Adam Rackis
Adam Rackis

Reputation: 83356

You can use a string literal

string nameOfUser = @"Domain\userName";

or escape your backslash

string nameOfUser = "Domain\\userName";

Your query also has some errors in it. You theoretically need to put quotes around nameOfUser

string nameOfUser = @"Domain\userName";
scomm.CommandText = "SELECT * FROM tableOfUsers WHERE UserName= '" + nameOfUser + "'";

But you should really use a parameterized query

string nameOfUser = @"Domain\userName";
scomm.CommandText = "SELECT * FROM tableOfUsers WHERE UserName= @name";
scomm.Parameters.AddWithValue("@name", nameOfUser);

This will protect your against Sql Injection attacks, and, more mundanely, will allow your query to work with people whose names have a '—like "O'Reilly"

Upvotes: 5

Related Questions