SQL server form entering a string with an apostrophe

Question

I have a C# form application which is linked to an SQL server database. The application relays queries to the SQL server and inputs/outputs data accordingly.

An error which came up is when I am trying to input a string which has an apostrophe in it like for example inputting "The dog's bone".

Is there any way to input this since I cannot ask the user to always enter a double apostrophe as many people suggest.

This is my query which I am inputting:

Query = "INSERT INTO " + databaseNotes + " VALUES ('" + Release_Desc_txtBox.Text + "')";
constring = "Password=" + pass + ";Persist Security Info=True;User ID=" + user + ";Initial Catalog=" + catalog + ";Data Source=" + datasource;
// CONNECTION TO SQL SERVER DATABASE
SqlConnection conDataBase = new SqlConnection(constring);
SqlCommand cmdDataBase = new SqlCommand(Query, conDataBase);
SqlDataReader myReader;

Answer 1

If you wanna escape single quotes, you need to double them in your sql query as

The dog''s bone

But I don't recommend it._{Which also you can't}

Use parameterized queries instead which will automatically handled these single quotes without doubling them. And this kind of string concatenations are open for SQL Injection attacks.

Also use using statement to dispose your SqlConnection and SqlCommand automatically. As far as I see, you don't need SqlDataReader since you just try to use INSERT query.

using(var conDataBase = new SqlConnection(constring))
using(var cmdDataBase = conDataBase.CreateCommand())
{
    // Set your CommandText property with parameters.
    // Add your parameters and their values with `Add()` method.
    // Open your connection.
    // Execute your query.
}

By the way, since you take your table name as an input, please do strong validation before you put it in your sql query or go for a whitelist. I hope there are only be a fixed set of possible correct values for your table name.

Answer 2

Use Prepared statements. These are safer to use & also prevents SQL injection attack

https://en.wikipedia.org/wiki/Prepared_statement

Or you have to escape the user input.

But better solution is to use Prepared Statements