通过限制可用字符和使用REGEXP()防止MySQL中的SQL注入

Question

我正在寻找一种不使用准备语句来防止SQL注入的方法。我知道准备好的语句是防止SQL注入的正确方法，但是MySQL不允许在ALTER准备语句中使用参数，因此我需要另一种方法：

# Will not work
CREATE DEFINER=`root`@`localhost` PROCEDURE `TestProcedure`()
BEGIN
    SET @InputVal = 'NewPassword';
    SET @SQLStr = 'ALTER USER \'SomeUser\'@\'localhost\' IDENTIFIED BY ?';
    
    PREPARE SQLStatement FROM @SQLStr;
    EXECUTE SQLStatement USING @InputVal;
    DEALLOCATE PREPARE SQLStatement;
END

具体目标是创建允许用户更改密码的存储过程。我假设字母数字输入不会导致SQL注入(假设这是一个足够合理的假设，但我愿意纠正，因为我已经看到一些非常有创意的SQL注入，比如backspace字符)：

CREATE DEFINER=`root`@`localhost` PROCEDURE `ChangeCurrentUserPassword`(IN NewPassword VARCHAR(30))
BEGIN
    SET @NewPassword = NewPassword;
    
    IF @NewPassword REGEXP '^[a-z0-9]{1,}$' THEN
        SET @SQLStr = CONCAT('ALTER USER \'', Substring_Index(USER(),'@',1), '\'@\'localhost\' IDENTIFIED BY \'', NewPassword, '\';');
        
        PREPARE SQLStatement FROM @SQLStr;
        EXECUTE SQLStatement;
        DEALLOCATE PREPARE SQLStatement;
    ELSE
        # Non-alphanumeric (or blank) password.
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Password must be alpha-numeric';
    END IF;
END

上面的方法确实是按预期工作的，而且大概是安全的(除了允许单字符密码-只是一次拼凑-测试逻辑)，但我不喜欢它只将密码限制为alpha数字这一事实。我想要尽可能多的非阿尔法数字字符，我可以添加到REGEXP语句。

我见过许多其他线程询问如何使用REGEXP来检测SQL注入，但对于每个线程，它们都要求从任何原始输入中检测。这里的关键区别在于，我不是试图检测SQL注入尝试，而是试图限制允许的字符，以便SQL注入在一开始是不可能的。

有谁知道可以包含但仍然安全地阻止SQL注入的字符列表吗？

旁注:如果还有更好的方法，我很乐意用一种全新的方法来处理这个过程。我知道我可以直接更新mysql.user表，这将允许正确地使用准备好的语句，但我想避免直接更新系统表--我觉得像ALTER这样的语句存在纯粹是因为应该尽可能避免直接更新。

抱歉太长时间了。谢谢你抽出时间讲到这一点！

Answer 1

阅读SQL injection that gets around mysql_real_escape_string()的答案，特别是this one。

这是一个旧的答案，引用了废弃的PHP函数mysql_real_escape_string。它已被mysqli_real_escape_string所取代，它编码了以下特殊字符：

NUL (ASCII 0)，\n，\r，\ '，“和Control-Z

其中\n是换行符(ASCII‘0A’)，\r是“回车”字符(ASCII‘0D’)。

mysqli_set_charset($link, 'utf8');
$s = mysqli_real_escape_string($link, chr(0) . "\n\r\\'" . '"' . chr(26));
var_dump($s);

指纹：

string(14) "\0\n\r\\\'\"\Z"

所以：

确保服务器连接使用字符集使用utf8.

• Either或utf8mb4转义上述7个特殊字符，或者不允许passwords.

• Ensure中的部分或全部字符在转义密码周围有单引号。

因此，例如，如果您只允许字母数字allow加上“@#$%&！_”中的特殊字符，那么就不需要转义。如果你另外允许单引号和双引号，那么你只需要确保这些是用反斜杠转义的。