How to concat multiple GET arguments into a prepared statement

Question

Im working on a geolocation app and want to return a radius result according to tags the user

$tags = preg_replace('/\s+/', '', $tags);
$tags_list = [];
$tags_list = explode(',', $tags);
$tags = implode(" OR c.title=", $tags_list);
echo $tags;
if(
!empty($latitude) &&
!empty($longtitude) &&
!empty($radius)
){

    $qry = "SELECT o.id, o.title, o.icon, o.description,
    (
        6371 * acos
        (
            cos(
                radians( :lat )
            )
            * cos(
                radians( lat)
            ) * cos(
                radians( lng) - radians( :lng )
            ) + sin(
                radians( :lat )
            ) * sin(
                radians( lat)
            )
            )
            ) AS distance
            FROM objects o, category_object co, category c
            WHERE o.id = co.id_object AND co.id_category = c.id AND c.title=:title
            HAVING distance/1000 <= :radius
            ORDER BY distance/1000 ASC;";

    $stmt = $db->prepare($qry);

    $stmt->bindParam(":lat", $latitude);
    $stmt->bindParam(":lng", $longtitude);
    //Here
    $stmt->bindParam(":title", $tags);
    $stmt->bindParam(":radius", $radius);

    if($stmt->execute()){
        while ($result = $stmt->fetch(PDO::FETCH_OBJ)) {
            echo json_encode(array("type" => "FeatureCollection",
                "features" => array(
                    "type" => "Feature",
                    "geometry" => array("type" => "Point",
                        "coordinates" => [$latitude, $longtitude]),
                    "properties" => array(
                        "ID" => $result->id,
                        "icon" => $result->icon,
                        "tags" => "",
                        "title" => $result->title,
                        "description" => $result->description))), JSON_UNESCAPED_SLASHES);
            echo "\n\n";
        }

    }

If I input "mexican" it works perfectly fine.

localhost/../radius.php&lat=..&lng=..&radius=..&tags=mexican  <<<-- works

The problem is once I input

localhost/../radius.php&lat=..&lng=..&radius=..&tags=mexican,restaurants  <<<-- doesn't work

I think the problem is that I convert the code into a preparted statement, thats why I would like to know how to change the way I input it.

Thank you for your time!

DB connection code

<?php
class Database {

public function getConnection() {

  $this->conn = null;

  try {
    $this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
    $this->conn->exec("set names utf8");
  } catch (PDOException $e) {
    echo "connection error" . $e->getMessage();
  }

  return $this->conn;
  }

  }
  ?>

It shouldn't be a database connection problem, since the first query works perfectly — nothingspecial, Jul 04 '19 at 13:30
Sorry, recognised the code and thought it was the same question as last time — RiggsFolly, Jul 04 '19 at 13:31
added it nevertheless, I think the bindParam breaks my implode() method — nothingspecial, Jul 04 '19 at 13:33
Your code will generate sql like `WHERE o.id = co.id_object AND co.id_category = c.id AND c.title='mexican OR c.title=restaurant'` Thats not legit SQL — RiggsFolly, Jul 04 '19 at 13:38
You are going to need parenthasis as well as you are mixing AND and OR's which always means you will need `x=1 AND y=2 AND (a=10 OR a= 11)` — RiggsFolly, Jul 04 '19 at 13:44

score 2 · Accepted Answer · answered Jul 04 '19 at 13:35

2

The problem is here:

$tags = implode(" OR c.title=", $tags_list);

You need this where clause in the SQL:

AND (c.title = 'mexican' OR c.title = 'restaurants')

If you want to bind params you have to do some more work.
Like:

AND (c.title = :title_1 OR c.title = :title2)

You can assign the values with this code:

$title_1 = $tags_list[0];
$title_2 = $tags_list[1];

$stmt->bindParam(':title_1', $title_1);
$stmt->bindParam(':title_2', $title_2);

answered Jul 04 '19 at 13:35

Giacomo M

4,450
7
28
57

how would I create the code in a way that it stays dynamic? There can be a variable amount of titles – nothingspecial Jul 04 '19 at 13:42
yes there can be. you have to build your sql string dynamically – Giacomo M Jul 04 '19 at 13:43
I dont get how I can put a for loop in my code to add that, do you have a hint for me? Simple adding . for(...) . throws an error in my syntax – nothingspecial Jul 07 '19 at 17:22

How to concat multiple GET arguments into a prepared statement

1 Answers1