CODEWITHSUNDEEP
c#.netcharacter-encodingspecial-characters
Alex
Alex

Reputation: 11157

string " ' " issue when inserting into DB

i am using sqlite client for windows phone for my database. I run into an issue regarding text formatting in this code :

cmd.CommandText = @" Insert into Restaurants (address,description,id,latitude,longitude,name,opening_hours,phone,sandwich,price,updated_at,website,score,rating_count,thumbnail_url) values ('" + r.address + "','" + r.description + "',"+r.id +","+r.latitude+","+r.longitude+",'"+r.name+"','"+r.opening_hours+"','"+r.phone+"','"+r.sandwich+"','"+r.price+"','"+r.updated_at+"','"+r.website+"',"+r.score+","+r.rating_count+",'"+r.thumbnail_url+"')";
cmd.ExecuteScalar();

The issue is that the text fields maybe like "xyz it's abc" and so the ' breaks my update command. How can i keep the ' and make my code run?

Upvotes: 0

Views: 107

Answers (4)

Wouter de Kort
Wouter de Kort

Reputation: 39898

You could escape the characters by doing something like:

cmd.CommandText = cmd.CommandText.Replace("'", "''");

But building a query the way you do could lead to some serious security risks. Have a look at the following article which describes how to change dynamic sql to use sql parameters:

Upvotes: 0

Grant Thomas
Grant Thomas

Reputation: 45058

Instead of using a verbatim query string, which is (more) open to attack, use parameters instead:

cmd.Parameters.Add("@Param0", SqlDbType.VarChar, 80).Value = "value";

Upvotes: 4

KV Prajapati
KV Prajapati

Reputation: 94653

Use Parameter instead of hard coded string (query).

cmd.CommandText = @"Insert into Restaurants 
    (address,description,id,latitude,longitude,name,opening_hours,
      phone,sandwich,price,updated_at,website,score,rating_count,thumbnail_url) 
  values 
    (@address,@description,@id,@latitude,@longitude,@name,@opening_hours,
      @phone,@sandwich,@price,@updated_at,@website,@score,
        @rating_count,@thumbnail_url)";

Upvotes: 4

Kinexus
Kinexus

Reputation: 12904

Consider using stored procedures or parameterised queries rather than direct SQL. This will have the added benefit of making your code less susceptible to issues.

Upvotes: 1

Related Questions