SQL查询，返回表格还是行？

Question

在我的Web应用程序中，我编写了一个查询来检查用户ID和密码。如果用户ID失败，则编写查询以显示用户ID错误；如果密码错误，则显示密码错误。我编写的查询只返回int类型，但我想返回表或数据行，请问你能给我一个解决方案吗？

这是我的查询：

用户表包括用户名、电子邮件地址、电话号码、国家等信息。

create procedure [dbo].[sp_users_login] (
  @username varchar(30),
  @password varchar(30),
  @ret int output)
as
 if exists (select username from users where username=@username)
   if exists (select [password], username 
                from users 
                where [password]=@password 
                  and username=@username)
     set @ret =1
   else 
     set @ret=2 
 else 
   set @ret=3

我的查询只会返回一个整数，但我也需要用户的详细信息。例如：用户名，电子邮件等。我想要特定用户的所有详细信息 - 这个查询是否可能？

- Surya sasidhar

---

为什么要返回无效的用户名/密码信息？提供的信息可能与多个记录匹配，而不仅仅是一个。 - OMG Ponies

请注意：出于明显的安全原因，不应提供关于是用户 ID 还是密码验证失败的信息。 - Mitch Wheat

我同意，但他们可能不会将该信息提供给用户，而只是让应用程序考虑它。也许他想发送电子邮件来警告用户有人试图以他们的身份登录。 - Rob Farley

Answer 1

我曾考虑使用HAVING将结果强制限制在一行中，并使用子查询来检查某些内容，但这并不必要。

尝试在SELECT子句中使用聚合函数，以便只返回一行。然后可以使用CASE获取所需信息。

SELECT 
  CASE 
    WHEN COUNT(*) = 0 THEN 'No username' 
    WHEN MAX(password) = @password THEN 'Logged in' 
    ELSE 'Bad password'
  END as LoginStatus,
  MAX(emailaddress) as Email
FROM dbo.Users u
WHERE username = @username
;

对于您需要的所有其他字段，重复使用 MAX(emailaddress)。如果用户名是唯一的（您应该放置一个约束来确保它是唯一的），那么这将很好。如果没有匹配的用户，则这些行将返回为空。但如果他们只是输错了密码，这些字段将被返回，因此请检查您的 LoginStatus 来确定是否应该关注它。

- Rob Farley

Answer 2

select [password], username, email, ...
from users 
where username=@username;

您应该只使用用户名选择感兴趣的字段。不可能有两个不同的用户具有相同的用户名和不同的密码。如果其中一个决定更改其密码，并且新密码恰好与另一个用户的密码匹配怎么办？如果一个用户由于打字错误（例如他们很接近）输入了另一个用户的密码，那么他突然登录到另一个用户帐户中怎么办？首先，您要如何识别用户？

此外，无论用户是否错过了用户名和密码或仅密码，您都应返回相同的错误“用户名或密码不正确”。返回不同的错误消息是信息泄露，通过披露随机尝试的用户是否存在，您只是为黑客提供服务。

- Remus Rusanu

Answer 3

你可以从SQL存储过程中返回0到多个记录集，以及输出参数。

在T-SQL中，记录集类似于表或数据行。

因此，以你的示例存储过程为例，你可以这样做：

create procedure [dbo].[sp_users_login] ( 
  @username varchar(30), 
  @password varchar(30), 
  @ret int output) 
as 
 if exists (select username from users where username=@username) 
 begin
   if exists (select [password], username  
                from users  
                where [password]=@password  
                  and username=@username) 
     set @ret =1 
   else  
     begin
         set @ret=2
     end  
   -- The select below is just an example - modify it to your needs
   select * from users 
   where username = @username
 end
 else  
   set @ret=3 

现在您拥有一个包含用户详细信息的记录集，以及您的int输出值。

但是，这个答案有三个注意点：

• 我提供的SQL可以根据使用和实际数据进行改进，它只是用于显示选择到记录集中
• 访问记录集和输出参数的方式将取决于您的数据访问技术。
• 以您目前的方式在数据库中存储密码可能不是一个好主意。

- David Hall

---

正如OMG Ponies所说，从返回无效密码用户名对的详细信息方面来看，您尝试做的可能没有太多意义。 - David Hall

顺便说一句，只有在使用了返回的所有行集之后，才能访问输出参数。 - Cade Roux

@Cade Roux - 谢谢你提到这个。我很少使用输出参数，所以这个事实是我每次被咬到时才记得的东西之一。 - David Hall

@Surya - 存储用户身份验证数据的数据库有许多替代方案，例如使用Active Directory。然而，问题不在于使用数据库，而在于如何使用它。您应该考虑使用一些现成的身份验证提供程序，而不是自己编写。如果您确实拥有自己的密码存储库，则数据库是可以的，但只要您不将密码以明文形式存储，而是将其作为加盐单向哈希的结果进行存储。 - David Hall

（警告：我不是安全专家）只要您使用盐值加密密码，使用md5应该是可以的。 - David Hall

Answer 4

• 不要将密码存储在数据库中 - 存储加盐哈希（还要注意默认的不区分大小写排序规则将导致用户名和密码都不区分大小写比较 - 通常密码是区分大小写的）。您可以使用适当的加盐数据和适当的哈希函数选择 T-SQL hashbytes 函数。
• 如果用户名有效但密码无效或反之，则不要向用户指示任何差异，此信息也可用于协助攻击。
• 客户端代码应检查结果集中是否存在行并采取适当措施，没有行表示没有登录可进行 - 在消耗所有行之前，您无法获取输出参数，我IRC。

这是我推荐的代码（我省略了输出参数，并假设密码没有存储而是散列 - 密码只是用用户名加盐来创建哈希（这通常不是最佳选择，特别是如果您允许用户名更改并且未适当重新获取密码以重新存储新哈希，则用户名通常不区分大小写））：

create procedure [dbo].[sp_users_login] (
  @username varchar(30),
  @password varchar(30)
)
as
    select username -- , addition users.columns 
    from users 
    where pwhash = HashBytes('SHA1', UPPER(@username) + @password) 
        and username = @username)

你还应该看一下这些问题：

将盐与哈希值一起存储是否安全？

加密、哈希和密码问题，完全不懂？

盐生成和开源软件

PHP密码的安全哈希和盐

- Cade Roux

---

谢谢Cade Roux先生的详细解释，我会检查这些问题。感谢您的回复。 - Surya sasidhar