Exclude first HTML filter option in SQL query

Hi there,

I think my current problem is simple but at the moment I cant figure out how to solve it.
I imagine that an option could be IF statement but I think there must to be something more simple.


I have a query that takes a keyword, a category and a region from the html search form

What I need to do is that the query will run only if category AND region value is a number.

So, the query should omit the value for this

       
 <option value="" selected>Any sectors</option>

Open in new window

and this:      
<option value="" selected>Any region</option>   

Open in new window



So, if the user search for a keyword with the filters by default (any sector) (any region)
The query will omit the value for those filters.

This is an extract of the code
	         <!-- ADDED A DEFAULT SELECTION -->
	         <option value="" selected>Any sectors</option>
	         <option value="1">Aerospace &amp; Transportation</option>
	         <option value="2">Automotive</option>
	         <option value="3">Banking, Finance &amp; Real Estate</option>
	         <option value="4">Basic Materials</option>
	         <option value="5">Business &amp; Government</option>
	         <option value="7">Construction &amp; Heavy Industry</option>
	         <option value="6">Consumer Goods &amp; Retail</option>
	         <option value="8">Energy &amp; Utilities</option>
	         <option value="9">Food &amp; Drink</option>
	         <option value="10">Information Technology</option>
	         <option value="11">Media &amp; Leisure</option>
	         <option value="12">Pharma &amp; Healthcare</option>
	         <option value="13">Telecomunication</option>
           </select>
           <label for="regions">Select Region:</label>
           <select name="regions" id="regions">

             <!-- ADDED A DEFAULT SELECTION -->
             <option value="" selected>Any region</option>            
             <option value="1">Africa</option>
             <option value="3">Asia</option>
             <option value="24">Australia and New Zealand</option>
             <option value="12">Central America</option>
             <option value="20"> Central and Eastern Europe</option>
             <option value="15">Central Asia</option>
             <option value="16">East Asia</option>
             <option value="4">Europe</option>
             <option value="19">Middle East</option>
             <option value="8">North Africa</option>
             <option value="13">North America</option>
             <option value="14">South America</option>
             <option value="17">South Asia</option>
             <option value="18">South East Asia</option>
             <option value="23">Western Europe</option>
             <option value="0">Global </option>
           </select>
          </p>
          <p>&nbsp;</p>
          <p>
            <input type="radio" name="radio" id="boton1" value="1" checked="checked" />
            <label for="boton1">market reports</label>
            <input type="radio" name="radio" id="boton2" value="2" />
            <label for="boton2">swots</label>
            <input type="radio" name="radio" id="boton3" value="3" />
            <label for="boton3">all content</label>
          </p>

          <!-- ADDED A SUBMIT CONTROL -->
          <p>
            <input type="submit" />
          </p>

        </form>
</div>
</body>


<?php

error_reporting(E_ALL);

$db_host = "****";
$db_user = "***"; 
$db_word = "***"; 
$db_name =  "***";
$db_port = "***";


$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name, $db_port);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}



// RUN THE ACTION SCRIPT IF THE REQUEST HAS BEEN SUBMITTED
if (!empty($_GET))
{
    // SECTOR / REGION INDEX REPORT ELEMENTS
    $id_cat  = $_GET['categories'];
	
    $id_reg  = $_GET['regions'];

    // SEARCH KEYWORD
    $keyword = $_GET['keyword'];

    // TABLES TO BE SEARCHED
    switch ($_GET['radio'])       
    {
        case 1:
            $sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, sector_index_reports AS T2,  country_index_reports AS T3
                     WHERE T2.id_cat = '$id_cat' AND  (T1.id=T2.id_obiekt) AND  (T3.id_cat= '$id_reg') AND (T1.id=T3.id_obiekt) AND name LIKE '%$keyword%'" ;
            break;	

Open in new window

Klaus AndersenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GaryCommented:
if (!empty($_GET))
{
    // SECTOR / REGION INDEX REPORT ELEMENTS
    $id_cat  = $_GET['categories']!='Any sectors'?$_GET['categories']:'%';
	
    $id_reg  = $_GET[regions]!='Any region'?$_GET[regions]:'%';

Open in new window

0
Klaus AndersenAuthor Commented:
Thanks @GaryC123. Unfortunately didn´t work, am I doing something wrong?

This is what I have

HTML
 <!-- ADDED A DEFAULT SELECTION -->
	         <option value="" selected>Any sector</option>
	         <option value="1">Aerospace &amp; Transportation</option>
	         <option value="2">Automotive</option>
	         <option value="3">Banking, Finance &amp; Real Estate</option>
	         <option value="4">Basic Materials</option>
	         <option value="5">Business &amp; Government</option>
	         <option value="7">Construction &amp; Heavy Industry</option>
	         <option value="6">Consumer Goods &amp; Retail</option>
	         <option value="8">Energy &amp; Utilities</option>
	         <option value="9">Food &amp; Drink</option>
	         <option value="10">Information Technology</option>
	         <option value="11">Media &amp; Leisure</option>
	         <option value="12">Pharma &amp; Healthcare</option>
	         <option value="13">Telecomunication</option>
           </select>
           <label for="regions">Select Region:</label>
           <select name="regions" id="regions">

             <!-- ADDED A DEFAULT SELECTION -->
             <option value="" selected>Any region</option>
             <option value="1">Africa</option>
             <option value="3">Asia</option>
             <option value="24">Australia and New Zealand</option>
             <option value="12">Central America</option>
             <option value="20"> Central and Eastern Europe</option>
             <option value="15">Central Asia</option>
             <option value="16">East Asia</option>
             <option value="4">Europe</option>
             <option value="19">Middle East</option>
             <option value="8">North Africa</option>
             <option value="13">North America</option>
             <option value="14">South America</option>
             <option value="17">South Asia</option>
             <option value="18">South East Asia</option>
             <option value="23">Western Europe</option>
           </select>

Open in new window


PHP
if (!empty($_GET))
{
    // SECTOR / REGION INDEX REPORT ELEMENTS
// SECTOR / REGION INDEX REPORT ELEMENTS
     $id_cat  = $_GET['categories']!='Any sector'?$_GET['categories']:'%';
	
    $id_reg  = $_GET['regions']!='Any region'?$_GET['regions']:'%';

Open in new window

0
Ray PaseurCommented:
Yes, it's an if() statement or two.  The example from GaryC123 uses the ternary operator, which is a form of if() statement.

A wise programmer would have a filter function for each external variable to ensure that the input from external variables is one of the acceptable values for that particular variable.  You might write your own function, or add it as a class method.  You could check to see if the value was a number, but that would not tell you that it was a number from your form.  As with most things in security and logic, you make tradeoffs between convenience and safety.

This might be useful to shape your thinking about external variables.
http://php.net/manual/en/language.variables.external.php

And this is required reading, every single link.
http://php.net/manual/en/security.php

From this and your other EE questions I really think you would get a lot of value out of this book:
http://www.amazon.com/PHP-MySQL-Web-Development-Edition/dp/0321833899
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Ray PaseurCommented:
Sigh.  You can use var_dump($_GET) to learn what is appearing in your script.

Each form input control produces two important data elements in the request.  The first is the name= attribute from the HTML input.  This appears in the request in the associative array index. The second is the value= attribute.  This appears in the associative array value.
0
GaryCommented:
I only posted the relevant snippet of your code that needed amending, you still need the rest of it if that really is all you have for your PHP code.
0
Klaus AndersenAuthor Commented:
@Ray, I´m approaching to the general solution of my search engine. Has been a bit pain and
lot of time around the topic. As you know I´m  new in PHP/Mysql programming.
Thanks for the permanent support!
0
Ray PaseurCommented:
Have a look at this: http://www.laprbass.com/RAY_temp_pj_.php

This is how you might filter one of the input controls.  You would want to have a filter that is custom-fitted to each of the input controls, depending on the expected values.  Once all of the filters have passed muster, you can use the values to create a query string.

<?php // RAY_temp_pj_.php
error_reporting(E_ALL);

// IF THE REQUEST HAS BEEN SUBMITTED
if (!empty($_GET))
{
    // IF THE SECTOR IS ACCEPTABLE (THIS IS OUR FILTER)
    if (($_GET['sector'] >= 1) && ($_GET['sector'] <= 13))
    {
        echo "WE WILL USE {$_GET['sector']} IN THE QUERY";
    }
    else
    {
        echo "SORRY, PLEASE PICK A VALID SECTOR";
    }
}
$htm = <<<EOD
<form>
<select name="sector">
<option value="0" selected>Any sector</option>
<option value="1">Aerospace &amp; Transportation</option>
<option value="2">Automotive</option>
<option value="3">Banking, Finance &amp; Real Estate</option>
<option value="4">Basic Materials</option>
<option value="5">Business &amp; Government</option>
<option value="7">Construction &amp; Heavy Industry</option>
<option value="6">Consumer Goods &amp; Retail</option>
<option value="8">Energy &amp; Utilities</option>
<option value="9">Food &amp; Drink</option>
<option value="10">Information Technology</option>
<option value="11">Media &amp; Leisure</option>
<option value="12">Pharma &amp; Healthcare</option>
<option value="13">Telecomunication</option>
</select>

<input type="submit" />
</form>
EOD;

echo $htm;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GaryCommented:
@_PJ_
Are you including the rest of your PHP code?
0
Klaus AndersenAuthor Commented:
@Ray, @GaryC123

This is how I solved it so far:

HTML
 <option value="any" selected>Any sector</option>
	         <option value="1">Aerospace &amp; Transportation</option>
	         <option value="2">Automotive</option>
	         <option value="3">Banking, Finance &amp; Real Estate</option>
	         <option value="4">Basic Materials</option>
	         <option value="5">Business &amp; Government</option>
	         <option value="7">Construction &amp; Heavy Industry</option>
	         <option value="6">Consumer Goods &amp; Retail</option>
	         <option value="8">Energy &amp; Utilities</option>
	         <option value="9">Food &amp; Drink</option>
	         <option value="10">Information Technology</option>
	         <option value="11">Media &amp; Leisure</option>
	         <option value="12">Pharma &amp; Healthcare</option>
	         <option value="13">Telecomunication</option>
           </select>
           <label for="regions">Select Region:</label>
           <select name="regions" id="regions">

             <!-- ADDED A DEFAULT SELECTION -->
             <option value="any" selected>Any region</option>            
             <option value="1">Africa</option>
             <option value="3">Asia</option>
             <option value="24">Australia and New Zealand</option>
             <option value="12">Central America</option>
             <option value="20"> Central and Eastern Europe</option>
             <option value="15">Central Asia</option>
             <option value="16">East Asia</option>
             <option value="4">Europe</option>
             <option value="19">Middle East</option>
             <option value="8">North Africa</option>
             <option value="13">North America</option>
             <option value="14">South America</option>
             <option value="17">South Asia</option>
             <option value="18">South East Asia</option>
             <option value="23">Western Europe</option>
             <option value="0">Global </option>
           </select>

Open in new window


PHP

		
if ($id_cat != 'any' AND $id_reg!='any') {
$sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, sector_index_reports AS T2, country_index_reports AS T3 WHERE T2.id_cat = '$id_cat' AND  (T1.id=T2.id_obiekt) AND  (T3.id_cat= '$id_reg') AND (T1.id=T3.id_obiekt) AND name LIKE '%$keyword%'" ; }


else if ($id_cat != 'any' AND $id_reg =='any') {
$sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, sector_index_reports AS T2  WHERE T2.id_cat = '$id_cat' AND  (T1.id=T2.id_obiekt) AND name LIKE '%$keyword%'" ; }		
			 

else if ($id_cat == 'any' AND $id_reg!='any') {
$sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports AS T1, country_index_reports AS T2 WHERE  
(T2.id_cat= '$id_reg') AND (T1.id=T2.id_obiekt) AND name LIKE '%$keyword%'" ; }	


else {  $sql = "SELECT id,name,organizer_id,no_pages,publication_date,price,currency  FROM reports WHERE  name LIKE '%$keyword%'" ; }

Open in new window

0
Ray PaseurCommented:
Yes, that is sure to get you hacked.  You should be following the guideline to Accept Only Known Good Values.  If you look for "any" and decide to use whatever is there so long as it's not "any" you're just begging for trouble.

A better strategy would be to make a list of acceptable values, perhaps in an array, and check the inputs to see if the values your script received are actually in the array.  If the input values are not in the array, your script is under attack, so you would never use those external values in a query.
0
Klaus AndersenAuthor Commented:
Today I will take a look how to solve this in a safest way. Actually I'm trying to give solution to  this kind of issues with the knowledge I have, and you know i still need to learn many things, but I keep on working!

Thanks Ray!
0
Ray PaseurCommented:
Here's an example of verifying external input by comparing it to known good (internal) values.

<?php // RAY_post_and_confirm.php
error_reporting(E_ALL);


// SHOW HOW TO USE A PRE-DEFINED ARRAY OF VALUES TO CREATE A FORM AND TO VALIDATE THE INPUT FROM THE FORM
// THE CLIENT IS ASKED TO PROVIDE THE INPUT, THEN CONFIRM THAT THE VALUES ARE OK


// THE ARRAY OF ACCEPTABLE VALUES
$good_values 
= array
( 'Red'
, 'Green'
, 'Blue'
)
;


// NORMALIZED FORMATTING OF THE CHOSEN COLOR, IF ANY
$color = isset($_POST["color"]) ? $color = ucfirst(strtolower($_POST["color"])) : $color = "";


// THE ACTION SCRIPT -- IF THE FORM HAS BEEN FILLED IN FOR FINAL SUBMISSION
if (!empty($_POST["final_submit"]))
{
    // IF THE VALUE IS ACCEPTABLE
    if (in_array($color, $good_values))
    {
        // SEND THE EMAIL HERE AND PRODUCE THE THANK-YOU PAGE
        echo "YOU CHOSE $color -- THANKS ";
        die ("EMAIL SENT");
    }
    // IF THE VALUE IS NOT ACCEPTABLE, REQUIRE A NEW VERIFICATION AND ISSUE AN ERROR MESSAGE
    else
    {
        echo "<br/>ERROR: $color IS NOT ONE OF ";
        echo implode(', ', $good_values);
        unset($_POST["verify_submit"]);
        $color = '';
    }
}


// THE ACTION SCRIPT -- IF THE FORM HAS BEEN FILLED IN FOR VERIFICATION PRIOR TO SUBMISSION
if (!empty($_POST["verify_submit"]))
{
    // IF THE VALUE IS ACCEPTABLE
    if (in_array($color, $good_values))
    {
        // TELL THE CLIENT THAT THE VERIFICATION IS OK
        echo "<br/>YOU CHOSE $color -- YOUR CHOICE HAS BEEN VERIFIED ";
        echo "<br/>YOU CAN NOW USE THE 'SEND EMAIL' BUTTON BELOW";
    }
    // IF THE VALUE IS NOT ACCEPTABLE, REQUIRE A NEW VERIFICATION AND ISSUE AN ERROR MESSAGE
    else
    {
        echo "<br/>ERROR: $color IS NOT ONE OF ";
        echo implode(', ', $good_values);
        unset($_POST["verify_submit"]);
        $color = '';
    }
}

// THE ORIGINAL FORM SCRIPT -- GENERATE THE HTML FORM TO RECEIVE THE DATA
echo "<form method=\"post\">";
echo "ENTER A COLOR -- ";
echo implode(', ', $good_values);
echo "<br/><input name=\"color\" value=\"$color\" />";

// IF THE DATA HAS NOT BEEN SUCCESSFULLY VERIFIED
if (!isset($_POST["verify_submit"]))
{
    echo "<br/><input name=\"verify_submit\" value=\"VERIFY MY ENTRY\" type=\"submit\" />";
}

// IF THE DATA HAS BEEN SUCCESSFULLY VERIFIED
if (isset($_POST["verify_submit"]))
{
    echo "<br/><input name=\"final_submit\" value=\"SEND EMAIL\" type=\"submit\" />";
}
echo "</form>";

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.