关于散列/存储日期/存储电子邮件的 MySQL 数据库问题

Question

我正在尝试设计一个简单的用户表，它有一个主键 userid，然后另一个表使用 userid 作为 users 的外键表。我在这里有正确的想法吗？

CREATE TABLE `users` (
    `userid` INT(6) NOT NULL,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(50) NOT NULL,
    `date_join` DATE NOT NULL,       <- should I just make this CURDATE instead?
    `user_handle` VARCHAR(50) NOT NULL,
    `date_modified` DATE NOT NULL,
    PRIMARY KEY (`userid`)
)

1) 如何使用户标识自动递增？

2) 如何将密码存储为 MD5 哈希值？另外，我读过各种强烈推荐 brcrypt 的编码员，想法？

3) 如何将默认的 user_handle 设置为电子邮件的第一部分，位于 @ 符号之前？这样 john@smith.com 将产生 john 的用户句柄。

4) 在设计用户数据库时我应该采取哪些额外的安全措施？

5) 与用户关联的其他表中的外键是否需要指向用户中的userid 的外键？

非常感谢大家，我希望这不是太多问题!

Answer 1

1. How can I make userid auto increment?

   指定AUTO_INCREMENT userid 列上的属性:

   CREATE TABLE `users` (
       `userid` INT(6) NOT NULL AUTO_INCREMENT,
       -- etc.
   

2. How do you store passwords as an MD5 hash? Also, I've read various coders strongly recommending brcrypt, thoughts?

   推荐 bcrypt 而不是 MD5 来哈希密码的主要原因是 bcrypt 是为此目的而设计的，而 MD5 是为了验证消息的完整性而设计的:因此 bcrypt 故意慢，而 MD5 故意快。这意味着与 MD5 哈希相比，攻击者需要做更多的工作才能暴力破解 bcrypt 哈希。

   由于这两个函数都产生固定大小的二进制输出(MD5 为 16 字节，bcrypt 为 56 字节)，合理的列类型是 BINARY : BINARY(16) 用于 MD5 和 BINARY(56) 用于 bcrypt。

   无论哪种情况，您都应该确保加盐您的散列。 Salt 是一个随机字符串，在计算(最终)哈希值之前与用户密码连接:使用的 salt 与用户记录一起存储在数据库中，但每个用户都不同。这会阻止彩虹表 攻击，以在您的数据库遭到破坏时恢复用户的密码。

   执行这些操作所涉及的实际代码将取决于您开发应用程序所使用的语言、库和/或框架。

3. How can I set the default user_handle to be the first part of an email before the @ symbol? Such that john@smith.com would yield a user handle of john.

   这样的逻辑可能最适合您的应用程序代码，但也可以使用 MySQL 的 SUBSTRING_INDEX() 在 SQL INSERT 语句中完成。功能:

   INSERT INTO users VALUES (
     -- [ deletia ]
     SUBSTRING_INDEX('john@smith.com', '@', 1),
     -- [ deletia ]
   );
   

4. Any extra security measures I should take when designing a user database?

   我强烈建议您阅读 this excellent post以获得相关安全概念的详尽解释。

5. The foreign key in other tables assocated with users would need a foreign key that points to userid in users?

   是的。外键的存在是因为它被用于该目的；如果你想执行 foreign key constraints在 MySQL 中(即确保引用的记录存在于外部表中)，您需要为本地表和外部表使用 InnoDB 存储引擎；此外，索引必须存在于 key 的本地和外部副本上。然后使用如下子句在引用表中定义约束:

   FOREIGN KEY (userid) REFERENCES users (userid)