在MySQL Query中转换$ _POST变量

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

我试图从$ _POST值创建一个MySql查询麻烦是我不想使用所有$ _POST值，因为有些用于其他的东西，所以我试图比较值名称与数组，看看它们是否是实际归档在DB中然后如果它们是在名称中创建一个Query字符串的值

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'"

我现在已经陷入困境我不知道如何将$ QUERY数组中的所有值转换为单个字符串，即$ search =“country ='United Kingdom'AND county ='example'”

Any help would be much appreciated.

任何帮助将非常感激。

5 个解决方案

#1

$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);

#2

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.

你还需要检查mysql攻击等，但这是纯粹的基础知识。

#3

What am suggest is use :

建议使用：

A. Case Insensitive using strtolower

A.案例不敏感使用strtolower

B. Sanitation using mysql_real_escape_string

B.使用mysql_real_escape_string进行卫生

C. Using in_array for validation

C.使用in_array进行验证

D. implode to bring everything together

D.内心将所有东西放在一起

$_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);
}

#4

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

看看内爆功能。你应该使用'AND'作为$ glue参数。

#5

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.

PDO将为您提供多种优势;更不用说参数化查询了。我已经创建了一个要点，它将为您提供一种方法，只将您想要的参数列入白名单，构建SQL查询并执行参数化PDO查询。

Lines 08 - 18 provide you with SQL to build a scratch database/table to test this on.
第08 - 18行为您提供了SQL来构建一个临时数据库/表来测试它。
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")
第23 - 27行处理白名单和where子句构建（只需将'AND'替换为'OR'以构建“ANY”类型过滤器而不是“ALL”）
Line 41 builds the full SQL query
第41行构建完整的SQL查询
Line 50 executes the parameterized query.
第50行执行参数化查询。

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

以下是基于我们的示例的$ parameters数组将包含的内容：

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

The $whereClause:

$ whereClause：

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

The $sql:

$ 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):

源的白名单部分（完整源代码位于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:

处理参数化查询的PDO代码：

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

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

最后，这里是链接的摘要供您参考：

gist.github.com/2340119
gist.github.com/2340119
php.net/pdo.prepared-statements
php.net/pdo.prepared-statements
php.net/filter_var
php.net/filter_var

#1

$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);

#2