Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I've not coded this for myself yet, but I would approach the problem like this:</p> <p><strong>1.</strong> Create a table which can be used to force a validity check when a user presents a cookie for persistence:</p> <pre><code>create table RememberMe ( user_id int(10) NOT NULL, user_token char(10) NOT NULL, token_salt int(6) NOT NULL, time int(10) NOT NULL, PRIMARY KEY (user_id), CONSTRAINT nameYourConstraint FOREIGN KEY (user_id) REFERENCES userTableName (whatever_user_id_equals) ) </code></pre> <p>To populate this table I would add some lines of code to the login, for this example I'll use pseudo-code</p> <pre><code>// userID variable has been sanitized already so // check if user clicked remember me // and if the user logged in successfully: if ( rememberMe == checked &amp;&amp; login() == true ) { // random number to serve as our key: randomNumber = random( 99, 999999 ); // convert number to hexadecimal form: token = toHex( ( randomNumber**randomNumber ) ); // encrypt our token using SHA1 and the randomNumber as salt key = encrypt( token, randomNumber, SHA1 ); // get the number of seconds since unix epoch: // (this will be 10 digits long until approx 2030) timeNow = unix_time() // check to see if user is in table already: sql = "SELECT user_id FROM RememberMe WHERE user_id = 'userID'"; // connect to database: db = new DBCon(); result = db-&gt;query( sql ); // number of rows will always be 1 if user is in table: if ( result-&gt;rows != 1 ) exists = true; else exists = false; result-&gt;free_memory(); if ( exists == true ) { sql = "UPDATE RememberMe SET user_id = 'userID' user_token = 'token' token_salt = 'randomNumber' time = 'timeNow'"; } else { sql = "INSERT INTO RememberMe VALUES( 'userID', 'token', 'randomNumber', 'timeNow' )"; } result = db-&gt;query( sql ); // the affected rows will always be 1 on success if ( result-&gt;affected_rows != 1 ) { print( "A problem occurred.\nPlease log in again." ); quit(); } result-&gt;free_memory(); // create a new cookie named cookiemonster and store the key in it: // (we're not actually storing a score or birthday, its a false flag) set_cookie( "CookieMonster", escape("score="+ userID +"birthday="+ key ); } </code></pre> <p>What this code does is check if the user has checked the remember me and it populates the database table with a key, a token, and a salt for the user as well as a time (so that you can enforce time restraints on the remember me feature).</p> <p>From here you can add code to your website that checks if the <strong>CookieMonster</strong> cookie is set and <em>if it is</em> you can follow these steps to enforce its validity:</p> <blockquote> <ol> <li><p>extract the userID and the key from the cookie presented</p></li> <li><p>Query the database with userID to see if</p> <pre><code> --&gt; a) user has requested to be remembered --&gt; b) check the time to see if they cookie is still valid --&gt; c) extract the token and salt from database table record </code></pre></li> <li><p>Run the token and salt through an encrypt() function call and match against the presented key.</p></li> <li><p>If everything checks out, create a new session and log the user in.</p></li> </ol> </blockquote> <p>Now every time the user comes to your site they will be logged in, and <strong>in the event that their computer is compromised the attacker will not have access to their password</strong></p> <p>Side Note: You should always require your user to present a password when changing their password or email, this way should a user's cookie find its way into the wrong hands your attacker will be unable to steal the account.</p>
    singulars
    1. This table or related slice is empty.
    1. POthe most secure way of setting up a cookie for a "remember me" feature
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCharles Addis
    1. USCharles Addis
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POthe most secure way of setting up a cookie for a "remember me" feature
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI like this...I noticed in your login script, all it did was update the token and all the details..it didn't actually check to see if the cookie existed before...I'm a little confused about that. Shouldnt this be done after it checks to see if the token and all that are correct between table and cookie, THEN update the details to new numbers with new time? Or what does that do that I'm not seeing?
      singulars
      1. PO
      1. USkdjernigan
    2. COYeah, sorry I know its a little confusing I was just trying to get the idea across. I said somewhere in there (i think!) that some code needs to be added to check if the cookie is set.This is all pseudo-code so that it could be ported into whatever language. Most of the functions were based off of functions in the PHP library, however.
      singulars
      1. PO
      1. USCharles Addis
    3. COAll pages on your website should check for the cookie, not just a single page (any page that could be an entry point, at least). If the cookie is set, take the user ID and grab the info from the DB. If its not, this code gets run when the user logs in. Lets say you want the 'remember me' function to remember people for two weeks [ this is specified in your code by taking the current time in seconds and subtracting from it the time that the cookie was set. Once you've done the subtraction you can compare the result to the time limit.]
      singulars
      1. PO
      1. USCharles Addis
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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