mysql - 如何在 ColdFusion 中编写区分大小写的查询？我希望用户名和密码区分大小写

Question

如何在 ColdFusion 中编写区分大小写的查询？我希望用户名和密码区分大小写。这就是我所拥有的:

<cfquery datasource="myDataSource" name="qVerify">
    SELECT *
      FROM tblUsers
     WHERE userName = '#userName#' 
       AND password = '#password#'
</cfquery>
<cfif qVerify.RecordCount>
    <cfset session.allowin = "True" />
    <cfset session.ID = qVerify.ID />
    { "login" : "valid" }
<cfelse>
    { "login" : "invalid" }
</cfif>

因此，如果用户 Dave(因为它在数据库中)以 dave 身份登录，它将无效...

Answer 1

这应该将列视为区分大小写，但您应该真正解决密码存储为纯文本的问题。

<!--- MS SQL Server ---> 
<cfquery datasource="myDataSource" name="qVerify">
SELECT *
FROM tblUsers
WHERE userName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#userName#"> COLLATE SQL_Latin1_General_CP1_CS_A
AND password = <cfqueryparam cfsqltype="cf_sql_varchar" value="#password#"> COLLATE SQL_Latin1_General_CP1_CS_A
</cfquery>

---

(根据评论更新)

确切的排序规则名称取决于数据库。对于 MySQL:

<!--- MySQL ---> 
<cfquery datasource="myDataSource" name="qVerify">
SELECT *
FROM tblUsers
WHERE userName = <cfqueryparam cfsqltype="cf_sql_varchar" value="#userName#"> COLLATE latin1_general_cs
AND password = <cfqueryparam cfsqltype="cf_sql_varchar" value="#password#"> COLLATE latin1_general_cs
</cfquery>

参见 documentation on charset/collate了解更多详情。