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

Answer 2

Use a properly constructed DAL with SQL Parameter objects handed to stored procedures and you don't have to worry about this. Implement business objects and dal to abstract the user input enough that it isn't executed as SQL but rather recognized as values. examples are fun:

public class SomeDal
{
    public void CreateUser(User userToBeCreated)
    {
        using(connection bla bla)
        {
            // create and execute a command object filling its parameters with data from the User object
        }
    }
}

public class User
{
    public string Name { get; set; }
    ...
}

public class UserBL
{
    public CreateUser(User userToBeCreated)
    {
        SomeDal myDal = new SomeDal();
        myDal.CreateUser(userToBeCreated);
    }
}

public class SomeUI
{
    public void HandleCreateClick(object sender, e ButtonClickEventArgs)
    {
        User userToBeCreated = new User() { Name = txtName.Text };
        UserBL userBl = new UserBL();
        userBl.CreateUser(userToBeCreated);
    }
}