Fetch MySql database from cocoa app - PHP bridging [Solved]

Question

Hello to everybody,

I'm trying to download a range of rows from my MySql database, through a cocoa app that I'm developing! To do all more easy, I use a php that receive an index from my app and send back all the rows up to that index. So, my cocoa code is:

NSInteger index = 0;
NSString *urlString = [NSString stringWithFormat:@"http://localhost/test.php?index=%d&", index];

NSArray *items = [NSArray arrayWithContentsOfURL:[NSURL URLWithString: urlString]];

NSLog(@"%@", [items description]);

So, when the php receive the GET variable "index"... run this code:

$index = $_GET['index'];
$Keys = array(...);

mysql_connect($Host, $User, $Password) or die("Unable to connect to database"); 
mysql_select_db($Database) or die("Unable to select database");


$result = mysql_query("SELECT * FROM transactions where id > $index ORDER BY id");

$plist = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
$plist .= "<!DOCTYPE plist PUBLIC \"-//Apple//DTD PLIST 1.0//EN\" \"http://www.apple.com/DTDs/PropertyList-1.0.dtd\"&gt;\n";
$plist .= "<plist version=\"1.0\">\n";
$plist .= "<array>\n";

while($row = mysql_fetch_array($result)) { 
    $plist .= "\t<dict>\n";

    foreach($Keys as $key){
     $plist .= "\t\t<key>$key</key>\n";
     $plist .= "\t\t<string>$row[$key]</string>\n";
    }

    $plist .= "\t</dict>\n";
}

$plist .= "</array>\n";
$plist .= "</plist>";

echo $plist;

unset($_GET['index']);

All works fine only if from index and the last id of the database there are maximum 30 rows! If, from my cocoa code, I set index to request up to 30 rows or I set index to zero (to request all the database) ... the NSArray object contains nothing!

What I've do wrong?

Thank you in advance for the replies!

Answer 1

I'm sorry! I've solved by myself! There was an encoding issue! Just changed:

utf8_encode($row[$key])

and I've solved the problem!

Answer 2

This won't answer your question, but your code is wide open to a sql injection attack. The way to fix it is a very simple sprintf:

$result = mysql_query(sprintf("SELECT * FROM transactions where id > %d ORDER BY id", intval($index)));

Now, if a malicious user tries to inject text into the $index variable, then the intval + sprintf combination will just turn it into the number 0, thereby protecting your db.