how to avoid single quote in sql queary

Question

I have To search data from database. My query is like as select * from table where subject like'%'+txtsearch+'%' and here my textbox has value like ----- teacher's book this query will give error as syntax error near----- 's Then how to execute my query,i don't want to remove single quote

Manish Dwivedi · Answer

Hi Khargesh, You can escape single quote using two single quotes (NOT double quote). means if your search keyword is: teacher's book then it should be like that: teacher''s book

Khargesh Rajput · Answer

tahnks sir, i can replace ' to '' in my query

Wim Sturkenboom · Answer

Use parameter queries. Your approach is dangerous as it's prone to sql injection (not only with select, also with insert, update etc) Your query should look like select * from table where subject like @txtsearch Below a piece of code that demonstrates (for an insert) string strconnection = "Data Source=WIM_LT-PC\SQL2012;Initial Catalog=testje;User ID=northwind;Password=northwind"; try { SqlConnection conn = new SqlConnection(strconnection); conn.Open(); SqlCommand cmd = new SqlCommand( "INSERT INTO tblImages(ImageFile, Front_Image, FIO, FIL) VALUES (@ImageFile, @Front_Image, @FIO, @FIL)", conn); cmd.Parameters.AddWithValue("@ImageFile", ImageName); cmd.Parameters.AddWithValue("@Front_Image", byteArray); cmd.Parameters.AddWithValue("@FIO", FrontImageOffset.ToString()); cmd.Parameters.AddWithValue("@FIL", FrontImageLength.ToString()); cmd.ExecuteNonQuery(); conn.Close(); } catch(Exception ex) { // for a windows forms application MessageBox.Show(ex.Message); } This insert query uses 4 parameters (starting with '@'); before executing the query, the values for the 4 parameters are added. I have not tested this with wildcard; for you to figure that one out. PS: I know it's dangerous to assume, but above is for C# and assumes a winform or webform (seeing that you mention textbox)