Problems with PHP and MySQL

Question

I am working within Xcode and have an iOS application that you input information and the app connects to a DB via a PHP file. There is no problem when uploading, a name or an email address. But for some reason when it comes to uploading a good amount of text, via a UITextView, there becomes a problem. It succeeds when there are no punctuation at all. But when there is a period, or a question mark, it does not get uploaded to the server, it just fails. But with the email field, there is no problem when it comes to periods or even that @ symbol. I am not fluent in PHP or MySQL backend stuff, so I am very confused. Here is the code for the php file:

if (isset ($_GET["firstName"]) && isset($_GET["lastName"]) && isset($_GET["emailAddress"]) && isset($_GET["deviceType"]) && isset($_GET["problemTextField"]) && isset($_GET["iosVersion"])){
    $firstName = $_GET["firstName"];
    $lastName = $_GET["lastName"];
    $emailAddress = $_GET["emailAddress"];
    $deviceType = $_GET["deviceType"];
    $problemTextField = $_GET["problemTextField"];
    $iosVersion = $_GET["iosVersion"];

} else {
    $firstName = "User first name";
    $lastName = "User last name";
    $emailAddress = "User email address";
    $deviceType = "User device type";
    $problemTextField = "User problem text field";
    $iosVersion = "User ios version";
}

$con = mysql_connect($DB_HostName,$DB_User,$DB_Pass) or die(mysql_error()); 
mysql_select_db($DB_Name,$con) or die(mysql_error()); 

$sql = "insert into $DB_Table (firstName, lastName, emailAddress, deviceType, problemTextField, iosVersion, Status, Second_Status) values('$firstName','$lastName',
    '$emailAddress','$deviceType','$problemTextField','$iosVersion', 'Unanswered', 'Answered')";
  $res = mysql_query($sql,$con) or die(mysql_error());

mysql_close($con);
if ($res) {
  echo "success";
}else{
  echo "failed";
}

Like I said, I am not fluent in PHP, so please be nice when pulling apart my syntax for the PHP file.

EDIT: After a whole day of debugging, I have realized that if I take away spaces from in between words, everything is fine. Is there a reason for this? I don't want to have to put plus's in between everything, I know that is not correct.

Here is my Xcode code:

NSString *strURL = [NSString stringWithFormat:@"http://www.website.com/phpFile.php?firstName=%@&lastName=%@&emailAddress=%@&deviceType=%@&problemTextField=%@&iosVersion=%@", firstName.text, lastName.text, emailAddress.text, deviceType.text, self.problemTextBox.text, iosVersion.text];

    // to execute php code
    NSData *dataURL = [NSData dataWithContentsOfURL:[NSURL URLWithString:strURL]];

    // to receive the returend value
    NSString *strResult = [[NSString alloc] initWithData:dataURL encoding:NSUTF8StringEncoding];`

Answer 1

First of, do not use the mysql_* functions, they are deprecated and will be removed from php soon. Check out mysqli or PDO for a replacement.

When talking to a database, its important that the input is checked so its not bad.
You can do this with escape functions or prefferably prepared statements.
Both mysqli and pdo have prepared statements.
The prepared statements will help you with escaping, as you let the mysql wrapper know what type of data it should expect, giving it anything else will cause an error.

Your code is very open for something called SQL Injections (http://en.wikipedia.org/wiki/SQL_injection) and thats a bad thing.
This is probably why your query breaks at certain characters.
Prepared statements helps to prevent this!

Please read the mysqli documentation and specifically mysqli prepared statements:
http://php.net/manual/en/book.mysqli.php
http://php.net/manual/en/mysqli.prepare.php

I recommend Mysqli due to the fact that its more similar to the mysql api than PDO is.
But PDO might be better if you don't mind learning something new!

Also, as ddelnado mentions, GET might not be optimal for this, POST might be better suited.

Answer 2

use mysql_real_escape_string like

 $firstName = mysql_real_escape_string($_GET["firstName"]);

before all get values

Thanks

Answer 3

You should not be using the $_GET superglobal. The Get method has a limit on the size of the data where as the $_POST superglobal has not limit. So your code should look like this.\

if (isset ($_POST["firstName"]) && isset($_POST["lastName"]) && isset($_POST["emailAddress"]) && isset($_POST["deviceType"]) && isset($_POST["problemTextField"]) && isset($_POST["iosVersion"])){
$firstName = $_POST["firstName"];
$lastName = $_POST["lastName"];
$emailAddress = $_POST["emailAddress"];
$deviceType = $_POST["deviceType"];
$problemTextField = $_POST["problemTextField"];
$iosVersion = $_POST["iosVersion"];

I don't know what form you are using to submit this but the method of that form needs to be post also. In addition you should always escape your variables before inserting them in a database so sql injection does not occur.

Answer 4

A couple of observations:

1. You said:

But when there is a period, or a question mark, it does not get uploaded to the server, it just fails.

Your question presumes that the problem rests in the PHP code, but it sounds like you might not properly be percent escaping the parameters when creating the request. Notably, many people erroneously use stringByAddingPercentEscapesUsingEncoding (which will percent escape some characters, but not others). Notably, there are "legal" characters that are valid in a URL, but are not valid within a POST/GET parameter, so you need to specify those "legal" characters that should also be percent escaped (e.g. ?, &, +, etc.) within a parameter.

In short, you really want to use CFURLCreateStringByAddingPercentEscapes, which allows you to specify additional characters that should be escaped per RFC 3986. For example, I've used the following NSString category.

    @implementation NSString (URLEncode)
    
    - (NSString *)stringForHTTPRequest
    {
        return CFBridgingRelease(CFURLCreateStringByAddingPercentEscapes(kCFAllocatorDefault,
                                                                         (CFStringRef)self,
                                                                         NULL,
                                                                         (CFStringRef)@":/?@!$&'()*+,;=",
                                                                         kCFStringEncodingUTF8));
    }
    
    @end

Or use a framework like AFNetworking which simplifies the process of creating requests and takes care of this for you.

2. Note, this PHP code is returning simple string response. Instead, I'd suggest creating JSON response, which will make it easier for the Objective-C code to handle and interpret the response (and report/log the error). For example, if using the procedural rendition of mysqli:

    <?php
   
    // specify that this will return JSON
   
    header('Content-type: application/json');
   
    // open database
   
    $con = mysqli_connect("localhost","user","password","database");
   
    // Check connection
   
    if (mysqli_connect_errno())
    {
        echo json_encode(array("success" => false, "message" => mysqli_connect_error(), "sqlerrno" => mysqli_connect_errno()));
        exit;
    }
   
    // get the parameters
   
    $field1 = mysqli_real_escape_string($con, $_REQUEST["field1"]);
    $field2 = mysqli_real_escape_string($con, $_REQUEST["field2"]);
   
    // perform the insert
   
    $sql = "INSERT INTO sometable (field1, field2) VALUES ('{$field1}', '{$field2}')";
   
    if (!mysqli_query($con, $sql))
    {
        $response = array("success" => false, "message" => mysqli_error($con), "sqlerrno" => mysqli_errno($con), "sqlstate" => mysqli_sqlstate($con));
    }
    else
    {
        $response = array("success" => true);
    }
   
    echo json_encode($response);
   
    mysqli_close($con);
   
    ?>
   

Or, if using the object-oriented style:

    <?php
    
    header('Content-type: application/json');
    
    $mysqli = new mysqli("localhost", "user", "password", "database");
    
    // check connection 
    
    if ($mysqli->connect_errno) {
        echo json_encode(array("success" => false, "message" => $mysqli->connect_error, "sqlerrno" => $mysqli->connect_errno));
        exit();
    }
    
    // perform the insert
    
    $sql = "INSERT INTO sometable (field1, field2) VALUES (?, ?)";
    
    if ($stmt = $mysqli->prepare($sql)) {
        $stmt->bind_param("ss", $_REQUEST["field1"], $_REQUEST["field2"]);
        
        if (!$stmt->execute())
            $response = array("success" => false, "message" => $mysqli->error, "sqlerrno" => $mysqli->errno, "sqlstate" => $mysqli->sqlstate);
        else
            $response = array("success" => true);
    
        $stmt->close();
    } else {
        $response = array("success" => false, "message" => $mysqli->error, "sqlerrno" => $mysqli->errno, "sqlstate" => $mysqli->sqlstate);
    }
    
    $mysqli->close();
    
    echo json_encode($response);
    
    ?>

This way, the Objective-C app can receive the response, parse the JSON, and look at success to determine whether the operation was successful or not, and look at message for the error message if it wasn't successful. This just permits a more robust conversation between app and server.

3. By the way, I'd suggest that you use mysqli_real_escape_string or manually bind parameters to a prepared statement to protect against SQL injection attacks or unintended errors arising from unescaped strings in your SQL.

4. I'd also suggest you create a POST request rather than a GET request which allows larger values.