SQL WHERE子句匹配不区分大小写，如何实现严格区分大小写精确匹配？

问题原因 #

你遇到的字符串匹配不区分大小写的问题，是因为数据库users表的email、password字段默认使用了不区分大小写的排序规则（通常排序规则后缀带_ci的即为大小写不敏感规则，是case insensitive的缩写）。

解决方案 #

方案1：修改字段排序规则（永久生效，推荐）

修改对应字段的排序规则为区分大小写的规则，修改后默认的=比较就会严格校验大小写：

• 如果是MySQL数据库，执行如下SQL修改表结构：

ALTER TABLE users 
MODIFY COLUMN email VARCHAR(255) COLLATE utf8mb4_bin NOT NULL,
MODIFY COLUMN password VARCHAR(255) COLLATE utf8mb4_bin NOT NULL;

• 如果是PostgreSQL数据库，直接使用默认VARCHAR类型即可，若之前配置了不区分大小写规则，可指定排序规则为COLLATE "C"。
• 如果是SQL Server数据库，将字段排序规则改为SQL_Latin1_General_CP1_CS_AS即可（后缀CS代表大小写敏感）。

方案2：查询时临时指定规则（无需修改表结构）

如果不方便修改表结构，可以在编写查询SQL时临时强制指定区分大小写的排序规则，MySQL示例如下：

SELECT email FROM users 
WHERE email = 'some-email@email.com' COLLATE utf8mb4_bin 
AND password = 'pass134' COLLATE utf8mb4_bin;

现有代码的问题修复 #

1. 严重SQL注入漏洞修复：你当前直接将用户传入的参数拼接到SQL语句的写法存在极高的SQL注入风险，必须改为参数化查询，Node.js端正确写法如下：

app.get('/login', function(req, res) {
  const params = new URLSearchParams(req.url.split('?').pop());
  const email = params.get('email');
  const pass = params.get('pass');
  // 用?做占位符的参数化查询，避免SQL注入
  connection.query(
    'SELECT email FROM users WHERE email = ? AND password = ? COLLATE utf8mb4_bin',
    [email, pass],
    function(err, result) {
      if (err) throw err;
      if (result.length > 0 && result[0]?.email) {
        // 登录成功逻辑
      } else {
        // 账号或密码错误逻辑
      }
    }
  );
});

2. 你贴出的原SQL中password '${pass}'缺少=符号，会直接触发语法报错，需要补全。

额外安全提示 #

生产环境禁止明文存储用户密码，必须使用bcrypt、Argon2等不可逆加密算法对密码做哈希处理后再存储，校验时将用户传入的密码加密后再和数据库存储的哈希值比对，避免密码泄露风险。

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