循环直到密码唯一 [英] Loop until passcode is unique

问题描述

这是一个文件共享网站.为了确保每个文件唯一的密码"是真正唯一的，我正在尝试:

This is for a file sharing website. In order to make sure a "passcode", which is unique to each file, is truely unique, I'm trying this:

$genpasscode = mysql_real_escape_string(sha1($row['name'].time())); //Make passcode out of time + filename.
    $i = 0;
    while ($i < 1) //Create new passcode in loop until $i = 1;
    {
        $query = "SELECT * FROM files WHERE passcode='".$genpasscode."'";
        $res = mysql_query($query);
        if (mysql_num_rows($res) == 0) // Passcode doesn't exist yet? Stop making a new one!
        {
            $i = 1;
        }
        else // Passcode exists? Make a new one!
        {
            $genpasscode = mysql_real_escape_string(sha1($row['name'].time()));
        }
    }

如果两个用户同时在 exact 上上传了一个具有相同名称的文件，这实际上只能防止输入两次密码，但是，总比后悔好吗?我的问题是；可以按照我的预期工作吗?我无法可靠地(轻松地阅读)对其进行测试，因为即使关闭一秒钟也将生成唯一的密码.

This really only prevents a double passcode if two users upload a file with the same name at the exact same time, but hey better safe than sorry right? My question is; does this work the way I intend it to? I have no way to reliably (read: easily) test it because even one second off would generate a unique passcode anyway.

更新: Lee建议我这样做:

UPDATE: Lee suggest I do it like this:

do {
    $query = "INSERT IGNORE INTO files 
       (filename, passcode) values ('whatever', SHA1(NOW()))";
    $res = mysql_query($query);
} while( $res && (0 == mysql_affected_rows()) )

[编辑":我更新了上面的示例，其中包含两个关键修复程序.请参阅下面的详细信息. -@李]

[Edit: I updated above example to include two crucial fixes. See my answer below for details. -@Lee]

但是我担心它会更新其他人的行.如果文件名和密码是数据库中唯一的字段，那将不是问题.但是除此之外，还要检查mime类型等.因此我想到了这一点:

But I'm afraid it will update someone else's row. Which wouldn't be a problem if filename and passcode were the only fields in the database. But in addition to that there's also checks for mime type etc. so I was thinking of this:

//Add file
        $sql = "INSERT INTO files (name) VALUES ('".$str."')";
        mysql_query($sql) or die(mysql_error());

        //Add passcode to last inserted file
        $lastid = mysql_insert_id();
        $genpasscode = mysql_real_escape_string(sha1($str.$lastid.time())); //Make passcode out of time + id + filename.
        $sql = "UPDATE files SET passcode='".$genpasscode."' WHERE id=$lastid";
        mysql_query($sql) or die(mysql_error());

那将是最好的解决方案吗?最后插入的ID字段始终是唯一的，因此密码也应该是唯一的.有什么想法吗?

Would that be the best solution? The last-inserted-id field is always unique so the passcode should be too. Any thoughts?

UPDATE2:如果行已经存在，显然IGNORE不会替换该行.对我来说这是一个误解，所以这可能是最好的选择！

UPDATE2: Apperenatly IGNORE does not replace a row if it already exists. This was a misunderstanding on my part, so that's probably the best way to go!

推荐答案

严格来说，唯一性测试不能保证并发负载下的唯一性.问题是您在插入行以声明"新生成的密码之前(或与之分开)检查唯一性.另一个进程可能同时在做相同的事情.这是怎么回事...

Strictly speaking, your test for uniqueness won't guarantee uniqueness under a concurrent load. The problem is that you check for uniqueness prior to (and separately from) the place where you insert a row to "claim" your newly generated passcode. Another process could be doing the same thing, at the same time. Here's how that goes...

两个进程生成完全相同的密码.他们每个人都从检查唯一性开始.由于两个进程都尚未(尚未)在表中插入行，因此两个进程都不会在数据库中找到匹配的密码，因此两个进程都将假定该代码是唯一的.现在，随着每个进程继续进行工作，最终它们将都使用生成的代码在files表中插入一行，因此您将得到一个副本.

Two processes generate the exact same passcode. They each begin by checking for uniqueness. Since neither process has (yet) inserted a row to the table, both processes will find no matching passcode in database, and so both processes will assume that the code is unique. Now as the processes each continue their work, eventually they will both insert a row to the files table using the generated code -- and thus you get a duplicate.

要解决此问题，您必须执行检查，并以单个原子"操作进行插入.以下是此方法的说明:

To get around this, you must perform the check, and do the insert in a single "atomic" operation. Following is an explanation of this approach:

如果希望密码唯一，则应将数据库中的列定义为UNIQUE.通过拒绝插入会导致重复密码的行，这将确保唯一性(即使您的php代码没有).

If you want passcode to be unique, you should define the column in your database as UNIQUE. This will ensure uniqueness (even if your php code does not) by refusing to insert a row that would cause a duplicate passcode.

CREATE TABLE files (
  id int(10) unsigned NOT NULL auto_increment PRIMARY KEY,
  filename varchar(255) NOT NULL,
  passcode varchar(64) NOT NULL UNIQUE,
)

现在，使用mysql的SHA1()和NOW()生成您的密码作为插入语句的一部分.将其与INSERT IGNORE ...( docs )组合，然后循环直到成功插入一行:

Now, use mysql's SHA1() and NOW() to generate your passcode as part of the insert statement. Combine this with INSERT IGNORE ... (docs), and loop until a row is successfully inserted:

do {
    $query = "INSERT IGNORE INTO files 
       (filename, passcode) values ('whatever', SHA1(NOW()))";
    $res = mysql_query($query);
} while( $res && (0 == mysql_affected_rows()) )

if( !$res ) {
   // an error occurred (eg. lost connection, insufficient permissions on table, etc)
   // no passcode was generated.  handle the error, and either abort or retry.
} else {
   // success, unique code was generated and inserted into db.
   // you can now do a select to retrieve the generated code (described below)
   // or you can proceed with the rest of your program logic.
}

---

注意:上面的示例经过编辑，以说明@martinstoeckli在评论部分中发表的出色观察.进行了以下更改:

---

Note: The above example was edited to account for the excellent observations posted by @martinstoeckli in the comments section. The following changes were made:

• 将mysql_num_rows()(文档)更改为mysql_affected_rows()(文档)-num_rows不会t适用于插入物.还删除了mysql_affected_rows()的参数，因为此函数在连接级别而不是结果级别上运行(无论如何，插入的结果是布尔值，而不是资源编号).
• 在循环条件中添加了错误检查，并在循环退出后添加了错误/成功测试.错误处理非常重要，因为没有它，数据库错误(例如丢失的连接或权限问题)将导致循环永远旋转.上面显示的方法(使用IGNORE和mysql_affected_rows()，并分别测试$res是否有错误)使我们能够将这些实际数据库错误"与唯一性约束违例(这是完全无效的条件)区分开来.逻辑部分).

• changed mysql_num_rows() (docs) to mysql_affected_rows() (docs) -- num_rows doesn't apply to inserts. Also removed the argument to mysql_affected_rows(), as this function operates on the connection level, not the result level (and in any case, the result of an insert is boolean, not a resource number).
• added error checking in the loop condition, and added a test for error/success after loop exits. The error handling is important, as without it, database errors (like lost connections, or permissions problems), will cause the loop to spin forever. The approach shown above (using IGNORE, and mysql_affected_rows(), and testing $res separately for errors) allows us to distinguish these "real database errors" from the unique constraint violation (which is a completely valid non-error condition in this section of logic).

如果在生成密码后需要获取密码，只需再次选择记录:

If you need to get the passcode after it has been generated, just select the record again:

$res = mysql_query("SELECT * FROM files WHERE id=LAST_INSERT_ID()");
$row = mysql_fetch_assoc($res);
$passcode = $row['passcode'];

编辑:上面的示例更改为使用mysql函数LAST_INSERT_ID()，而不是PHP的函数.这是完成同一件事的一种更有效的方法，并且所生成的代码更清晰，更清晰，更混乱.

Edit: changed above example to use the mysql function LAST_INSERT_ID(), rather than PHP's function. This is a more efficient way to accomplish the same thing, and the resulting code is cleaner, clearer, and less cluttered.

这篇关于循环直到密码唯一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！