CODEWITHSUNDEEP
sqlsecurityado.netsql-injection
user161433
user161433

Reputation: 4519

How to put double quotes in ADO.NET query?

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)");

Upvotes: 1

Views: 2716

Answers (4)

Dan Rigby
Dan Rigby

Reputation: 17883

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\" ";

Upvotes: 4

KV Prajapati
KV Prajapati

Reputation: 94645

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

Use Type-Safe SQL Parameters for Data Access

Upvotes: 0

Noon Silk
Noon Silk

Reputation: 55082

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);

Upvotes: 2

David Andres
David Andres

Reputation: 31781

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("'", "''").

Upvotes: -1

Related Questions