string " ' " issue when inserting into DB

Question

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?

The Great Escapism (Or: What You Need To Know To Work With Text Within Text) — deceze
– deceze ♦, Commented Oct 24, 2011 at 9:13
@amit not a good advice: while correct, it won't protect against SQL injection attack. — user447356
– user447356, Commented Oct 24, 2011 at 9:15

KV Prajapati · Accepted Answer · 2011-10-24 09:13:59Z

3

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)";

answered Oct 24, 2011 at 9:13

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Grant Thomas · Accepted Answer · 2011-10-24 09:14:41Z

3

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";

answered Oct 24, 2011 at 9:14

Grant Thomas

45.1k10 gold badges88 silver badges129 bronze badges

2 Comments

Alex Over a year ago

don't have Parameters, this sqlite implementation isn't an official one(windows phone doesn't support sqlite native)

Grant Thomas Over a year ago

@BadescuAlexandru Then AVD's answer is probably your alternative to this solution - you might still need some manual escaping however, to execute successfully.

Kinexus · Accepted Answer · 2011-10-24 09:13:54Z

0

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.

answered Oct 24, 2011 at 9:13

Kinexus

12.9k6 gold badges43 silver badges63 bronze badges

Comments

Wouter de Kort · Accepted Answer · 2011-10-24 09:16:00Z

0

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:

answered Oct 24, 2011 at 9:16

Wouter de Kort♦

40k13 gold badges88 silver badges104 bronze badges

Collectives™ on Stack Overflow

string " ' " issue when inserting into DB

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related