Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5

    How do I create a DO WHILE php loop using mysqli record ?


    Hi,

    I want to create a random token, check it against existing tokens in the database, create a replacement if that token already exists and is less than 1 hour old, in order to be sure that the token is unique before inserting it into the database.

    Presumably I should do this with a WHILE / DO WHILE LOOP.

    I could just about manage it with old mysql but now I'm starting to use mysqli and will probably waste ages trying without success to get it right and will inevitably come here to ask advice - so it seemed sensible to ask if someone would be so kind as to sort me out.

    As an extra thought, should I convert universal_token.createddatetime and $MySQLDateTimeStringMinus_1_Hour to integers?

    I'm taking the procedural approach as Object Oriented is still beyond my thinking.

    Here's my code ;

    PHP Code:
    //create $newtoken
    $newtoken dechex(mt_rand(02147483647)) . dechex(mt_rand(02147483647));
        
    //check that the same $newtoken hasn't already been created
    $existingtokensql = ("SELECT universal_token.token FROM universal_token WHERE universal_token.token = '".$newtoken."' && universal_token.createddatetime > '".$MySQLDateTimeStringMinus_1_Hour."' ");
        
    $existingtokenresult mysqli_query($con$existingtokensql);
        
    // create a different $newtoken if $newtoken already exists
    $newtoken dechex(mt_rand(02147483647)) . dechex(mt_rand(02147483647)); 
            
    // Free result set
    mysqli_free_result($existingtokenresult);

    //insert $newtoken into database
    $tokeninsertsql = ("INSERT INTO universal_token (token, createddatetime) VALUES ('".$newtoken."', '".$MySQLDateTimeStringNOW."') ");
            
    // effect the insert
    $tokeninsertresult mysqli_query($con,$tokeninsertsql); 
    Hope someone is happy to help. Please adhere as closely to my existing code as possible. Thank you.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,571
    Rep Power
    595
    PHP Code:
    do {
       
    //create $newtoken
       
    $newtoken dechex(mt_rand(02147483647)) . dechex(mt_rand(02147483647));
       
    //check that the same $newtoken hasn't already been created
       
    $existingtokensql = ("SELECT universal_token.token FROM universal_token  WHERE universal_token.token = '".$newtoken."' && universal_token.createddatetime > '".$MySQLDateTimeStringMinus_1_Hour."' ");
        
       
    $existingtokenresult mysqli_query($con$existingtokensql);
    } while (
    mysqli_num_rows($existingtokenresult)>0); 

    Comments on this post

    • foreverforever agrees : Thank you - not many points but it's all I can offer !
    Last edited by gw1500se; July 4th, 2013 at 06:56 AM.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    the approach is kinda weird. Instead of generating weak random numbers and writing complicated code to deal with possible collisions, how about you generate strong random numbers and simply eliminate the risk of collisions?

    I'd also be interested in what you need those numbers for. Do they have to be unguessable? Then mt_rand() would be completely unsuitable, anyway.

    In any case, use something like bin2hex(openssl_random_pseudo_bytes(16)) to generate 16 strong pseudo random bytes. The chances of getting a collision there are effectively zero, so no need for additional code. If you're super paranoid, you may put a UNIQUE constraint on the column.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4124
    mysql has a function for generating universally unique tokens, it's called UUID()

    sql Code:
     
    --untested
    SET @unid = UUID();
     
    INSERT
    INTO
    	`universal_token` (
    		 `token`
    		,`createddatetime`
    	) VALUES (
    		 @unid
    		,NOW()
    	)
    ;
     
    SELECT @unid AS `token`;

    Comments on this post

    • foreverforever agrees : Thank you - all the point I can give.
    Last edited by Northie; July 4th, 2013 at 07:28 AM.
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5
    Thank you gw1500se - works like a dream !

    For some reason I didn't think that there was such a thing as mysqli_num_rows.

    I thought that I had to somehow contrive something using the result.

    Glad I know better know as this is going to make life so much easier than I thought.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5
    Originally Posted by Jacques1
    Hi,

    the approach is kinda weird. Instead of generating weak random numbers and writing complicated code to deal with possible collisions, how about you generate strong random numbers and simply eliminate the risk of collisions?

    I'd also be interested in what you need those numbers for. Do they have to be unguessable? Then mt_rand() would be completely unsuitable, anyway.

    In any case, use something like bin2hex(openssl_random_pseudo_bytes(16)) to generate 16 strong pseudo random bytes. The chances of getting a collision there are effectively zero, so no need for additional code. If you're super paranoid, you may put a UNIQUE constraint on the column.
    Hi Jacques1,

    I'm not overly worried about the strength of the token security-wise. All I want it for is to make it a little tougher to submit data with a hack - every form will have a valid token, so any data submitted without a valid token will be rejected.

    Is my approach seriously wrong or just a little unusual ?

    Always happy to receive advice.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5
    Originally Posted by Northie
    mysql has a function for generating universally unique tokens, it's called UUID()
    Thank you Northie. I'll look into UUID.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,571
    Rep Power
    595
    One of the reasons I visit this forum is to learn myself. Northie's post was something of which I was totally unaware, until now. Thanks.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by foreverforever
    I'm not overly worried about the strength of the token security-wise. All I want it for is to make it a little tougher to submit data with a hack - every form will have a valid token, so any data submitted without a valid token will be rejected.
    So you create a token every time you render a form, and when somebody does a request, you check if the submitted token is in your list of valid tokens?

    What's the point of this? How does this prevent "hacks"? From what I can tell in your case, a valid token means absolutely nothing -- except that the user has at some time visited your site and got a token from you.

    I know it's not very popular around here to ask for the reason for doing things. But I think that's what's actually important.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5
    Token is to be checked slightly more thoroughly than you suggest, but essentially you are correct. If the user presents a token that wasn't assigned specifically to them with the foregoing 60 minutes, for use in conjunction with that form, then the form submission is not accepted.

    I was thinking that it would reduce the chance of people successfully modifying my database by generating fake GETs/POSTs - not that I've any idea how to generate a fake GET/POST. Would it not do so ?

    Originally Posted by Jacques1
    So you create a token every time you render a form, and when somebody does a request, you check if the submitted token is in your list of valid tokens?

    What's the point of this? How does this prevent "hacks"? From what I can tell in your case, a valid token means absolutely nothing -- except that the user has at some time visited your site and got a token from you.

    I know it's not very popular around here to ask for the reason for doing things. But I think that's what's actually important.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,016
    Rep Power
    376
    typically what you do is create a random (unique) no, store it as a session, echo it out on the form hidden input. When user posts, you check whether this hidden input is same as that of your session. no need for database, timeouts etc etc

    Comments on this post

    • Jacques1 disagrees : How does that protect him from "people modifying the database"? What you describe is CSRF protection. He is talking about preventing SQL injections.
  22. #12
  23. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    This doesn't protect your database at all. In fact, you're working at the completely wrong end.

    To secure your database, you need to make sure that the user input doesn't affect the query. The most effective way of doing that is to use prepared statement. This means you first send a constant query template with placeholders to the database server. The server parses this template and then waits for you to fill the placeholders with data. When you got the user input, you pass it to the placeholders and have the database system execute the whole thing. Since there's a strict separation between the actual SQL query and the data being sent, the user input (data) cannot change the query. There's no risk of an attacker injecting SQL commands.

    If for some strange reason you cannot use prepared statements, you need to escape the user input by hand. However, this has proven to be very error-prone, so don't do it unless you have to.

    See The 6 worst sins of security for further info.



    Originally Posted by foreverforever
    Token is to be checked slightly more thoroughly than you suggest, but essentially you are correct. If the user presents a token that wasn't assigned specifically to them with the foregoing 60 minutes, for use in conjunction with that form, then the form submission is not accepted.
    Are you sure the token is bound to a particular form and user? In your query above, you don't store any such info. You just have a list of tokens.

    But that doesn't really matter, anyway. The token is useless. It doesn't tell you anything. If you think that it verifies whether the user has actually sent the request with this form, you're wrong. Nothing prevents me from simply opening the form, taking the token and creating a valid request with arbitrary data.



    Originally Posted by foreverforever
    I was thinking that it would reduce the chance of people successfully modifying my database by generating fake GETs/POSTs - not that I've any idea how to generate a fake GET/POST. Would it not do so ?
    There are no "fake" requests. You should forget about this notion.

    A request is a request. It's impossible to find out if it was initiated by clicking on the "submit" button of your form, or if somebody has typed in the bytes by hand. And it simply doesn't matter. One the internet, anybody can send any data to any server. It's your job to make sure your server can handle the incoming data.

    So web security is not about somehow restricting the input. It's about making sure the input causes no damage.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5
    Some webhosts (including mine) don't carry session variables from one server to another so I'm using my token idea rather than session variables. Seems to me to be the same principle but storing in the database rather than as a session variable.

    Originally Posted by paulh1983
    typically what you do is create a random (unique) no, store it as a session, echo it out on the form hidden input. When user posts, you check whether this hidden input is same as that of your session. no need for database, timeouts etc etc
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5
    Originally Posted by Jacques1
    To secure your database, you need to make sure that the user input doesn't affect the query. The most effective way of doing that is to use prepared statement.
    Moving on to prepared statements is my next target. They may be more secure but not really necessary for me to learn for purposes of checking my token system, but thank you for the advice, and I will be adopting them. I've done a couple of test ones already but I found them to be a bit tricky to get my head around so one step at a time. I will embrace them for the project build though.

    If for some strange reason you cannot use prepared statements, you need to escape the user input by hand. However, this has proven to be very error-prone, so don't do it unless you have to.
    I understand about sql injection and escaping/unescaping, and will be using it wherever necessary. Again, it's a case of developing my knowledge step-by-step. I was unaware that mysqli_real_escape_string was error-prone.

    See The 6 worst sins of security for further info.
    Thank you, I will read and hopefully some of it will sink in to my thick skull

    Are you sure the token is bound to a particular form and user? In your query above, you don't store any such info. You just have a list of tokens.
    For simplicity, I did not include details of user, form, & whether the token has been used. I will also be using analytics to look for users/ip addresses that use tokens very rapidly &/or in great quantity.

    The token is useless. It doesn't tell you anything. If you think that it verifies whether the user has actually sent the request with this form, you're wrong. Nothing prevents me from simply opening the form, taking the token and creating a valid request with arbitrary data.
    It's ok if they do that. All data will be checked to ensure that it adheres to acceptable values. More specific values than strings, integer, date, etc as performed by prepared statements.

    So web security is not about somehow restricting the input. It's about making sure the input causes no damage.
    I understand this and I think that my token helps. It is not going to be the limit of my security efforts but I think it offers a degree more security than not having it - maybe only a fraction of a degree, but certainly not less.
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    57
    Rep Power
    5
    I guess that as I have the attention of such knowledgeable people, I should take the opportunity to ask if CSRF enables malicious people to steal/emulate the content of cookies from users' computers ?

    Because of the previously-mentioned problem about session variables not being carried perpetuated between servers, I have gone for cookies to identify each user visit. NB visit not user id.

    Is this approach going to cause me big bad problems?

    If so, what is the best solution please?

    Happy to rewrite this as a different thread if mods prefer.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo