How to ignore SQL Select command for a row if the value is empty

Question

I have this SQL query:

$sql2 = "SELECT * FROM MyTable WHERE time1='".$_POST['time']."' AND type1='".$_POST['type']."';

The result:

$result1 = $conn->query($sql2);

if ($result1->num_rows > 0) {
    // output data of each row
    while($row = $result1->fetch_assoc()) {

        echo $row["name1"];

name1 row is the name of the activity in database.

The HTML form has these two select lists:

<select id="time" name="time" required="" data-do-validate="true">
    <option value="">Select ...</option> // this is the NULL value
    <option value="time1">Type 1</option>
    <option value="time2">Type 2</option>
    <option value="time3">Type 3</option>
    <option value="time4">Type 4</option>
                        </select>

<select id="type" name="type" required="" data-do-validate="true">
    <option value="">Select ...</option> // this is the NULL value
    <option value="type1">Type 1</option>
    <option value="type1">Type 2</option>
    <option value="type1">Type 3</option>
    <option value="type1">Type 4</option>
                        </select>

So what I try to do is selecting from the database the name of all activities with matching data submitted by the HTML form.

BUT what I additionally want is to ignore the SQL Select for the Time ( For example ) if the user leave the value empty ( Select ... ), the result in this case will be the names of all activities based only on the Type. And if he select a time and a type the result in this case will be more specified based on the Time and the Type of the activity.

What can I do?

Thank you!

Bom, injection. I hope no one will ever ask for type `O'Maley` — Xenos, Oct 07 '16 at 16:04
I just *so* much want to POST a type value of "**`foo' OR 1=1 ORDER BY 1 -- `**". — spencer7593, Oct 07 '16 at 16:07
security risk in my case is not a problem and all the data are public — sadssdsada, Oct 07 '16 at 16:12
I hate when people say *"I'm not that far along..."* or *"This site will not be public..."* or *"It's only for school, so security doesn't matter..."*. If teachers and professors are not talking about security from day one, they're doing it wrong. Challenge them. They're teaching sloppy and dangerous coding practices which students will have to unlearn later. I also hate it when folks say, *"I'll add security later..."* or *"Security isn't important now..."* or *"Ignore the security risk..."*. If you don't have time to do it right the first time, when will you find the time to add it later? — Jay Blanchard, Oct 07 '16 at 16:31
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Oct 07 '16 at 16:31
The statement... "[a] security risk ... is not a problem" is true in the sense that a security *risk* can be mitigated. An actual loss is a *problem*. Once there is a problem, it's too late for mitigation. In that sense, a *problem* is not a security *risk*. And there are a number of large organizations that can also truthfully say "all the data are public" when that data *shouldn't* be public, because of software written without consideration for mitigating potential security risks. — spencer7593, Oct 07 '16 at 18:12
Addressing SQL Injection vulnerabilities is *low hanging fruit*. It is *not* at all difficult to follow well-known best practice coding conventions when writing software. What *is* hard is going back to thousands of lines of code that has already been written, tested and deployed, and making changes to it. What *is* hard is *unlearning* habitual coding patterns. (I really don't care that security risk is not a concern "*in this case*". it *is* a concern in nearly every other case. There is *no* excuse for knowingly writing code that is vulnerable to SQL Injection. 'nuff said. — spencer7593, Oct 07 '16 at 18:23

score 0 · Answer 1 · answered Oct 07 '16 at 16:06

Why running for a complete dynamically built query?

SELECT * FROM MyTable WHERE (1=? AND time1=?) AND type1=?;

Having the parametrized values: isset($_POST['time']) ? 1 : 0, isset($_POST['time']) ? $_POST['time'] : null, and $_POST['type'].

MySQL will handle the optimization of not doing the (1=0 AND time1=?) part.

score -1 · Answer 2 · answered Oct 07 '16 at 16:06

    if(isset($_POST["time"])){ // Not Empty
      $sql2 = "SELECT * FROM MyTable WHERE time1='".$_POST['time']."' AND type1='".$_POST['type']."';
    } 
    else{ //Only select the type
      $sql2 = "SELECT * FROM MyTable WHERE type1='".$_POST['type']."';
    }

    $result1 = $conn->query($sql2);

if ($result1->num_rows > 0) {
    // output data of each row
    while($row = $result1->fetch_assoc()) {

        echo $row["name1"];

score -1 · Accepted Answer · edited Oct 07 '16 at 16:09

-1

You could build your Statement depending on the $_POST Values (by the way, in productive you should't use $_POST values directly in the SQL statement).

$connect = "";
$sql2 = "SELECT * FROM MyTable ";

if($_POST['time']!='' || $_POST['type']!='') {
  $sql2 .= "WHERE ";
  if($_POST['time']!='') {
      $sql2 .= "time1='".$_POST['time']."'";
      $connect = " AND ";
  }
  if($_POST['type']!='') {
     $sql2 .= $connect . "type1='".$_POST['type']."'";
  }
}

edited Oct 07 '16 at 16:09

Don't Panic

41,125
10
61
80

answered Oct 07 '16 at 16:07

sebkrueger

386
5
16

dude you are awesome! :D – sadssdsada Oct 12 '16 at 13:03
this method can handle 1000000000 parameters without any problems :) – sadssdsada Oct 12 '16 at 13:04

How to ignore SQL Select command for a row if the value is empty

3 Answers3