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. — user1864610
– user1864610, Commented Jan 14, 2015 at 2: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
– jonmrich, Commented Jan 14, 2015 at 2: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. — user1864610
– user1864610, Commented Jan 14, 2015 at 3: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
– jonmrich, Commented Jan 14, 2015 at 4:01

Community · Accepted Answer · 2017-05-23 10:26:28Z

0

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

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Jan 21, 2015 at 13:50

jonmrich

4,3738 gold badges52 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Istiaque Ahmed · Accepted Answer · 2015-01-14 03:17:37Z

-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, 2015 at 3:17

Istiaque Ahmed

6,53626 gold badges83 silver badges145 bronze badges

4 Comments

user1864610 Over a year ago

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

Istiaque Ahmed Over a year ago

@HoboSapiens, all_vars is an array. The query fecthes value, does not insert anything- so why sanitisation?

user1864610 Over a year ago

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.

Istiaque Ahmed Over a year ago

@HoboSapiens, 'Any single value in the $_GET array is a string.' - disagreed.

Collectives™ on Stack Overflow

Pass array of "WHERE" statements to php

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related