CODEWITHSUNDEEP
c#.netsql-server
dragonfly
dragonfly

Reputation: 17783

Addslashes / escape single quote in .NET

I'm maintaining a piece of code that concatenates SQL queries like:

string sql = "SELECT* FROM table WHERE column = '" + value + "'";

Now it turned out that if value contains ' characted it will break SQL Server text statement. I searched a bit for method similar to PHP addslashes, but without success. There's Regex.Escape but it didn't do the trick. Is there any other way to address that issue except calling Replace method on string?:

string sql = "SELECT* FROM table WHERE column = '" + value.Replace("'", "''") + "'";

Thanks, Paweł

Upvotes: 1

Views: 6290

Answers (4)

Sergey K
Sergey K

Reputation: 4114

I would recommend don't use the string concatenation you should use the parameters instead look at the following example

// 1. declare command object with parameter
SqlCommand cmd = new SqlCommand(
    "select * from Customers where city = @City", conn);
// 2. define parameters used in command object
SqlParameter param  = new SqlParameter();
param.ParameterName = "@City";
param.Value         = inputCity;
// 3. add new parameter to command object
cmd.Parameters.Add(param);

Upvotes: 3

Jonathan Wood
Jonathan Wood

Reputation: 67345

And this is a key reason why SQL parameters are generally recommended. If that's not an option, I think String.Replace() is as good as any other method.

Upvotes: 1

Oded
Oded

Reputation: 499302

You are opening your code to a SQL Injection attack - use parametrized queries instead.

As a bonus, such escaping shenanigans will no longer be needed, as the libraries will take care of them for you.

Upvotes: 3

George Duckett
George Duckett

Reputation: 32448

You should be using SqlCommand and SqlParameter instead of building the query as you currently are. As others have said, what you're doing leaves you wide open to injection attacks.

If you can't do that then as you say, Replace is as good an option as any.

Upvotes: 8

Related Questions