Sanitizing SQL data

Question

Google turns up all sorts of discussions on sanitizing queries for web access but I'm not finding anything addressing what I'm concerned with:

Sanitizing user input data in a c# program. This must be done by a reversible transformation, not by removal. As a simple example of the problem I don't want to mangle Irish names.

What's the best approach and is there any library function that does it?

Answer 1

It depends on what SQL Database you are using. For instance if you want a single quote literal in MySQL you need to use a backslash, Dangerous: ' and an escaped escaped character literal: \'. For MS-SQL things are completely different, Dangerous: ' escaped:''. Nothing is removed when you escape data in this fashion, it a way of representing a control character such as a quote mark in its literal form.

Here is an example of using parameterized queries for MS-SQL and C#, taken from the Docs:

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

For MySQL i am not aware of a parameterized query library you can use. You should use mysql_real_escape_string() or opointally you could use this function.:

public static string MySqlEscape(this string usString)
{
    if (usString == null)
    {
        return null;
    }
    // SQL Encoding for MySQL Recommended here:
    // http://au.php.net/manual/en/function.mysql-real-escape-string.php
    // it escapes \r, \n, \x00, \x1a, baskslash, single quotes, and double quotes
    return Regex.Replace(usString, @"[\r\n\x00\x1a\\'""]", @"\$0");
}