如何编写适配Express.js登录注册的MySQL存储过程以防范SQL注入

注：你当前使用的?占位符参数化查询本身已经可以防御SQL注入，改用存储过程是等价的实现方案，两种方式安全性没有明显差异。

第一步：在MySQL中创建存储过程 #

首先连接你的MySQL数据库，执行以下两段SQL分别创建注册、登录对应的存储过程：

1. 用户注册存储过程 #

-- 先修改分隔符避免存储过程内的分号被提前解析
DELIMITER //
CREATE PROCEDURE `user_register`(IN p_username VARCHAR(255), IN p_password VARCHAR(255))
BEGIN
  INSERT INTO userinfo (username, password) VALUES (p_username, p_password);
END //
DELIMITER ;

2. 用户登录存储过程 #

DELIMITER //
CREATE PROCEDURE `user_login`(IN p_username VARCHAR(255), IN p_password VARCHAR(255))
BEGIN
  SELECT username, password FROM userinfo 
  WHERE EXISTS (
    SELECT 1 FROM userinfo 
    WHERE userinfo.username = p_username AND userinfo.password = p_password
  );
END //
DELIMITER ;

如果后续需要修改存储过程，先执行DROP PROCEDURE IF EXISTS 存储过程名;再重新创建即可。

第二步：修改Express端代码 #

将原有查询字符串替换为存储过程调用语句即可，参数传递逻辑保持不变：

var username, password;
var userInfoArray = [username, password]
// 替换为存储过程调用语句
var registrationQueryString = "CALL user_register(?, ?)"
var loginQueryString = "CALL user_login(?, ?)"

function userRegistration (userInfoArray){
    dbConnection.query( registrationQueryString, userInfoArray, function(err, results, fields) {
        if (err) throw err
        console.log("registered new user")
    })
}
function userLogin (userInfoArray){
    dbConnection.query( loginQueryString, userInfoArray, function(err, results, fields) {
        if (err) throw err
        // 存储过程返回的结果集嵌套在第一层数组的0号位置，按需要取即可
        console.log(results[0])
    })
}

注意事项 #

• 存储过程创建完成后可以先在MySQL客户端执行CALL user_register('test','test123');、CALL user_login('test','test123');测试功能是否正常，避免业务代码调试时无法定位问题
• 原代码中密码为明文存储，生产环境必须使用bcrypt等加密库对密码做哈希处理后再写入数据库
• 调用存储过程时仍然使用?占位符传递参数，完全避免SQL注入风险

内容的提问来源于stack exchange，提问作者user16789156