Do custom functions not work with PHP PDO prepare/execute methods?

Question

I'm writing a script with PHP PDO/SQLite that takes user-inputted zipcode, and using this distance function return all the locations within a certain radius and other criteria. I first wrote it just using a regular query method and it worked fine. But then I decided it's a good idea to sanitize it with the prepare/execute methods. When I did that, the radius search broke. All the other criteria worked fine, and if I changed the location to a straight match (not using the distance function) it worked as well. But if I use the custom function and the prepare/execute... the radius function always returns true. Has anyone else experienced this?

This code works:

db->sqliteCreateFunction('distance', function () {
    if (count($geo = array_map('deg2rad', array_filter(func_get_args(), 'is_numeric'))) == 4) {
        return round(acos(sin($geo[0]) * sin($geo[2]) + cos($geo[0]) * cos($geo[2]) * cos($geo[1] - $geo[3])) * 6378.14, 3);
    }
    return null;
}, 4);

$searches=array();
if(isset($_POST['specialty'])) {
    $searches['specialty'] = "specialtyID = $_POST[specialty]";
}
if (isset($_POST['name'])) {
    $searches['name'] = "name LIKE \"%$_POST[name]%\"";
}
if (isset($_POST['postalCode'])) {
    // leaving out code that sets $lat,$lon
    $searches['postalCode'] = "distance('$lat','$lon',lat,lon)<:$_POST[postalCodeRadius]";
}

$query="SELECT * FROM providers WHERE ".implode(" AND ",$searches);
$result = db->query($query)->fetchAll();

This code does not work for postalCode (but does for specialty and name):

// leaving out same create function code

$searches=array();
$postdata=array();
if(isset($_POST['specialty'])) {
    $searches['specialty'] = "specialtyID = :specialty";
    $postdata['specialty']=$_POST['specialty'];
}
if (isset($_POST['name'])) {
    $searches['name'] = "name LIKE \"%$_POST[name]%\"";
    $postdata['name']="%$_POST[name]%";
}
if (isset($_POST['postalCode'])) {
    // leaving out code that sets $lat,$lon
    $searches['postalCode'] = "distance('$lat','$lon',lat,lon)<:postalCodeRadius";
    $postdata['postalCodeRadius']=$_POST['postalCodeRadius'];
}

$sth = $primecare_db->prepare("SELECT * FROM providers WHERE ".implode(" AND ",$searches));
$sth->execute($postdata);
$result = $sth->fetchAll();

You need to try some simple query first, not a dynamically assembled monster. At the moment you arte trying to sit on three stools at once - using custom functions, using prepared statements, using complex dynamically built query. Why not to do each part at a time? Why not to try a prepared statement with just a simple query consists of a single custom function call and single parameter? — Your Common Sense, Oct 23 '13 at 05:04
Isn't what you're asking what I did in the top block of code? I got it working with a simple query instead of using prepare. — Dan Goodspeed, Oct 23 '13 at 05:19

Do custom functions not work with PHP PDO prepare/execute methods?

0 Answers0