Error encountered inserting into a database in C#

Question

I am inserting values into a database but i get an error when the value to be inserted has an apostrophe e.g Jo's. How do i resolve this? This is my code

     SqlDataReader readerReference = cmd_Reference.ExecuteReader();

        int loginIndex = readerReference.GetOrdinal("a column");
        int DomCodeLoginIndex = readerReference.GetOrdinal("a column");
        int dom_domain = readerReference.GetOrdinal("dom_domaine"); 
        int emp_surname = readerReference.GetOrdinal("a column");
        int emp_name = readerReference.GetOrdinal("a column");
        int srv_name = readerReference.GetOrdinal("a column");
        int emp_model_tel = readerReference.GetOrdinal("a column");
        int emp_EntryDate = readerReference.GetOrdinal("a column");
        int emp_Telephone = readerReference.GetOrdinal("a column");
        int emp_Mobile = readerReference.GetOrdinal("a column");
        int emp_Email = readerReference.GetOrdinal("a column");


        string insert = "INSERT INTO EMP_tracking(emp_login, emp_dom_code, emp_domain,   emp_affectation_date, emp_surname" +
            ",emp_name, emp_service,emp_telephone_model" +
            ",emp_entry_date" +
            ",emp_telephone_number" +
            ",emp_cellphone_number" +
            ",emp_email) VALUES ('{0}',{1},'{2}','"+DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss") +"','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}')";




    string toInsert = string.Format(insert, readerReference.GetString(loginIndex),
                                            readerReference.GetInt32(DomCodeLoginIndex ), 
                                            readerReference.GetString(dom_domain),
                                            readerReference.GetString(emp_surname ),
                                            readerReference.GetString(emp_name), 
                                            readerReference.GetString(srv_name), 
                                            readerReference.GetString(emp_model_tel), 
                                            readerReference.GetDateTime(emp_EntryDate), 
                                            readerReference.GetString(emp_Telephone),
                                            readerReference.GetString(emp_Mobile), 
                                            readerReference.GetString(emp_Email)
                                   );      


    cmd_Insert.CommandText = toInsert;
    cmd_Insert.ExecuteNonQuery();

Let's say the column concerned is emp_login. How will the solution be implemented on it?

You have to escape the apostrophe – jAC Feb 04 '13 at 11:12 — jAC, Feb 04 '13 at 11:12

score 2 · Answer 1 · answered Feb 04 '13 at 11:13

2

Use Parameterized queries , that will resolve your issue and will save you from SQL injection

See this tutorial: C-Sharp Database Parameterized Insert Tutorial

answered Feb 04 '13 at 11:13

Habib

219,104
29
407
436

score 2 · Accepted Answer · answered Feb 04 '13 at 11:14

This question comes up over and over again. You should not create SQL strings using concatenation - instead use paremeters:

string insert = "INSERT INTO EMP_tracking(emp_login, emp_dom_code, emp_domain,   emp_affectation_date, emp_surname" +
        ",emp_name, emp_service,emp_telephone_model" +
        ",emp_entry_date" +
        ",emp_telephone_number" +
        ",emp_cellphone_number" +
        ",emp_email) VALUES (@emp_login, @emp_dom_code, //... etc.";

cmd_Insert.Parameters.AddWithValue("@emp_login", readerReference.GetString(loginIndex));
cmd_Insert.Parameters.AddWithValue("@emp_dom_code", readerReference.GetInt32(DomCodeLoginIndex));
//etc...

score 1 · Answer 3 · edited May 23 '17 at 11:48

1

You can try escaping the apostrophe with another apostrophe.

There's a similar question here: How to insert a value that contains an apostrophe (single quote)?.

Warning: SQL injection alert. Don't allow db manipulation except through parameters.

edited May 23 '17 at 11:48

Community

1
1

answered Feb 04 '13 at 11:12

mcalex

6,628
5
50
80

Error encountered inserting into a database in C#

3 Answers3