CODEWITHSUNDEEP
phpmysql
Cookiimonstar
Cookiimonstar

Reputation: 421

Turning $_POST variables in MySQL Query

I trying to create a MySql Query from the $_POST values trouble is i don't want to use all the $_POST values as some are used for other things so i am trying to compare the value names against an array to see if they are actual filed in the DB then if they are create a Query string out of the names an values

this is what i have up to now

$i = 1;
$b = 1;

$cnt = count($_POST);

foreach ($_POST as $key => $entry)
{

$array = array('Country', 'County', 'Age', 'ect', 'ect');

foreach ($array as $arrayValue) {
    if ($arrayValue == $key) {
$b++;
        if($i == 1) {$query[] = "$key='$entry'";} 
         elseif($cnt == $b) {$query[] = "$key='$entry'";} 
           else {$query[] = "$key='$entry' AND ";}
$i++;
    }
}

}

I have gotten stuck now im not sure how to turn all the values out of the $QUERY array into a single string ie $search = "country='United Kingdom' AND county ='example'"

Any help would be much appreciated.

Upvotes: 1

Views: 448

Answers (5)

Wil Moore III
Wil Moore III

Reputation: 7194

PDO is going to provide you several advantages; not to mention parameterized queries. I've created a gist which will give you a way to whitelist only the parameters you want, builds a SQL query and executes a parameterized PDO query.

  1. Lines 08 - 18 provide you with SQL to build a scratch database/table to test this on.
  2. Lines 23 - 27 handle the whitelist and where clause building (just replace ' AND ' with ' OR ' to build a an "ANY" type filter instead of an "ALL")
  3. Line 41 builds the full SQL query
  4. Line 50 executes the parameterized query.

Here is what the $parameters array would contain based on our example:

array(3) {
  'Country' => string(3) "USA"
  'County'  => string(6) "Nassau"
  'Age'     => string(2) "21"
}

The $whereClause:

string(38) "Country = ? AND County = ? AND Age = ?"

The $sql:

string(72) "SELECT * FROM pdo_whitelist WHERE Country = ? AND County = ? AND Age = ?"

The whitelist portion of the source (full source is at https://gist.github.com/2340119):

<?php

// whitelist parameters
$post_input = ['Country' => 'USA', 'County' => 'Nassau', 'Age' => 21, 'EscalatePriviledge' => true, 'MakeMeSuperUser' => 1];
$whitelist  = ['Country', 'County', 'Age'];
$parameters = array_map('trim', array_intersect_key($post_input, array_flip($whitelist)));
$wheres     = array_map(function($fieldName){ return "${fieldName} = ?"; }, array_keys($parameters));
$whereClause= join(' AND ', $wheres);
// you should validate and normalize here as well (see: php.net/filter_var)

The PDO code that handles parameterizing the query:

$statement = $pdo->prepare($sql);
$statement->execute(array_values($parameters));

Finally, here is a summary of links for your reference:

Upvotes: 0

Baba
Baba

Reputation: 95101

What am suggest is use :

A. Case Insensitive using strtolower

B. Sanitation using mysql_real_escape_string

C. Using in_array for validation

D. implode to bring everything together 

$_POST['Country']  = "United Kingdom" ;
$_POST['County']  = "example" ;
$array = array('Country', 'County', 'Age', 'ect', 'ect');
$query = array();

foreach ($_POST as $key => $entry)
{
    if(in_array(strtolower($key), $array) || in_array($key, $array))  
    {
        $query[] = $key . " = '" . mysql_real_escape_string($_POST[$key]) . "'";

    }
}

$search = implode(" AND ", $query);
var_dump($search);

Output

string 'Country= 'United Kingdom' AND County= 'example'' (length=47)

Optional 

// Optional 
// You array is inconsistent  and contains case sensitive values .. use can use this to convert them to small letters 
array_walk($array, "toLower");
function toLower(&$item, $key)
{
    $item = strtolower($item);
}

Upvotes: 1

BenOfTheNorth
BenOfTheNorth

Reputation: 2872

You're over complicating things for yourself. If you know what variables you want to use, simply check for them and build your query:

$sql = "SELECT * FROM mytable WHERE ";

$sql_array = array();

if(isset($_POST['A'])) { $sql_array[] = 'some_col_a = '.$_POST['A']; }
if(isset($_POST['B'])) { $sql_array[] = 'some_col_b = '.$_POST['B']; }
if(isset($_POST['C'])) { $sql_array[] = 'some_col_c = '.$_POST['C']; }

$sql .= implode(' AND ', $sql_array);

You'd also need to check against mysql attacks etc, but this is the pure basics.

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

Look at implode function. You should use ' AND ' as a $glue parameter.

Upvotes: 0

Botanick
Botanick

Reputation: 663

$query = array();
$vars = array('Country', 'County', 'Age', 'etc1', 'etc2');
foreach ($vars as $v)
{
    if (isset($_POST[$v]))
    {
        $query[] = $v.' = "'.addslashes($_POST[$v]).'"';
    }
}
$query = implode(' AND ', $query);

Upvotes: 1

Related Questions