查询通过phpMyAdmin工作，但不能通过从viewController调用的php脚本工作 [英] Query works through phpMyAdmin but doesn't work through php script called from viewController
问题描述
我有一个tableViewController，它显示了一个按名称排序的地方列表！使用查询从我的MySQL数据库中检索该列表
I've a tableViewController that presents a list of places sorted by name! This list is retrieved from my MySQL database using the query
从城堡按名称选择IDUser
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".
在我的tableViewController中，我这样做:
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;
脚本php被称为:
<?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;
我在字符串中作为参数给出的纬度和经度分别是40.535568和16.503588 在phpMyAdmin中，如果我运行sql命令:
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:
从城堡ORDER BY中选择ID (POW((经度16.503588)，2)+ POW((纬度40.535568)，2))
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:
未处理的异常:您的SQL语法有错误；检查 与您的MySQL服务器版本相对应的手册 在第1行的'POW((latitude-40.535568)，2))'附近使用的语法
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
这是字符串地址:
http://localhost/myApp/fieldsRequest.php?query=SELECT%20ID%20FROM%20Castle%20ORDER%20BY%20(POW((longitude-16.503588),2)+POW((latitude-40.535568),2))
推荐答案
问题在于，stringByAddingPercentEscapesUsingEncoding仅转义那些通常在URL中无效的字符.但是一般来说，URL中允许使用某些字符(例如&和+)，但在URL参数中没有问题(例如&分隔参数，+会转换为空格字符) ).
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).
因此，您需要一种方法，而不是stringByAddingPercentEscapesUsingEncoding，不仅可以对URL中无效的那些字符进行转义，而且还可以对URL中通常具有特殊含义但在URL中有问题的那些字符进行转义参数.在这种情况下，您希望对SQL中出现的+进行百分比转义.
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.
最简单的方法是使用CFURLCreateStringByAddingPercentEscapes，而不是stringByAddingPercentEscapesUsingEncoding:
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));
}
而且，顺便说一句，您应该将参数转义到URL的百分比，而不是URL本身.
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]];
顺便说一句，我衷心劝阻您不要在URL中传递SQL字符串.这是一个严重的安全风险.您应该只传递参数(例如，纬度和经度)，然后让PHP验证这些参数，然后构建SQL本身.
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.
这篇关于查询通过phpMyAdmin工作，但不能通过从viewController调用的php脚本工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
