Help Regarding SQL injection

Question

Please help me to prevent my data from SQL injection. I have replaced ' with '' (single quote with 2 quote) while doing any operation on sql server. Please tell me what all i need to do , to prevent my application from SQL injection. my application is in asp.net 2.0

i will use parameterized queries but what about my old projects.. i mean what about where i have written a string query and sending it to sql server as a commandtext.

Please tell me can any one insert sql injection even i have replaced ' with ''?

Answer 1

The best you can do is to use parameterized queries, if the language/framework supports it.

EDIT: asp.net can handle it. Use SqlCommand

An example from here -

private static void UpdateDemographics(Int32 customerID,
    string demoXml, string connectionString)
{
    // Update the demographics for a store, which is stored 
    // in an xml column. 
    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

Answer 2

Instead of cleaning up the SQL manually, you should be using a library to access SQL.

Do not build up query string manually and if you need to pass parameters through, use parameterized queries and stored procedures.

See this example in VB.NET.

Answer 3

I'm not certain, but I don't think there's any quick easy way to protect your old projects from SQL injection attacks.

I think your best bet would probably be to actually modify the data access code in your old projects to use parameterised queries.

Or, you could do as Oded suggests and re-write your old projects using a library.