Pass array of "WHERE" statements to php

Question

I'm looking to take the values of several select boxes and join them all up into an array to use to form a mySQL query. The array contains the key and value for the WHERE statements. I'm making the array like this:

var allvars = [];
$('#more_rows').click(function(){
var onevar = $('#first_select option:selected').text();  
var twovar = $('#second_select option:selected').text();
allvars.push(onevar + "," + twovar );
});

This ends up giving me an array like this:

["age , 29", "gender, male"]

The question is...how do I get this into PHP to return a count? I'm working with something like this.

jQuery:

  $.ajax({
        url: 'att_count.php',
        type: 'GET',
        dataType: 'JSON',
        data: {all_att: allvars},
        success: function(data) {
       //code to display count
 }
 });

PHP (relevant parts):

$whereAttributes = isset($_GET['all_att'])? "{$_GET['all_att']}" : '';

$whereAttributes = mysql_real_escape_string($whereAttributes);

$sql = "select count(guid) from full_db2 where {$whereAttributes};";

$result = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);

header('Content-type: application/json');
echo json_encode($result);

Of course, this doesn't work and gives me something like this is the error log:

Call to a member function fetchAll() on a non-object in ../att_count.php on line 14 (which is the line in the PHP above starting with $sql).

I'm sure there are some better ways to do this overall, but it's my first crack at it. The goal is to be able to pass an "unlimited" number of key/value pairs into PHP to return a COUNT for all the records that have that combination of key/value. Assume that all the joins are "AND" for now...so the final query should be something like this using the array above:

SELECT COUNT(guid) FROM full_db2 WHERE age = '25' AND gender = 'male';

Any suggestions on how to fix this using my current code would help and any suggestion on a cleaner, better way to put this query together would be appreciated.

Whatever else you do, don't construct your `WHERE` clause in Javascript and have PHP pass it to MySQL unedited - there lies the path of SQL injection. Validate the key/value pairs with PHP and build your `WHERE` clause in PHP from the result. — , Jan 14 '15 at 02:50
I think I'm doing it the way you suggest. I'm just passing attributes in a URL to PHP and there it gets formed into the `WHERE` clause. I've also added `mysql_real_escape_string`. Take a look at my code above and let me know if I'm doing it correctly. — jonmrich, Jan 14 '15 at 02:52
Your code takes the raw value off `$_GET['all_att']` and plugs it straight into an SQL query. You need to check that you have keys that you accept and values that make sense then assemble a query from that. — , Jan 14 '15 at 03:23
@HoboSapiens The keys and values come right from the database, as my selects are dynamically generated from the database. So, assuming this is the case, how do I construct a proper query? — jonmrich, Jan 14 '15 at 04:01

score 0 · Answer 1 · edited May 23 '17 at 10:26

Here's a question where I was able to get this figured out after making a little more progress:

Create array for PDO from variables passed from jquery

Here's the relevant part:

$q = $dbh->prepare("DESCRIBE full_db2");
 $q->execute();
$table_fields = $q->fetchAll(PDO::FETCH_COLUMN);
$validKeys = $table_fields;
$sql = 'SELECT COUNT(guid) FROM full_db2';
$any_condition = false;
foreach($_GET as $key=>$val) {
   if (!empty($val) && in_array($key,$validKeys)) {
     if ($any_condition) {
       $sql .= ' AND '.$key.' = :'.$key;
     } else {
       $sql .= ' WHERE '.$key.' = :'.$key;
       $any_condition = true;
     }
   }
}

$stmt = $dbh->prepare($sql);

foreach($_GET as $key=>$val) {

if (!empty($val)  && in_array($key,$validKeys)) {
 $stmt ->bindValue(':'.$key, $val, PDO::PARAM_STR);
 }
}

$stmt->execute();

score -1 · Answer 2 · answered Jan 14 '15 at 03:17

-1

Use the following code in server side :

// $whereAttributes = isset($_GET['all_att'])? "{$_GET['all_att']}" : ''; THIS LINE WAS WRONG

 $whereAttributes =$_GET['all_att'];

$age_data=$whereAttributes[0];

$gender_data=$whereAttributes [1];

$age_data_array=explode(','$age_data);

$gender_data_array=explode(',',$gender_data);

$where_clause=trim($age_data_array[0])." = ".trim($age_data_array[0])." AND " .trim($gender_data_array[0])."=".trim($gender_data_array[1]);

$sql = "select count(guid) from full_db2 where ". $where_clause;

answered Jan 14 '15 at 03:17

Istiaque Ahmed

6,072
24
75
141

Don't use this. `$_GET['all_att']` contains a string, not an array. Everything thereafter fails from that one error. There's still no sanitisation going on, so SQL injection remains a possibility – Jan 14 '15 at 03:20
@HoboSapiens, `all_vars` is an array. The query fecthes value, does not insert anything- so why sanitisation? – Istiaque Ahmed Jan 14 '15 at 03:24
Any single value in the `$_GET` array is a string. Your code is treating it as an array, so it will fail. It's also possible to pass a key/value pair like 'age, age', which will result in a where clause like `where age=age` (if your code worked) that will result in all rows being returned. Here that might be nothing more than inconvenient, but elsewhere similar code might allow a login where none should be possible, or deletion of all the rows in a table, or other nasties I haven't thought of yet. – Jan 14 '15 at 03:31
@HoboSapiens, 'Any single value in the $_GET array is a string.' - disagreed. – Istiaque Ahmed Jan 14 '15 at 03:44

Pass array of "WHERE" statements to php

2 Answers2