single quotes escape during string insertion into a database

Question

Insertion fails when "'" is used. example string is: He's is a boy. I've attempted to skip the "'" using an escape symbol , but I believe this is not the right way.

textBox3.Text.Replace("'", " \'");
string sql= "insert into gtable (1text,1memo) values ('"+textBox3.Text+"',null)";
        OleDbCommand cmd = new OleDbCommand(sql, con);

        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

I did have the option of replacing "'" with "`" but this changes the text in the db as well. I wish to retain "'" as the same , and also insert it into the db.

codingbiz · Answer 1 · 2014-09-18T14:34:01.330

58

Try this

    string sql= "insert into gtable (1text,1memo) values (@col1,NULL)";
    OleDbCommand cmd = new OleDbCommand(sql, con);
    cmd.Parameters.AddWithValue("@col1",textBox3.Text);
    con.Open();

edited Sep 18 '14 at 14:34

answered Aug 11 '12 at 06:14

codingbiz

26,179
8
59
96

8

+1 By separating the structure of the SQL statement from the data, this eliminates the danger of sql injection (and there is no need to escape anything). – Alex Aug 11 '12 at 07:00
The issue with this is that it requires a connection, you can't get the "compiled" version? For example, I have a separate unit of work open and just want to execute a manual bit of SQL with a parameter I dont want to have to open a new connection – JustAnotherDeveloper Feb 02 '17 at 12:55

score 46 · Accepted Answer · answered Aug 11 '12 at 06:12

46

try

string sql= "insert into gtable (1text, 1memo) " + 
            "values ('" + textBox3.Text.Replace("'", "''") + "', null)";

answered Aug 11 '12 at 06:12

juergen d

201,996
37
293
362

this worked man !! thanks. Well how do you use "''" to escape a single quote. Where did you read this. I want to learn about these special characters which the queries won't accept. – lunchbox Aug 11 '12 at 06:17
16

Technically correct answer but that's asking for trouble. Use parameters, as @codingbiz has suggested. – Alex Aug 11 '12 at 06:51
thk u saved my life – PuntanetDeveloper May 04 '19 at 21:33

score 9 · Answer 3 · answered Aug 11 '12 at 06:13

9

To insert single quotes in database replace ' with ''. In database only single quote will go.

Use this

string sql= "insert into gtable (1text,1memo) values ('" 
            + textBox3.Text.Replace("'", "''") + "', null)";

Rest code is same.

answered Aug 11 '12 at 06:13

Nikhil Agrawal

47,018
22
121
208

This looks like the same as juergen d's answer (_the accepted answer_) but a minute sooner! Why is this not considered? – Matin Kh Aug 11 '12 at 06:37
2

@MatinKh: Today is not my day. – Nikhil Agrawal Aug 11 '12 at 10:36

score 2 · Answer 4 · answered Aug 11 '12 at 06:26

On the MSDN article for String.Replace it says:

Returns a new string in which all occurrences of a specified Unicode character or String in the current string are replaced with another specified Unicode character or String.

On the very first line you are not assigning the value of textBox3.Text to the result of that method call, meaning that absolutely nothing happens.

Furthermore, to escape a quote in SQL Server, you simply use two single-quotes (Note: NOT the same thing as a double-quote).

This should give you the expected outcome:

textBox3.Text = textBox3.Text.Replace("'", "''");

Additionally, you may wish to look into String.Format for your string concatenation needs.

String escapedInput = textBox3.Text.Replace("'", "''");
String sql = String.Format("insert into gtable (1text,1memo) values ('{0}',null)", escapedInput);

score -5 · Answer 5 · edited Aug 27 '15 at 13:39

-5

The best way is:

string Name = Server.HtmlEncode(txtName.Text);

edited Aug 27 '15 at 13:39

bluish

26,356
27
122
180

answered Sep 12 '14 at 11:10

Aki

149
4
13

What does HTML has to do with SQL? – bluish Aug 27 '15 at 13:40

single quotes escape during string insertion into a database

5 Answers5

Linked