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 queryi 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)