SQL数据清洗

Question

谷歌搜索出各种讨论关于对Web访问进行查询的清理，但我没有找到任何解决我所关注的问题的内容:

在c#程序中清理用户输入数据。这必须通过可逆变换而不是删除来完成。举个简单的例子，我不想破坏爱尔兰人的名字。

最佳方法是什么，并且是否有任何库函数可以执行此操作？

- Loren Pechtel

---

你是指传递可能包含撇号的数据到 SQL 查询吗？如果使用 SQL Parameter 对象，这应该不是一个问题。你将获得数据的净化，并且在你的数据中任何重要的字符都应该被适当地转义。 - Robert Harvey

同意，参数化查询是正确的方式。 - driis

我只通过我的业务逻辑生成SQL查询。 - Jeroen

@RobertHarvey，SQL参数不足。输入撇号会返回SqlException：“字符串后有未关闭的引号 ')'”。 - usefulBee

@usefulBee 任何带有撇号的内容都需要放在参数中。 - Loren Pechtel

Answer 1

这要看你使用的SQL数据库是什么。例如，在MySQL中，如果想要单引号文字，需要使用反斜杠，不安全的写法是：'，转义后的字符文字是：\'。在MS-SQL中，情况完全不同，不安全的写法是：'，转义后是：''。这种方式并不会删除任何数据，而是一种表示控制字符（例如引号）在其字面形式下的方法。

以下是从文档中提取的使用C#和MS-SQL参数化查询的示例：

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

对于MySQL，我不知道是否有可用的参数化查询库。您应该使用mysql_real_escape_string()或可选地使用此函数：

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

- rook

---

我意识到这是一篇较旧的帖子，但是PHP [5.0+]通过MySQLi (http://php.net/manual/en/book.mysqli.php)和PDO库 (http://php.net/manual/en/book.pdo.php)都支持参数化查询。 - Tieson T.

@Tieson T. 和 adodb，以及参数化查询是最佳选择。 - rook

FYI，mysql_real_escape_string已被弃用。 - Owen Johnson

这听起来不错，我尽可能地这样做，但有时由于参数嗅探的性能问题（我的表非常大），我无法使用参数，因此被迫使用硬编码SQL。有关如何对我的SQL进行净化的建议吗？ - Gabriel Espinoza

@Gabriel Espinoza 考虑使用 ORM。没有人阻止您使用手动输入验证，只需确保测试您的代码即可。 - rook

Answer 2

使用一个正确构建的DAL，将SQL参数对象传递给存储过程，您就不必担心这个问题了。实现业务对象和数据访问层(DAL)，对用户输入进行抽象处理，使其不被作为SQL语句执行，而是被识别为值。示例很有趣：

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

- Jimmy Hoffa

---

我认为参数化命令是正确的选择，但是你的示例代码与它们无关！ - Loren Pechtel

@Loren Pechtel：注释说要使用它们，但更重要的是，您希望用户输入填充像User这样的业务对象，作为传输到创建命令和参数的dal。这种抽象将用户与DB分离得更加安全，因为您可以创建UserValidator和其他类似的东西，使其输入不仅安全免受SQL注入，而且还安全免受无效值的影响。 - Jimmy Hoffa

我同意你需要一个检查有效性等方面的层，但这与防止系统因O'Neil先生而窒息是分开的。 - Loren Pechtel

一些SQL API完全没有参数化语句的方法。根据我的经验，这样的系统通常不是完整的数据库系统，而是提供SQL API的产品的附带功能。例如，EMC Documentum有一个名为DQL的ANSI SQL变体，没有记录避免SQL注入的方法。 - Ronnie Overby