Query works through phpMyAdmin but doesn't work through php script called from viewController

Question

I've a tableViewController that presents a list of places sorted by name! This list is retrieved from my MySQL database using the query

SELECT IDUser FROM Castle ORDER BY Name

But my goal is to sort them by location according to userLocation. So, Any Place in the database has the fields "latitude" and "longitude".

In my tableViewController I do it:

NSString *IDString = @"SELECT ID FROM Castle ORDER BY Name ASC";

NSMutableString *strURL = [NSMutableString stringWithFormat:@"http://localhost/myApp/fieldsRequest.php?query=%@",query];

[strURL setString:[strURL stringByAddingPercentEscapesUsingEncoding:NSUTF8StringEncoding]];

NSData *dataURL = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];

NSError* error;

NSDictionary* json = [NSJSONSerialization
                          JSONObjectWithData:dataURL
                          options:kNilOptions
                          error:&error];

NSMutableArray *results = [[NSMutableArray alloc] init];

int numRow = 0;

for (NSArray *arrow in json) {
        [results addObjectsFromArray:arrow];
        numRow++;
}

return results;

Script php that is called:

<?php
require 'mycredentials.php';
$resultCredentials = sdbmyCredentials();

if ($resultCredentials == YES) {
       $arr = array();

        $query = $_GET[query];
        $results = mysql_query($query) or die ("Unhandled exception: " . mysql_error());

       // Add the rows to the array 
       while($obj = mysql_fetch_row($results)) {
       $arr[] = $obj;
       }
       echo json_encode($arr);
    }
    ?>

It works perfectly and I retrieve the list of places order by name. Instead when I do it:

_IDString = [NSString stringWithFormat:@"SELECT ID FROM Castle ORDER BY (POW((longitude-%f),2) + POW((latitude-%f),2))", longitude,latitude];

NSMutableString *strURL = [NSMutableString stringWithFormat:@"http://localhost/myApp/fieldsRequest.php?query=%@",query];

[strURL setString:[strURL stringByAddingPercentEscapesUsingEncoding:NSUTF8StringEncoding]];

NSData *dataURL = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];

NSError* error;

NSDictionary* json = [NSJSONSerialization
                          JSONObjectWithData:dataURL
                          options:kNilOptions
                          error:&error];

NSMutableArray *results = [[NSMutableArray alloc] init];

int numRow = 0;

for (NSArray *arrow in json) {
        [results addObjectsFromArray:arrow];
        numRow++;
}

return results;

Latitude and longitude that I give as parameters in the string are respectively 40.535568 and 16.503588 In phpMyAdmin if I run the sql command:

SELECT ID FROM Castle ORDER BY (POW((longitude-16.503588),2)+POW((latitude-40.535568),2))

it works perfectly and it gives me the results. But if I execute it by code as I wrote before, it doesn't produce results, but goes into error:

Unhandled exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'POW((latitude-40.535568),2))' at line 1

This is the string address:

http://localhost/myApp/fieldsRequest.php?query=SELECT%20ID%20FROM%20Castle%20ORDER%20BY%20(POW((longitude-16.503588),2)+POW((latitude-40.535568),2))

Answer 1

The problem is that stringByAddingPercentEscapesUsingEncoding only escapes those characters which are not valid in a URL in general. But there are some characters that are permissible in a URL in general (e.g. & and +), but are not problematic within a URL parameter (e.g. the & delimits parameters, the + translates to a space character).

So, instead of stringByAddingPercentEscapesUsingEncoding, you want a method that will percent escape not only those characters that are not valid in a URL, but also those characters that might have special meanings within a URL in general but which are problematic within a parameter. In this case, you want to percent escape the + that appears in your SQL.

The easiest way to do that is to use CFURLCreateStringByAddingPercentEscapes, instead of stringByAddingPercentEscapesUsingEncoding:

- (NSString *)percentEscapeURLParameter:(NSString *)string
{ 
    return CFBridgingRelease(CFURLCreateStringByAddingPercentEscapes(kCFAllocatorDefault,
                                                                     (CFStringRef)string,
                                                                     NULL,
                                                                     (CFStringRef)@":/?@!$&'()*+,;=",
                                                                     kCFStringEncodingUTF8));
}

And, by the way, you should be percent escaping the parameters to the URL, but not the URL itself.

NSString *sql = [NSString stringWithFormat:@"SELECT ID FROM Castle ORDER BY (POW((longitude-%f),2) + POW((latitude-%f),2))", longitude, latitude];

NSString *percentEscapedSQL = [self percentEscapeURLParameter:sql];

NSString *strURL = [NSString stringWithFormat:@"http://localhost/myApp/fieldsRequest.php?query=%@", percentEscapedSQL];

// don't percent-escape the full URL
//
// [strURL setString:[strURL stringByAddingPercentEscapesUsingEncoding:NSUTF8StringEncoding]];

---

As an aside, I'd heartily discourage you from passing the SQL string in the URL. That's a serious security risk. You should just pass parameters (e.g. the latitude and longitude), and have the PHP validate those parameters and then build the SQL itself.