Chained select boxes

[d0nny]

I know this has been asked previously and I have read all of the post (and more!) on this subject.
Suffice to say that I have a form that actually works now.

What I am trying to do is have two select boxes (dropdown) on my form and the 2nd select box has items in it that are dependent on the 1st select box. So, if I choose an item in the 1st select box the items listed in the 2nd select box are 'related' to the 1st select box. And all of these values are derived from a mySQL DB. The 1st select box obtains records from table A and the 2nd select box obtains records from table B (table A and table B are related by an ID).

The fact is I have this working. The form works great and the select boxes operate as I expect.
The only issue I have is that one of my values from the form is not being saved in the database, and I cannot work out why!?

So here's my form code:

<tr>
<td>Request Area</td>
<td>
<select name="request" id="request" onchange="AjaxFunction(this.value);">
<option value=''>Select One</option>
<?php
$q=mysql_query("select * from req_name ORDER BY request");
while($n=mysql_fetch_array($q)){
echo "<option value=$n[id]>$n[request]</option>";
}
?>
</select>
</td>

<td>Sub-request Name</td>
<td>
<div id="subreq">...</div>
</td>
</tr>
<tr>
<td valign="top">Description</td>
<td colspan="3"><textarea cols="50" rows="5" name="description"></textarea></td>
</tr>

And here's the Javascript that the 1st select box calls:

function AjaxFunction(id) {
	var httpxml;
	try {
		// Firefox, Opera 8.0+, Safari
		httpxml=new XMLHttpRequest();
	}
	catch (e) {
		// Internet Explorer
		try {
			httpxml=new ActiveXObject("Msxml2.XMLHTTP");
		}
		catch (e) {
			try {
				httpxml=new ActiveXObject("Microsoft.XMLHTTP");
			}
			catch (e) {
				alert("Your browser does not support AJAX!");
				return false;
			}
		}
	}
	function stateck() {
		if(httpxml.readyState==4) {
			document.getElementById("subreq").innerHTML=httpxml.responseText;
		}
	}
	var url="req.php";
	url=url+"?id="+id;
	url=url+"&sid="+Math.random();
	httpxml.onreadystatechange=stateck;
	httpxml.open("GET",url,true);
	httpxml.send(null);
}

And then there's the PHP file the JS calls

<?
$id=$_GET['id'];
include '/home/piglcouk/public_html/includes/dbc.php';
$q=mysql_query("select * from sub_reqs_name where req_id='$id'");
echo mysql_error();
echo "<select name='SubReq'>";
while($nt=mysql_fetch_array($q)) {
	echo "<option value='$nt[id]'>$nt[sub_req]</option>";
}
echo "</select>";
?>

All OK so far, and as I say the form works a treat.

Here's my INSERT query for the data:

INSERT INTO requests (user_id,date,description,request,sub_req) VALUES ($ur,NOW(),'$_POST[description]','$_POST[request]','$_POST[SubReq]')

$ur is a SESSION variable I am using for the UserID.

Some of the other table fields are defaulted. Everything saves nicely except for the SubReq.
Now my thinking is that this is empty on the form and I have no real way fo checking it as when I look at the source of the page after the 2nd select box has loaded, it is still blank. So although the 2nd select box is populated after the 1st box is changed, I think the form still thinks there's nothing in it, although that might be silly as there is obviously something in the 2nd select field!

As I say, I'm stumped.
Any ideas/help/advice/suggestions?
Do you need any more info?

Thanks ...

 

[d0nny]

Maybe a false alarm here as I think I've resolved the issue.

I did some further testing by 'echo'ing the variables both on the result page and also in emails I'm getting the form to submit. After playing with the variables for a while it managed to rectify itself.

Not entirely sure where the problem existed, but I've tested the insert now and it does work by saving all the data to the DB.

 

[jpadie]

please make sure that you escape your values before using them in database queries.

 

[d0nny]

Can you explain jpadie?

 

[jpadie]

this is bad practice and exposes you to sql injection attack vectors as well as failed queries

INSERT INTO requests (user_id,date,description,request,sub_req) VALUES ($ur,NOW(),'$_POST[description]','$_POST[request]','$_POST[SubReq]')

this is one of the right ways to do it

<?php

$sql = "INSERT INTO requests 
	(user_id,date,description,request,sub_req) 
	VALUES 
	($ur,NOW(),'%s','%s','%s')";

$params = array($_POST['description'], $_POST['request'],$_POST['SubReq']);
//escapee the params
$params = array_map('mysql_real_escape_string', $params);
mysql_query(vsprintf($sql, $params)) or die (mysql_error());
?>

 

[d0nny]

Oh boy, that certainly changes a lot of the code I've already written!
But I suppose its better to be careful, certainly after 2 recent attacks on some of our systems. I believe we had a 'reverse PHP shell' put on one of our services, which was put there through an upload form (which was also not secure!).

Slightly off topic, can anyone explain what a reverse PHP shell is?

 

[jpadie]

a reverse shell is simply a file that sits on your server through which shell commands can be passed for execution on the remote server. these can be used for perfectly legitimate purposes and are mainstream. See PHPTerm for example (phpterm.sourceforge.net)

other alternatives to the code i posted is:

1. to use mysql_real_escape_string() directly on the variables in the query.
2. put this code in as the first line of your scripts:

$_post = array_map('mysql_real_escape_string', $_POST);

and then reference $_post rather than $_POST in your queries. you can do the same for $_GET or other variables of course. you could also auto_prepend this script in your php.ini file.

3. use prepared statements in mysql
4. switch to using PDO (with the mysql driver) and use prepared statements.

IMO the order from best to worst solutions is 4, 3, 1, 2

see here for more on sql injection:

http://phpsec.org/projects/guide/3.html

 

[d0nny]

Thanks a lot of this.

My issue is I'm not a real PHP developer (and never pretend to be one!), but I like to dabble for my pet projects!

The bigger issue for me is that we have some legacy code/applications where I work which constantly get hacked - we had 2 hacks yesterday!

So understanding these issues helps me as the Tech Director!

 

[jpadie]

we're happy to help here.

one thing you could do, and this is really ugly, is run a script to change all references to mysql_query to _mysql_query

and then create a function called

function _mysql_query($query){

}

i can probably supply you with a function that would automatically sort out your variables and then execute and return the query.

this should fix 99% of the problems with sql injection without burping any of your applications. assuming you do not use highly complex queries. I use this technique for my plugin PDO For WordPress that converts WP to use SQLite and other databases.