CODEWITHSUNDEEP
phpmysql
log N
log N

Reputation: 945

How should I generate a dynamic querystring in php?

I am creating a search engine for a book database.I have a radio button for exact search and similar search.My query is that how should i generate a SQL query of exact search.For eg I have ISBN and title as the field.Likewise I have many fields and they can be kept empty and populated too.How should i generate a SQL query for this query?

eg if the title is populated and isbn is populated then it should be

select * from book_info where isbn="$_POST['isbn']" and title="$_POST['title']"

What if 10 fields are populated then how should i generate? Checking whether the filed is empty of not is a solution. But is there a better solution than this?

Upvotes: 0

Views: 142

Answers (3)

GrandmasterB
GrandmasterB

Reputation: 3455

Don't ever do that. What you are doing is inviting an SQL injection attack, which makes your site vulnerable to hacks.

In PHP, use PDO, and parameterized queries. 

$isbn = $_POST['isbn'] . '';
$title = $_POST['title'] . '';


$db = new PDO( "host", "user", "pass");
$stm = $db->prepare( "select id, name, title, whatever from book_info where isbn= ? and and title= ?");
$stm->bindParam( 1, $isbn);
$stm->bindParam( 2, $title);
$stm->execute();
while ($row = $stm->fetchObject())   //or just fetch()
{
   $othervar = $row->name;
   //etc
}

Upvotes: 1

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324630

xkcd

That aside, getting to the point...

Try something like this:

$allowed_keys = ["isbn","title",...]; // or array("isbn"...) if you're not up-to-date
$postvars = array_intersect_key($_POST,array_flip($allowed_keys));
$conditions = []; // or array(); for old PHP
foreach($postvars as $k=>$v) {
    $conditions[] = "`".$k."`='".mysql_real_escape_string($v)."'";
    // use whatever function is suitable for the library you're using
    // I'm assuming the basic mysql library, based on your injection vulnerability
}
if( $conditions) {
    $query = "select * from `book_info` where ".implode(" and ",$conditions);
    // run query
}

Upvotes: 2

invisal
invisal

Reputation: 11171

You can put all the option in the list like the following code.

$search = array("isbn" => $_POST['isbn'],
                "title" => $_POST['title'],
                "table_field" => $input_value);

Then, using for each loop to construct the condition part.

$sql = "SELECT * FROM book_info";
$condition = "";
foreach($search as $key => $value) {
    if (isset($value) && ($value != "")) {
        if ($condition != "") {
            $condition .= " AND ";
        }
        $condition .= "{$key}=:{$key}";
    }
}

Using prepare statement to prevent SQL Injection.

$sh = $db->prepare($sql . " WHERE " . $condition);
foreach($search as $key => $value) {
    if (isset($value) && ($value != "")) {
        if ($condition != "") {
            $condition .= " AND ";
        }
        $sh->bindValue(":{$key}", $value);
    }
}

Upvotes: 4

Related Questions