How to put double quotes in ADO.NET query?

Question

I am trying to prevent any SQL injection in all my queries and would like to know how to put double quotes in this query. Thanks

string.Format("SELECT TOP 10 article_guid, article_title 
               FROM article 
               WHERE article.article_isdeleted = 0 AND 
                     FREETEXT(article_title, @val)");

Answer 1

I'm not sure if double quotes will help you (which you can add if you like by escaping the quote, as in \"). What I've done in the past is to be mindful of single quotes, so I performed a replace on the content of @val prior to including it in the query, as in val.Replace("'", "''").

Answer 2

To prevent SQL Injection you must only use SqlParameter objects for all your queries, like so:

SqlCommand  command = new SqlCommand("update tblFoo set x = @x");
SqlParamter param   = new SqlParameter("@x", SqlDbType.NVarChar);

param.Value = "hello\"";

command.Parameters.Add(param);

Answer 3

Step 1: Don't do this. Use a parameterized query instead.

Parameterized queries remove most of the risk associated with SQL injection attacks.

From the link:

private void CallPreparedCmd() {
    string sConnString = 
        "Server=(local);Database=Northwind;Integrated Security=True;";
    string sSQL = 
        "UPDATE Customers SET City=@sCity WHERE CustomerID=@sCustomerID";
    using (SqlConnection oCn = new SqlConnection(sConnString)) {
        using (SqlCommand oCmd = new SqlCommand(sSQL, oCn)) {
            oCmd.CommandType = CommandType.Text;

            oCmd.Parameters.Add("@sCustomerID", SqlDbType.NChar, 5);
            oCmd.Parameters.Add("@sCity", SqlDbType.NVarChar, 15);

            oCn.Open();
            oCmd.Prepare();

            oCmd.Parameters["@sCustomerID"].Value = "ALFKI";
            oCmd.Parameters["@sCity"].Value = "Berlin2";
            oCmd.ExecuteNonQuery();

            oCmd.Parameters["@sCustomerID"].Value = "CHOPS";
            oCmd.Parameters["@sCity"].Value = "Bern2";
            oCmd.ExecuteNonQuery();

            oCn.Close();
        }
    }
}

That being said, you can insert quotes into a string by escaping the double quotes like this:

string newstring = " \"I'm Quoted\" ";

Answer 4

Why did you use string.Format? You are using @parameterized query and it is Type-Safe.

Use Type-Safe SQL Parameters for Data Access