关于mysql语句的转义

It is recommended to use either the mysqli or PDO_MySQL extensions. It is not recommended to use the old mysql extension for new development, as it has been deprecated as of PHP 5.5.0 and will be removed in the future.

 

官方建议使用mysqli的库，因为mysql库会在PHP5.5.0里去掉，所以建议在新项目中使用

mysqli_real_escape_string

 

一，首先看addslashes，它定义中只会对单引号(')，双引号(“)和反斜杠(\)和NULL字符进行转义。它在php源码(php-5.4.14)里的实现很好的说明了这一点。

PHPAPI char *php_addslashes(char *str, int length, int *new_length, int should_free TSRMLS_DC)
{
    /* maximum string length, worst case situation */
    char *new_str;
    char *source, *target;
    char *end;
    int local_new_length;

    if (!new_length) {
        new_length = &local_new_length;
    }
    if (!str) {
        *new_length = 0;
        return str;
    }
    new_str = (char *) safe_emalloc(2, (length ? length : (length = strlen(str))), 1);
    source = str;
    end = source + length;
    target = new_str;

    while (source < end) {
        switch (*source) {
            case '\0':
                *target++ = '\\';
                *target++ = '0';
                break;
            case '\'':
            case '\"':
            case '\\':
                *target++ = '\\';
                /* break is missing *intentionally* */
            default:
                *target++ = *source;
                break;
        }

        source++;
    }

    *target = 0;
    *new_length = target - new_str;
    if (should_free) {
        STR_FREE(str);
    }
    new_str = (char *) erealloc(new_str, *new_length + 1);
    return new_str;
}

 

二，再来看mysql_escape_string的源码，它是直接调用了Mysql C的API中的mysql_escape_string。注意代码里的话：这个函数已经被mysql_real_escape_string取代了，尽量别用这个函数

PHP_FUNCTION(mysql_escape_string)                       
{   
    char *str;
    int str_len;

    if (zend_parse_parameters(ZEND_NUM_ARGS() TSRMLS_CC, "s", &str, &str_len) == FAILURE) {
        return;
    }
    
    /* assume worst case situation, which is 2x of the original string.
     * we don't realloc() down to the real size since it'd most probably not
     * be worth it
     */
    
    Z_STRVAL_P(return_value) = (char *) safe_emalloc(str_len, 2, 1);
    Z_STRLEN_P(return_value) = mysql_escape_string(Z_STRVAL_P(return_value), str, str_len);
    Z_TYPE_P(return_value) = IS_STRING;

    php_error_docref("function.mysql-real-escape-string" TSRMLS_CC, E_DEPRECATED, "This function is deprecated; use mysql_real_escape_string() instead.");
}

 

三，同样，mysql_real_escape_string也调用了mysql C的API mysql_real_escape_string，注意如果填写了第二个参数，这里会检查数据库连接是否可用。

PHP_FUNCTION(mysql_real_escape_string)
{
    zval *mysql_link = NULL;
    char *str;
    char *new_str;
    int id = -1, str_len, new_str_len;
    php_mysql_conn *mysql;


    if (zend_parse_parameters(ZEND_NUM_ARGS() TSRMLS_CC, "s|r", &str, &str_len, &mysql_link) == FAILURE) {
        return;
    }

    if (ZEND_NUM_ARGS() == 1) {
        id = php_mysql_get_default_link(INTERNAL_FUNCTION_PARAM_PASSTHRU);
        CHECK_LINK(id);
    }

    ZEND_FETCH_RESOURCE2(mysql, php_mysql_conn *, &mysql_link, id, "MySQL-Link", le_link, le_plink);

    new_str = safe_emalloc(str_len, 2, 1);
    new_str_len = mysql_real_escape_string(mysql->conn, new_str, str, str_len);
    new_str = erealloc(new_str, new_str_len + 1);

    RETURN_STRINGL(new_str, new_str_len, 0);
}

而还有一个API mysqli_real_escape_string，其实它也调用的是C的API mysql_real_escape_string

PHP_FUNCTION(mysqli_real_escape_string) {
    MY_MYSQL    *mysql;
    zval        *mysql_link = NULL;
    char        *escapestr, *newstr;
    int         escapestr_len, newstr_len;

    if (zend_parse_method_parameters(ZEND_NUM_ARGS() TSRMLS_CC, getThis(), "Os", &mysql_link, mysqli_link_class_entry, &escapestr, &escapestr_len) == FAILURE) {
        return;
    }
    MYSQLI_FETCH_RESOURCE_CONN(mysql, &mysql_link, MYSQLI_STATUS_VALID);

    newstr = safe_emalloc(2, escapestr_len, 1);
    newstr_len = mysql_real_escape_string(mysql->mysql, newstr, escapestr, escapestr_len);
    newstr = erealloc(newstr, newstr_len + 1);

    RETURN_STRINGL(newstr, newstr_len, 0);
}

 四，总结：

1，建议新开发的项目使用转义mysql时使用mysqli_real_escape_string

2,  强烈建议使用 DBMS 指定的转义函数 （比如 MySQL 是 mysqli_real_escape_string()，PostgreSQL 是 pg_escape_string()），但是如果你使用的 DBMS 没有一个转义函数，并且使用 \ 来转义特殊字符，你可以使用这个函数。

3，当magic_qutoes_gpc开关为on时，实际上所有的 GET、POST 和 COOKIE 数据都用被 addslashes。如果要用msyql的escape函数，需要先调用stripslashes函数

4，addslashes会对(NULL), ('), ("), (\)进行转义，而mysql_real_escape_string和mysqli_real_escape_string除了转义addslashes的字符外，还会对(\n), (\r), (Control-Z)进行转义，并且会根据数据库连接的字符集来处理