[SOLVED] Best Practice ?

[Clinton]

I've got a table that lists office locations by city and state. Sometimes there are several offices in one city. Right now I am using the following code to pull up the citys

 

City: <select name="city" onchange='this.form.submit()'>
				<?php

    				$sql = "SELECT city FROM thelist WHERE state = '$state' ORDER BY city";

				$rs = mysql_query($sql);

				while($row = mysql_fetch_array($rs))
				{
    				extract($row);
  					echo "<option value='$city'>$city</option>";
				}

     				?>
                    </select>

 

But if there is three offices in one city then it will echo out that same city 3 times in the drop down list. What's the best way to correct this so it only shows once?

[hobeau]

<?php
$sql = "SELECT DISTINCT city FROM thelist WHERE state = '$state' ORDER BY city";
?>

[Clinton]

Thanks beau. That worked perfectly. I didn't know that existed. Time to look it up.

[hobeau]

One more thing, you really should use mysql_real_escape_string($state). That is very important to prevent sql injection.

[Clinton]

Ok, how can code be injected if it's just querying?

[KevinM1]

Ok, how can code be injected if it's just querying?

 

Where does $state come from?

[Clinton]

db connect include then an sql statement...

 

<?php

$sql = "SELECT * FROM $tbl_name WHERE (dtype = '$dtype' OR dtypewc = '$dtype') AND (dmajorp = '$smajor' OR dmajorc1 = '$smajor' OR dmajorc2 = '$smajor' OR dmajorc3 = '$smajor') $state_where $city_where ORDER BY jpted LIMIT $start, $limit";

$result = mysql_query($sql);

?>

[KevinM1]

db connect include then an sql statement...

 

<?php

$sql = "SELECT * FROM $tbl_name WHERE (dtype = '$dtype' OR dtypewc = '$dtype') AND (dmajorp = '$smajor' OR dmajorc1 = '$smajor' OR dmajorc2 = '$smajor' OR dmajorc3 = '$smajor') $state_where $city_where ORDER BY jpted LIMIT $start, $limit";

$result = mysql_query($sql);

?>

 

That doesn't really tell me anything.  Let me rephrase: is $state's value derived by the script, or is it supplied by the user?

[hobeau]

hunna03,

 

I've had to fix many a site (not built by me) that were just querying and didn't escape their sql statements. It doesn't matter what you are trying to do, if it gets injected into your sql statement it can easily be turned into an insert, update, or delete statement (I'm assuming that $state is coming from the client).