手动格式化 SQL 语句时(当涉及输入参数时，您真的不应该这样做！)，仅将参数值括在引号中是不够的.您还需要转义参数数据中的保留字符，否则您仍然容易受到注入攻击.攻击者可以简单地在数据中放置一个匹配的引号，关闭您的开场引号，然后其余的参数数据可以包含恶意指令.

When formatting an SQL statement by hand (which you really SHOULD NOT do when input parameters are involved!), it is not enough to merely wrap parameter values in quotes. You need to also escape reserved characters inside of the parameter data, otherwise you are still susceptible to injection attacks. The attacker could simply place a matching quote inside the data, closing off your opening quote, and then the rest of the parameter data can contain malicious instructions.

例如:

const char *p_name = "'); DROP TABLE MyTable; --";
sprintf(sql, "INSERT INTO MyTable(NAME) VALUES ('%s')", p_name);

或者:

const char *p_name = "\"); DROP TABLE MyTable; --";
sprintf(sql, "INSERT INTO MyTable(NAME) VALUES (\"%s\")", p_name);

这些将创建以下 SQL 语句:

These would create the following SQL statements:

INSERT INTO MyTable(NAME) VALUES (''); DELETE TABLE MyTable; --')

INSERT INTO MyTable(NAME) VALUES (""); DELETE TABLE MyTable; --")

说再见"执行 SQL 时到您的表！(假设执行 SQL 的用户具有对表的 DELETE 访问权限 - 这是它自己的另一个安全问题).

Say "bye bye" to your table when the SQL is executed! (assuming the user executing the SQL has DELETE access to the table - that is a whole other security concern of its own).

在这种情况下，您需要将参数数据中的任何单引号字符加倍，或斜线转义任何双引号字符，例如:

In this case, you would need to double up any single-quote characters, or slash-escape any double-quote characters, that are in the parameter data, eg:

const char *p_name = "'); DROP TABLE MyTable; --";
char *p_escaped_name = sqlEscape(p_name); // <-- you have to implement this yourself!
sprintf(sql, "INSERT INTO MyTable(NAME) VALUES ('%s')", p_escaped_name);
// or:
// sprintf(sql, "INSERT INTO MyTable(NAME) VALUES (\"%s\")", p_escaped_name);
free(p_escaped_name);

因此，生成的 SQL 语句将如下所示:

Thus, the resulting SQL statements would look like these instead:

INSERT INTO MyTable(NAME) VALUES ('''); DELETE TABLE MyTable; --')

INSERT INTO MyTable(NAME) VALUES ("\"); DELETE TABLE MyTable; --")

因此，插入表中的名称将是 ');删除表 MyTable；-- (或 ");删除表 MyTable；--).不漂亮，但表格会被保存.

Thus, the name inserted into the table would be '); DELETE TABLE MyTable; -- (or "); DELETE TABLE MyTable; --). Not pretty, but the table would be saved.

某些数据库框架提供了为您执行此转义的函数，但我在 sqlite 中没有看到，因此您必须在自己的代码中手动实现它，例如:

Some DB frameworks offer functions to do this escaping for you, but I don't see one in sqlite, so you will have to implement it manually in your own code, eg:

char* sqlEscape(const char *str)
{
    int len = strlen(str);
    int newlen = len;

    for (int i = 0; i < len; ++i) {
        switch (str[i]) {
            case '\'':
            case '"':
                ++newlen;
                break;
        }
    }

    if (newlen == len)
        return strdup(str);

    char *newstr = (char*) malloc(newlen + 1);
    if (!newstr)
        return NULL;

    newlen = 0;
    for (int i = 0; i < len; ++i) {
        switch (str[i]) {
            case '\'':
                newstr[newlen++] = '\'';
                break;
            case '"':
                newstr[newlen++] = '\\';
                break;
        }
        newstr[newlen++] = str[i];
    }

    newstr[newlen] = '\0';

    return newstr;
}

准备好的语句通过让数据库引擎在执行准备好的语句时为您处理这些详细信息，避免了手动执行此转义的需要.

A prepared statement avoids the need to do this escaping manually, by letting the DB engine handle these details for you when executing the prepared statement.

此外，您的代码对 sprintf() 的使用容易受到缓冲区溢出的影响，甚至更糟，因为精心设计的缓冲区溢出可以让攻击者在您的应用程序中执行任意机器代码，而不仅仅是在数据库中.改用 snprintf() 来避免这种情况.

Also, your code's use of sprintf() is susceptible to buffer overflows, which is even worse, because a carefully crafted buffer overflow can let an attacker execute arbitrary machine code inside your app, not just in the database. Use snprintf() instead to avoid that.