StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<ol> <li><blockquote> <p>How can I make userid auto increment?</p> </blockquote> <p>Specify the <a href="http://dev.mysql.com/doc/en/example-auto-increment.html" rel="nofollow noreferrer"><code>AUTO_INCREMENT</code></a> attribute on the <code>userid</code> column:</p> <pre><code>CREATE TABLE `users` ( `userid` INT(6) NOT NULL AUTO_INCREMENT, -- etc. </code></pre></li> <li><blockquote> <p>How do you store passwords as an MD5 hash? Also, I've read various coders strongly recommending brcrypt, thoughts?</p> </blockquote> <p>The main reason for recommending bcrypt over MD5 for hashing passwords is that bcrypt was designed for that purpose, whereas MD5 was designed to verify the integrity of messages: thus bcrypt is intentionally slow, whereas MD5 is intentionally fast. This means that it requires substantially more work for an attacker to brute-force bcrypt hashes versus MD5 ones.</p> <p>Since both functions produce fixed-size binary output (of 16 bytes in the case of MD5 and 56 bytes in the case of bcrypt), a sensible column type is <a href="http://dev.mysql.com/doc/en/binary-varbinary.html" rel="nofollow noreferrer"><code>BINARY</code></a>: <code>BINARY(16)</code> for MD5 and <code>BINARY(56)</code> for bcrypt.</p> <p>In either case, you should be sure to <em>salt</em> your hashes. Salt is a random string that is concatenated with the user's password before the (final) hash is calculated: the salt that is used is stored with the user record in the database, but is different for each user. This defeats <em>rainbow table</em> attacks to recover users' passwords should your database ever become compromised.</p> <p>The actual code involved in performing these actions will depend on the language, libraries and/or frameworks with which you are developing your application.</p></li> <li><blockquote> <p>How can I set the default <code>user_handle</code> to be the first part of an email before the <code>@</code> symbol? Such that <code>john@smith.com</code> would yield a user handle of <code>john</code>.</p> </blockquote> <p>Such logic is probably most suited to your application code, but it can also be done in the SQL <code>INSERT</code> statement using MySQL's <a href="http://dev.mysql.com/doc/en/string-functions.html#function_substring-index" rel="nofollow noreferrer"><code>SUBSTRING_INDEX()</code></a> function:</p> <pre><code>INSERT INTO users VALUES ( -- [ deletia ] SUBSTRING_INDEX('john@smith.com', '@', 1), -- [ deletia ] ); </code></pre></li> <li><blockquote> <p>Any extra security measures I should take when designing a user database?</p> </blockquote> <p>I highly recommend that you read <a href="https://stackoverflow.com/a/477578/623041">this excellent post</a> for a thorough explanation of related security concepts.</p></li> <li><blockquote> <p>The foreign key in other tables assocated with users would need a foreign key that points to <code>userid</code> in users?</p> </blockquote> <p>Yes. A foreign key exists by virtue of it being used for that purpose; if you wish to enforce <a href="http://dev.mysql.com/doc/en/innodb-foreign-key-constraints.html" rel="nofollow noreferrer">foreign key <em>constraints</em></a> in MySQL (that is, ensuring that the referenced record exists in the foreign table), you will need to be using the InnoDB storage engine for both the local and foreign tables; furthermore, indexes must exist on both the local and foreign copies of the key. The constraint is then defined in the referencing table using a clause like:</p> <pre><code>FOREIGN KEY (userid) REFERENCES users (userid) </code></pre></li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload