1

I have an iPhone app which submits user entered data to a SQL database. It causes an error, either cuts off the data or enters "null" when special characters are used. Seems that apostrophes and question marks and periods are ok, but anything outside of that causes it to cut off the post or post nothing at all.

My SQL Query to insert the data is as follows:

mysql_query("INSERT INTO tblQA (intResponseID, intPosterID, dPostDateTime, cCategory, cSubject, cBody) VALUES ($id, $u, NOW(), '$cat', '$sub', '$body');");

The field type in the database is a varchar(8000). SO, plenty of room where I know it's not getting cut off because of limitation of characters. The Collation i'm using is "latin1_swedish_ci"

EDIT: I test again. It's the "&" that is cutting off the string when posted to the SQL database. If I manuall insert from phpMyAdmin, I can insert string "Testing & Seeing if this works" with no problem.

iPhone code:

[p setObject:[NSString stringWithContentsOfURL:[NSURL URLWithString:[NSString stringWithFormat:@"http://website.com/script.php?user=%@&pass=%@&cat=%@&sub=%@&body=%@",[[p valueForKey:@"user"] stringByAddingPercentEscapesUsingEncoding:NSASCIIStringEncoding],[[p valueForKey:@"pass"] stringByAddingPercentEscapesUsingEncoding:NSASCIIStringEncoding],sport,@"",[[tvQ.text stringByReplacingOccurrencesOfString:@"\n" withString:@" "] stringByAddingPercentEscapesUsingEncoding:NSASCIIStringEncoding]]]] forKey:@"q"];

Additionally:

If I manually insert a string with the ampersand (&) it will appear in the iPhone app. It's only on the posting function, that it's not working.

Added 3:20p 8/10/10: I've done some additional testing and the INSERT query is working fine. I've created a separate script to take inputted data from a HTML page and inserted it just fine with the "&" symbol in it. Seems I need to take a look at what the iPhone is grabbing as a string and passing to the PHP script.

BigMike
  • 1,103
  • 3
  • 22
  • 34

3 Answers3

1

Escape your data using mysql_real_escape_string. As an ugly but quick fix, that would look like

$cat = mysql_real_escape_string($cat);
$sub = mysql_real_escape_string($sub);
$body = mysql_real_escape_string($body);
mysql_query("INSERT INTO tblQA (intResponseID, intPosterID, dPostDateTime, cCategory, cSubject, cBody) VALUES ($id, $u, NOW(), '$cat', '$sub', '$body');");

How special are your special characters? If they're Unicode, you might need to SET NAMES UTF8 as described in How can I store Unicode in MySQL?.

Community
  • 1
  • 1
MvanGeest
  • 9,536
  • 4
  • 41
  • 41
  • its the "&" character that's cutting off the post – BigMike Aug 10 '10 at 15:55
  • the "mysql_real_escape_string" seem to not allow the post to happen at all. $body = mysql_real_escape_string("'", "`", @$_REQUEST['body']); – BigMike Aug 10 '10 at 16:49
  • You're using the function incorrectly. Remove the first two arguments: `$body = mysql_real_escape_string(@$_REQUEST['body']);` – MvanGeest Aug 10 '10 at 17:28
  • Thanks MvanGeest, but it's still cutting off the string. So, if I type "Testing & Testing the app" it inserts "Testing" to the SQL database – BigMike Aug 10 '10 at 17:47
  • Have you tried debugging it in some way? `print_r` to print it out to the page, or some kind of error log if the PHP doesn't produce output? The error could come from the app itself (see tob's answer). – MvanGeest Aug 10 '10 at 18:03
  • MvanGeest - i edited and posted my iPhone code above in my original question. I'm not an expert developer, took this over from my developer who went MIA. – BigMike Aug 10 '10 at 18:30
  • I can't help you there, I don't know any Objective C. – MvanGeest Aug 10 '10 at 18:39
  • if I manually insert a string with a "&" it accepts it, which means the field is set up correctly. It's gotta be the passing from the iPhone app to my PHP script that is breaking the string. – BigMike Aug 10 '10 at 18:46
0

Try to URL encode your data before sending it to the server and decode the response respectively. NSString has methods for this.

tobiasbayer
  • 10,269
  • 4
  • 46
  • 64
  • tob, thanks. I'm using NSURL URLWithString currently. I edited my original post to include my iPhone code as well. – BigMike Aug 10 '10 at 18:32
0

Do not build SQL queries by string-concatenating user-supplied data. Use bound parameters (database abstraction layers like PDO and ADODB provide this in PHP).

nobody
  • 19,814
  • 17
  • 56
  • 77