PHP variable

Discussion in 'Web Design and Development' started by Macman1993, Jun 9, 2010.

  1. Macman1993 macrumors 6502

    Macman1993

    Joined:
    Nov 23, 2007
    #1
    So I decided a good way to really test my one day old MYSQL skills would be to write a random fact generator that uses a mysql database that stores two columns one is the id of the fact and the other is the fact. I use the php rand() function to generate a random id (between 1 and the highest id). My generated number is working fine and I store it in the variable $rand but the problem comes when trying to get the row with the id of $rand. I use the following query to get the row with the id

    PHP:
    $query=mysql_query("SELECT * FROM facts WHERE id='2'") or die(mysql_error());
    then a few more lines to get the fact. This works perfectly for a hard coded number but when I try to use the value of $rand it just doesn't work. How do I use a php int in a mysql query? I want something like this

    PHP:
    $query=mysql_query("SELECT * FROM facts WHERE id='$rand'") or die(mysql_error());
    I'm sure this is easy to do but I just don't know off the top of my head. I tried casting it with int($rand) just got the sake of it but also no luck. Anyone have the solution for using the value of $rand for my id?
     
  2. pfealey macrumors member

    Joined:
    Aug 27, 2009
    Location:
    Weston-super-Mare, UK
    #2
    PHP:
    $sql "SELECT * FROM facts WHERE id = " $rand;
    PHP:
    $query=mysql_query($sql) or die(mysql_error());  
    You could try the above, i'm not near my LAMP box to test this though
     
  3. m7a7d macrumors newbie

    Joined:
    Feb 27, 2010
    #3
    I'm definitely not a PHP expert (not even near), but what I've been taught so far is to build the query as a string, then use the mysqli_query() function.

    PHP:
    $query "SELECT * FROM facts WHERE id = '$rand'";
    $result mysqli_query($query);
    But honestly there is no difference, now that I think about it :eek:
     
  4. Darth.Titan macrumors 68030

    Darth.Titan

    Joined:
    Oct 31, 2007
    Location:
    Austin, TX
    #4
    Is rand() possibly returning a value that's outside the number of entries in your facts table?
    When I execute rand() with no arguments, I usually get large numbers like 752241300. Make sure to use the limiting arguments in rand() to prevent this.
    If you have 100 entries in your fact table use
    Code:
    $rand = rand(1,100);
    If you're already doing this, try what m7a7d suggested and load the query into a string first. That way you can echo it out to see what is trying to run. That usually helps me spot problems in queries.
     
  5. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #5
    PHP:
    $range_result mysql_query" SELECT MAX(`id`) AS max_id ,  MIN(`id`) AS min_id FROM `facts` ");

     
    $range_row =  mysql_fetch_object$range_result ); 
     
    $random mt_rand(  $range_row->min_id $range_row->max_id );
     
    $result =  mysql_query" SELECT * FROM `facts` WHERE `id` >= $random LIMIT 0,1  "); 
    This method checks min/max record numbers, picks a random within that range and account for missing id's and ensures only one row is returned. Using PHP is better than MySQL's internal RAND() because MySQL creates a temporary table with all the result rows and assigns each one of them a random sorting index. The results are then sorted and returned, which can be slow when large numbers of records are involved. I know this is a small project, so you could use MySQL all the way, honestly, but the disclaimer is worth noting for those who are considering methods and using large data sets. FYI.

    -jim
     
  6. Cromulent macrumors 603

    Cromulent

    Joined:
    Oct 2, 2006
    Location:
    The Land of Hope and Glory
    #6
    That is extremely bad practice. If you have dodgy data stored in your variables you could potentially destroy your database using that technique. I wish I knew who taught things like this.

    Use parameterised queries instead.
     
  7. m7a7d macrumors newbie

    Joined:
    Feb 27, 2010
    #7
    Could you clarify?

    You mean, building a string in $query and using it as the argument to mysqli_query() is bad practice?

    I'm a newb and I'm having trouble understanding the difference between

    $query = 'SOME STRING';
    $result = mysqli_query('$query');

    and

    $result = mysqli_query('SOME STRING');
     
  8. rowsdower macrumors 6502

    Joined:
    Jun 2, 2009
    #8
    It is bad practice to insert variables directly into the queries. See this article (not MySQL specific, but the concept is the same.

    There is no difference between these two. The problem comes in when you add a variable like this:

    Code:
    $result = mysqli_query("select firstname,lastname from users where username='$username'");
    Depending on what is in $username, an attacker could delete everything in your database, even for a query like that one that looks like a simple select.
     
  9. racketeer71 macrumors regular

    Joined:
    Jan 15, 2010
    #9
    Say you have the following values in the id column:

    1
    2
    4
    5
    8

    (The missing ones, are rows that were deleted when it was discovered that they weren't true).

    You'll get a MIN=1 and MAX=8, so I believe you could get ie. 3, 6 or 7 even though they have been deleted.

    The result being, that your last query might return 0 rows.
     
  10. racketeer71 macrumors regular

    Joined:
    Jan 15, 2010
    #10
    There's no difference, they're both equally bad.

    Imagine you have a login form, where the user submit $username and $password.

    You then create something like this:

    $query = "SELECT ID FROM Users WHERE Username = '" . $username . "' AND Password = '" . $password. "'

    So, if I login with $username = "foo" and $password = "bar", the query being executed would look like this:

    SELECT ID FROM Users WHERE Username = 'foo' AND Password = 'bar'

    So that's OK. But imagine I login with:
    username = "foo"
    password = ' OR '1' = '1

    Then the query being executed looks like this:

    SELECT ID FROM Users WHERE Username = 'foo' AND Password = '' OR '1' = '1'

    - and then I'll get access without knowing username nor password, since '1' = '1' always.

    Look up SQL injection attack in your favorite search engine.

    The problem kan be mitigated with http://php.net/manual/en/function.mysql-real-escape-string.php
     
  11. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #11
    :eek:

    Respectfully, that is an outrageous and completely false statement in the context in which you presented it. The article focuses on the very true premise that "the risk of the user supplying input that may cause your query to do something that you had not expected". The article actually includes a link about SQL injection prevention which is the solution to said concerns, mirroring what racketeer71 said in their response and I echo now.

    It is irresponsible to twist a valid concern about corrupting a database into a broad, general all-encompassing statement that inserting variables is bad, without any context as to why. The correct statement might be, "It is a security risk to insert user populated variables into a database query without following simple anti-SQL injection techniques before embedding those variables."

    Come on, now!

    :p

    -jim
     
  12. harpster macrumors regular

    Joined:
    Jan 26, 2010
    #12
    Your code should work assuming you have an id in the database equal to the value of $rand. Just test your variable with a fixed number first then use your computed random number.

    Also make sure for your test environment that in your php.ini file you have display_errors = On so you can see why things might have failed.

    PHP:

    $rand 
    2//test with this then replace with your rand function.

    echo "random number = " $rand "<br>";
     
    $query=mysql_query("SELECT * FROM fact WHERE id='$rand'") or die(mysql_error());  

    $row=mysql_fetch_array($query);
    echo 
    $row[1];

     
  13. rowsdower macrumors 6502

    Joined:
    Jun 2, 2009
    #13
    I don't know about completely false. Now that I read it again, it certainly needs some context and is more general than I intended it to be. I was intending to refer specifically to inserting parameters into a query, but that isn't really clear from my statement.

    There are certainly safe ways to concatenate strings or use string substitutions with variables to construct a query (including the code you presented, since the variable came from a trusted source). However, my opinion is that it is always a good practice to construct the query using '?' placeholders and use parameters to fill in the values. That way you always force a distinction in your mind between the variables you are using for the structure of the query and the variables that could possibly have come from the user, and you don't accidentally end up placing data from the user directly into a query somewhere down the line (e.g. if the OP some day wants the user to be able to bookmark his favorite facts or something and ends up using an argument to do it).

    I agree that my original statement was not entirely true as written, and that your rewording of my statement is entirely true. However, I think my statement is more likely to cause a "should I be doing this" moment, which I think is a good thing.

    Anyway, apologies if I caused any confusion.
     
  14. m7a7d macrumors newbie

    Joined:
    Feb 27, 2010
    #14
    As an application programmer new to web programming, I appreciate this discussion. Thanks for everyones input.

    Thanks racketeer71, you explained it perfectly :D
     
  15. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #15
    Your intentions - good.
    Your execution of said intentions - red alert!

    The object is to educate users as to vulnerabilities, not scare them to death! Relax, I'm not angry or whatever, and I was careful to quote only the portion of your statement I had issues with.

    -jim
     
  16. SrWebDeveloper macrumors 68000

    SrWebDeveloper

    Joined:
    Dec 7, 2007
    Location:
    Alexandria, VA, USA
    #16
    Incorrect.

    Look again at my example code which includes this:

    HTML:
    WHERE `id` >=
    The "greater than or equal to" accounts for any missing ID's.

    :p

    I posted that example for good reason.

    -jim
     
  17. harpster macrumors regular

    Joined:
    Jan 26, 2010
  18. m7a7d macrumors newbie

    Joined:
    Feb 27, 2010
    #18
    I've been thinking about this recently..

    about my original suggestion to put the query in a variable. Is this considered bad practice no matter how it's used?

    say I have a function,

    PHP:
        function get_all_subjects() {
            global 
    $connection;
            
    $query "SELECT *
                    FROM subjects
                    ORDER BY position ASC"
    ;
            
    $subject_set mysqli_query($connection$query);
            
    confirm_query($subject_set); // another user-defined function
            
    return $subject_set;
        }
    that I call in almost all my files. Is this bad practice? I understand you should be validating user input data before sending it off to query, but I don't see what's wrong with hard coding a string to use for a query.
     
  19. angelwatt Moderator emeritus

    angelwatt

    Joined:
    Aug 16, 2005
    Location:
    USA
    #19
    No, hard-coded is fine security-wise. It may become a nuisance to update down the road since it would require the developer to poor through all the code looking for this query, but that's about it.
     

Share This Page