Issue querying database

[toxicandy]

Eariler I wrote this topic: http://www.daniweb.com/web-development/php/threads/482142/searching-using-optional-parameters and I got ideas and got to this point with help of another guy:

<?php

$whereClause = "WHERE ";

foreach($_POST as $k => $v ){

  if( $v != "" ){
    $whereClause = $whereClause . $k . "='" . $v . "' AND ";
  }
}
$db = new PDO('mysql:host=localhost;dbname=petrzilk_test;charset=utf8', 'petrzilk_dbAdmin', '***********'); // Connecting to Database
   $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Error statement
   $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
   $query = $db->prepare("'SELECT * FROM VolunDB " . $whereClause . " 1 = 1'");
   $query->execute();
   $result = $query->fetch(PDO::FETCH_OBJ);
?>

But I get an error that is:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''SELECT * FROM VolunDB WHERE fname='andy' AND 1 = 1'' at line 1' in /home/petrzilk/public_html/Database/testSearch.php:14 Stack trace: #0 /home/petrzilk/public_html/Database/testSearch.php(14): PDO->prepare(''SELECT * FROM ...') #1 {main} thrown in /home/petrzilk/public_html/Database/testSearch.php on line 14

Any help?
Also how would I go about securing this code from injection? I tried using a filtered input but I can't get it to work the way the foreach is set up.

[cereal]

The problem starts with the quotes, you're writing:

$q = "'SELECT * FROM mytable'";

If you read carefully you will see double and single quotes surrounding the entire query, use only one type, not both, otherwise the all is interpreted as a single string.

Also how would I go about securing this code from injection? I tried using a filtered input but I can't get it to work the way the foreach is set up.

Use prepared statements as shown by DJBirdi in the other thread of yours, you can generate dynamic queries, but you must separate the values from it and use placeholders.

Basing on your above code you could do this:

$postkeys = array_keys($_POST);
$postvalues = array_values($_POST);

And then generate the list of conditions, but this is highly unsafe, because an attacker can submit an arbitrary POST request and enter any input desires, changing the query conditions.

A more safer approach is this:

$post = array();
array_map(function($key, $value) use(&$post)
{
    switch($key)
    {
        case 'fname':
        case 'lname':
            $post['keys'][] = $key . ' = ?';
            $post['values'][] = $value;
            break;
    }
    return $post;
}, array_keys($_POST), $_POST);

Just list in the switch statement the fields you want to include in your query, e.g. case 'email':, so even if an extra column is submitted, it will not be included in your query. In practice, with the above we are creating a white list. I'm using array_map() to loop the $_POST array but you can use any loop you prefer.

Now you can use $post['keys'] and $post['values'] for the prepared statement:

$query = "SELECT * FROM VolunDB WHERE " . implode(' AND ', $post['keys']);

$stmt = $db->prepare($query);
$stmt->execute($post['values']);
$result = $stmt->fetchAll(PDO::FETCH_OBJ);

But remeber, this will not remove HTML tags from the submitted code, that's up to you, before saving the input you must always sanitize and validate it. The prepared statement will only avoid mess with the query, not with the content. For more information check:

• http://php.net/manual/en/intro.filter.php

[toxicandy]

I have it working now but I ran into a small problem, I have check boxes that get saved to one field in the SQL server and would be saved like Item1,Item2,Item3 and so on, but when I am searching and I want to make a search like Item1,Item3 but not item2 I want that previous record with all 3 to still appear because it has 1 and 3, I don't want the code to look for exact matches but rather only to contain what I search for in that row, is that possible?

[cereal]

If it's a comma separated list you could use FIND_IN_SET():

SELECT * FROM VolunDB WHERE FIND_IN_SET('item1', items) AND FIND_IN_SET('item3', items);

So the above becomes:

$post['keys'][] = "FIND_IN_SET(?, $key)";

Or you could use LIKE:

SELECT * FROM VolunDB WHERE items LIKE '%item1%item3%';

To build this in PHP you have change the query builder to:

$values = '%'.implode('%', $post['values']).'%';

# then
$query = "SELECT * FROM VolunDB WHERE items LIKE ?";
$stmt = $db->prepare($query);
$stmt->execute($values);

But FIND_IN_SET() is optimized for comma separated lists.

Documentation:

• http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set

If possible, avoid string lists and separate the values to different columns.