Escape single quote in sql query c#

Question

Here I have this method in my CandidateOp class file.

public SqlDataReader getpartyID(string partyName)
{
    string query = "EXEC partyIDtoInsert'" +partyName+ "'";
    return new DataAccessLayer().executeQuerys(query);
}

I'm passing the ComboBox text in the form and I am getting the ID to the integer type variable tempPrID.

SqlDataReader reader02 = new CandidateOP().getpartyID(cmbParty.Text);
if (reader02.HasRows)
{
    while (reader02.Read())
    {
         tempPrID = (Int32)reader02[0];
    }
    reader02.Close();
}
else
{
    MessageBox.Show("Please enter a valid Party Name", "Invalid DATA");
} 

The partyIDtoInsert, is a stored procedure I have created and it is being called in the method getpartyID as shown before, to get the id of united national party.

EXEC partyIDtoInsert 'United National Party'; 

If I have a party name called "United People's Freedom Alliance", so If I like to insert that name, my stored procedure executing code it looks like this.

EXEC partyIDtoInsert 'United People's Freedom Alliance';

In this case the it considers the end point as People's single quote and returns me with sql exception.

How to escape this single quote problem in the statement to execute my stored procedure.

Answer 1

You should Use SqlParameters to avoid SQL Injection

string query = "EXEC partyIDtoInsert @PartyName";
Where you are executing the query

SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@PartyName", partyName);

Otherwise, the problem is that you don't have " " between partyIDtoInsert and 'United People's Freedom Alliance'. If you want to continue using "EXEC partyIDtoInsert '" should be like this. But this is wrong! Read about SQL Injection!

Answer 2

Just replace single quote (') by double single quote like ''.

so your function will look like:

public SqlDataReader getpartyID(string partyName)
            {
                string query = "EXEC partyIDtoInsert'" +partyName.Replace("'", "''") + "'";
                return new DataAccessLayer().executeQuerys(query);
             }

I hope it will solve your problem. :)