Query for condition 'ALL' with Variable?

Question

I get confused for post variable to sql query, here is sample of my report.php code

<form action="report.php">
<select id="status" name="status">                      
  <option value="MARRIED">married</option>
  <option value="SINGLE">Single</option>
  <option value="ALL">ALL</option>
</select>

<input type="submit" value="Seach">
</form>

<?php

$status= $_GET['status'];

// Create DB connection
$sql = "SELECT * FROM member WHERE status ='$status'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<B>id: </B>" . $row["user_id"]. " -- <b>Date Record:</b> " . $row["created"]. " -- <b>Last Seen</b> " . $row["last_seen"]. " -- <b>Status: </b> " 
    }
} else {
    echo "0 results";
}
$conn->close();

?>

How do i Query If "ALL" condition is Selected?

score 4 · Answer 1 · edited Dec 24 '14 at 16:34

4

I dont know the PHP so this is not the exact code but concept should be like this

if( $status == 'ALL' )
    $sql = "SELECT * FROM member";
else
    $sql = "SELECT * FROM member WHERE status ='$status'";

edited Dec 24 '14 at 16:34

Simone Nigro

3,928
2
24
55

answered Dec 24 '14 at 16:31

HaveNoDisplayName

7,711
106
32
44

1

watch out [sql-injection](http://stackoverflow.com/questions/601300/what-is-sql-injection) – Simone Nigro Dec 24 '14 at 16:34

score 2 · Accepted Answer · answered Dec 24 '14 at 16:50

It would be very wise in this case to store the $status variable in POST instead, since the SQL query depends on what value is stored in the URL, and is thus exposed to the user.

Another thing, since you are dealing with legacy code here is to make extra sure that you filter the user input and SQL query as much as possible. The thing with using older and obsolete functionality is that you will still be vulnerable to XSS and SQL injection attacks regardless of the precautions you take so it is highly recommended you go with either the MySQLi or PDO (PHP Data Objects) extension instead as these offer more stable and advanced functionality.

$status = htmlspecialchars($_GET['status'], ENT_QUOTES);
$where = '';
if ($status != 'ALL') {
    $where = 'WHERE status = "$status"';
}

$sql = mysql_real_escape_string('SELECT * FROM member ' . $where);
$results = mysql_query($sql);

Hi hRdCoder, thank you, I never thought about it, Yes i read i lot about than vulnerability,but it will fine for me, because this only for my small application and private uses only, thank you hRdCoder. — esi0411, Dec 24 '14 at 18:55
Your very welcome, esi0411. I hope that my suggestion is helpful to you. — hRdCoder, Dec 24 '14 at 20:06

score 0 · Answer 3 · answered Dec 24 '14 at 17:01

0

In PHP file

<?php

$status= $_GET['status'];

if($status == 'ALL'){
  $where = '';
}else{
  $where = 'status = '".$status."' ';
}
// Create DB connection
$sql = "SELECT * FROM member WHERE ".$where." ";

?>

answered Dec 24 '14 at 17:01

jay.jivani

1,514
1
14
30

Query for condition 'ALL' with Variable?

3 Answers3