'Escape single quote in sql query c#

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.

c++sharpsqlsql-server


Solution 1:[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!

Solution 2:[2]

As Hitesh Mistry has provided the answer like this, it worked for me. 

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

I'd like some one to explain how to use with sql parameters. because I'd like to have it implemented in that way. I don't get the answer provided with sql parameters as it is dealing with connection. I'm using connection in seperate class called DBconnect like this. It's called from another class called DataAccessLayer.

This is how I use connection in DBConnect.

        public static string makeConnection()
    {
        string con = ConfigurationManager.ConnectionStrings["MyDb.Properties.Settings.ConString"].ToString();
        return con;
    }

Solution 3:[3]

I've done something like this and I didn't get any exception.

partyName.Replace("'", $"{(char)39}");

Solution 4:[4]

try EXEC partyIDtoInsert 'United People''s Freedom Alliance';

Solution 5:[5]

It is also possible to escape the apostrophie using string replace method like this

str = str.Replace("'", @"\'")

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 ChathurawinD
Solution 3 Mahib
Solution 4 vikas
Solution 5 Dharman

Related Questions