special characters from iPhone causing POST error in SQL

Question

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.

Answer 1

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

Answer 2

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?.

Answer 3

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).