From 4e6a8a978661bfdf6e4bd3eabe3836fef6271699 Mon Sep 17 00:00:00 2001
From: "Michael J. Rubinsky" <mrubinsk@horde.org>
Date: Wed, 10 Feb 2010 13:57:18 -0500
Subject: [PATCH] use placeholders where possible, can't use them in the
 POINT() spatial function, as this seems to break the query.

---
 kronolith/lib/Geo/Mysql.php | 27 ++++++++++++++-------------
 1 file changed, 14 insertions(+), 13 deletions(-)

diff --git a/kronolith/lib/Geo/Mysql.php b/kronolith/lib/Geo/Mysql.php
index c679092e1..354848c4d 100644
--- a/kronolith/lib/Geo/Mysql.php
+++ b/kronolith/lib/Geo/Mysql.php
@@ -26,8 +26,8 @@ class Kronolith_Geo_Mysql extends Kronolith_Geo_Sql
     public function setLocation($event_id, $point)
     {
         /* First make sure it doesn't already exist */
-        $sql = "SELECT COUNT(*) FROM kronolith_events_geo WHERE event_id = ('" . $event_id . "')";
-        $count = $this->_db->getOne($sql);
+        $sql = 'SELECT COUNT(*) FROM kronolith_events_geo WHERE event_id = ?';
+        $count = $this->_db->getOne($sql, array($event_id));
         if ($count instanceof PEAR_Error) {
             Horde::logMessage($count, __FILE__, __LINE__, PEAR_LOG_ERR);
             throw new Horde_Exception($count);
@@ -36,18 +36,19 @@ class Kronolith_Geo_Mysql extends Kronolith_Geo_Sql
         /* Do we actually have data? */
         if ((empty($point['lat']) || empty($point['lon'])) && $count) {
             // Delete the record.
-            $sql = "DELETE FROM kronolith_events_geo WHERE event_id = '" . $event_id . "'";
+            $this->deleteLocation($event_id);
+            return;
         } elseif (empty($point['lat']) || empty($point['lon'])) {
             return;
         }
 
         /* INSERT or UPDATE */
         if ($count) {
-            $sql = "UPDATE kronolith_events_geo SET event_coordinates = GeomFromText('POINT(" . $point['lat'] . " " . $point['lon'] . ")') WHERE event_id = '" . $event_id . "'";
+            $sql = "UPDATE kronolith_events_geo SET event_coordinates = GeomFromText('POINT(" . $point['lat'] . " " . $point['lon'] . ")') WHERE event_id = ?";
         } else {
-            $sql = "INSERT into kronolith_events_geo (event_id, event_coordinates) VALUES('" . $event_id . "', GeomFromText('POINT(" . $point['lat'] . " " . $point['lon'] . ")'))";
+            $sql = "INSERT into kronolith_events_geo (event_id, event_coordinates) VALUES(?, GeomFromText('POINT(" . $point['lat'] . " " . $point['lon'] . ")'))";
         }
-        $result = $this->_write_db->query($sql);
+        $result = $this->_write_db->query($sql, array($event_id));
         if ($result instanceof PEAR_Error) {
             Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR);
             throw new Horde_Exception($result);
@@ -64,8 +65,8 @@ class Kronolith_Geo_Mysql extends Kronolith_Geo_Sql
      */
     public function getLocation($event_id)
     {
-        $sql = "SELECT x(event_coordinates) as lat, y(event_coordinates) as lon FROM kronolith_events_geo WHERE event_id = '" . $event_id . "'";
-        $result = $this->_db->getRow($sql, null, DB_FETCHMODE_ASSOC);
+        $sql = 'SELECT x(event_coordinates) as lat, y(event_coordinates) as lon FROM kronolith_events_geo WHERE event_id = ?';
+        $result = $this->_db->getRow($sql, array($event_id), DB_FETCHMODE_ASSOC);
         if ($result instanceof PEAR_Error) {
             Horde::logMessage($result, __FILE__, __LINE__, PEAR_LOG_ERR);
             throw new Horde_Exception($result);
@@ -95,12 +96,12 @@ class Kronolith_Geo_Mysql extends Kronolith_Geo_Sql
         /* Allow overriding the default conversion factor */
         $factor = empty($criteria['factor']) ? $this->_conversionFactor : $criteria['factor'];
 
-        // ... if this works it will be a miracle ;)
+        $params = array($point['lat'] . ' ' . $point['lon'], $factor, $radius, $limit);
         $sql = "SELECT event_id, "
-               . "GLength(LINESTRINGFromWKB(LineString(event_coordinates, GeomFromText('POINT(" . $point['lat'] . " " . $point['lon'] . ")')))) * " . $factor . " as distance, "
-               . "x(event_coordinates) as lat, y(event_coordinates) as lon FROM kronolith_events_geo HAVING distance < " . $radius . " ORDER BY distance ASC LIMIT " . $limit;
+               . "GLength(LINESTRINGFromWKB(LineString(event_coordinates, GeomFromText('POINT(?)')))) * ? as distance, "
+               . "x(event_coordinates) as lat, y(event_coordinates) as lon FROM kronolith_events_geo HAVING distance < ?  ORDER BY distance ASC LIMIT ?";
 
-        $results = $this->_db->getAssoc($sql, false, null, DB_FETCHMODE_ASSOC);
+        $results = $this->_db->getAssoc($sql, false, $params, DB_FETCHMODE_ASSOC);
         if ($results instanceof PEAR_Error) {
             Horde::logMessage($results, __FILE__, __LINE__, PEAR_LOG_ERR);
             throw new Horde_Exception($results);
@@ -108,4 +109,4 @@ class Kronolith_Geo_Mysql extends Kronolith_Geo_Sql
 
         return $results;
     }
-}
\ No newline at end of file
+}
-- 
2.11.0