how to avoid single quote in sql queary

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

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)

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Blocked Account12y ago
Accepted 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

0
Khargesh Rajput12y ago
tahnks sir,
i can replace ' to '' in my query
0
Wim Sturkenboom12y ago
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)
0