设计难题:如果已经使用了电子邮件地址，则发送电子邮件“已注册的电子邮件地址"，但是由于无法将重复项添加到表而无法发送 [英] Design dilemma: If e-mail address already used, send e-mail "e-mail address already registered", but can't because can't add duplicate to table

问题描述

注册表格要求提供用户名和电子邮件地址.数据通过验证后，将被添加到accounts表中.它存储诸如用户名，密码和电子邮件地址之类的数据.如果已经使用了用户名，则将通知用户，并且不会将任何数据添加到表中.如果立即通知用户，这被视为安全问题.电子邮件地址已在表格中.建议的解决方案是始终发送验证电子邮件，如果输入的用户名已经存在，则电子邮件会显示"此电子邮件地址已被使用"(并且没有激活链接)当然会给出.)

A registration form asks for username and e-mail address. After the data passes validation it's added to the accounts table. It stores data such as username, password and e-mail address. If the username has already been taken the user will be notified and no data will be added to the table. It was seen as a security issue if the user was immediately notified that the e-mail address was already in the table. The suggested solution to this was to always send a verification e-mail and if the entered username already existed the e-mail would say "this e-mail address has already been used" (and no activation link would be given of course).

问题是因为它现在可以正常运行，如果INSERT查询无法将数据插入表中，则会显示消息"Usernametake".这可能是错误的，因为在表中将电子邮件地址设置为唯一，因此如果输入相同的电子邮件地址，查询将失败.我再也无法发送验证邮件说"此电子邮件地址已被使用"，因为表中没有包含该电子邮件地址的记录.

The problem is that as it works now, if the INSERT query fails to insert the data into the table the message "Username taken" is shown. This may be wrong because the e-mail address is set to unique in the table so the query fails if the same e-mail address is entered. I can no longer send out a verification e-mail saying "this e-mail address has already been used" because there is no record in the table containing said e-mail address.

如何重新设计系统使其正常工作?

How can I redesign the system so it works?

我正在使用MySQL，表accounts具有主键username唯一键e-mail以及属性password和activation.

I'm using MySQL and the table accounts has the primary key username unique key e-mail and the attributes password and activation.

if(mysqli_stmt_execute($createAccount))
    echo 'Username available!';
else
    echo 'Username unavailable!';

在SQL中，有什么方法可以检查为什么不能将查询插入表中?例如，它可以判断哪个属性具有重复值吗?

In SQL is there some way to check to see why the query couldn't be inserted into the table? For example could it tell which attribute had a duplicate value?

如果我的问题不清楚，请告诉我.

Please let me know if my question is unclear.

推荐答案

首先运行SELECT查询以识别重复项.如果找不到重复项，则可以执行INSERT.

Run a SELECT query first to identify duplicates. If no duplicates are found, then you can perform your INSERT.

这篇关于设计难题:如果已经使用了电子邮件地址，则发送电子邮件“已注册的电子邮件地址"，但是由于无法将重复项添加到表而无法发送的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！