1

I'm trying to build a SQL SELECT statement from information collected through an html form, stored in the $_POST array. My difficulty arises because the statement must vary according to user's inputs.

I have 2 dropdown menus, whose values are stored in $_POST['flow'] and $_POST['tables'], respectively. So far so good.

Then I've 4 checkboxes for countries and 15 for years. The first stored in the array $_POST['country'] and the second in $_POST['year'].

I've tried to build the SQL statement through foreach loops like this:

<?php 
//Define SQL SELECT statement
    $sql = "SELECT * FROM ".$_POST['tables']." WHERE FlowType = '".$_POST['flow']."' AND (";

    foreach ($_POST['country'] as $value) {
        $sql .= "Reporter = '$value' OR ";
    }

    $sql = substr($sql, 0, -3);
    $sql .= ") AND (";

    foreach ($_POST['year'] as $value) {
        $sql .= "TradeYear = '$value' OR ";
    }

    $sql = substr($sql, 0, -4);
    $sql .= ")";

    echo $sql;
?>

It works, yet I have a feeling it could be implemented differently. Moreover, it will be complicated to apply prepared statements for PDO query with this method.

Any suggestions? I'd appreciate some inputs on this since my knowledge on PHP (and programming in general) is very basic. Thank you!

Kostas Mitsarakis
  • 4,772
  • 3
  • 23
  • 37
Francisco Venes
  • 13
  • 4
  • Use `WHERE IN ()` – u_mulder Sep 02 '16 at 20:47
  • This code is not going to be used in production, right? This is some web dev 101 course, right? *smiles nervously and sweats profusely* – MonkeyZeus Sep 02 '16 at 21:04
  • @MonkeyZeus, it definitely is a web dev 101 level question. But you've to start somewhere xD – Francisco Venes Sep 02 '16 at 21:22
  • It will be more complicated to recover your database after it's deleted by someone who's hostile. This code is so full of holes it will whistle if it moves fast. **Use prepared statements**. This is not hard to get right. You can name every placeholder you use and then bind to those using an associative array. – tadman Sep 03 '16 at 04:50
  • If you want an example of how to do this more safely look at how an ORM like [Doctrine](http://www.doctrine-project.org/), [Propel](http://propelorm.org/) or [Eloquent](https://laravel.com/docs/5.3/eloquent) allows you to express your code as a series of clauses optionally appended to a query. – tadman Sep 03 '16 at 04:52
  • Thanks! I've managed to use prepared statements on all my input data. But since I use IN statements I've to say it was a pain in the ass xD – Francisco Venes Sep 04 '16 at 21:59

1 Answers1

-1

Ignoring my comment from above, this should work (albeit, totally unsafe):

<?php 
//Define SQL SELECT statement
$sql = "SELECT * FROM ".$_POST['tables']." WHERE FlowType = '".$_POST['flow'];

if(isset($_POST['country']) && is_array($_POST['country']) && count($_POST['country']) > 0)
{
    $sql.= " AND Reporter in ('".implode("','", $_POST['country'])."')";
}

if(isset($_POST['year']) && is_array($_POST['year']) && count($_POST['year']) > 0)
{
    $sql.= " AND TradeYear in ('".implode("','", $_POST['year'])."')";
}

echo $sql;
?>
MonkeyZeus
  • 20,375
  • 4
  • 36
  • 77