Swift not passing variables to PHP Web Service to query my SQL database

Question

I have a PHP Web Service that can insert a new row to my database if I hardcode the values in to the SQL query directly. But when I try to do this using variables passed to the PHP script from my Swift function it breaks down. I have add print statements to my Swift so I am confident that the values are being passed to the function correctly and the error must lie in the lines afterwards.

PHP: I have commented the SQL query line out with the hardcoded values - Carrots, but this query does work so I think PHP->SQL is working ok, but Swift->PHP is the problem.

 <?php
$servername = "localhost";
$username = "*******";
$password = "*****";
$dbname = "*******";

$product_name = $_POST['product_name'] ?? 'DefaultProduct';
$code = $_POST['code'] ?? 'DefaultCode';
$Brands = $_POST['brands'] ?? '1';
$Comp1Mat = $_POST['Comp1Mat'] ?? 'DefaultMat';
$Comp2Mat = $_POST['Comp2Mat'] ?? '1';

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

//$sql = "INSERT INTO Products (product_name, code) VALUES ('Carrots', '0135')";

$sql = $conn->prepare("INSERT INTO Products (product_name, code, brands, Comp1Mat, Comp2Mat) VALUES (?, ?, ?, ?, ?)";
$sql->bind_param("isssi", $product_name, $code, $Brands, $Comp1Mat, $Comp2Mat);
$sql->execute();
$sql->close();

if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?> 

Swift:

func addProduct(product_name: String, code: String, brands: String, Comp1Mat: String, Comp2Mat: String){
        
        print(product_name)
        print(code)
        print(brands)
        print(Comp1Mat)
        print(Comp2Mat)
        
        let insertProductURL = URL(string: "http://recyclingmadesimple.xyz/insertproduct.php")!
        var urlRequest = URLRequest(url: insertProductURL)
        urlRequest.httpMethod = "POST"
        // Set HTTP Request Headers
        urlRequest.setValue("application/json", forHTTPHeaderField: "Accept");
        urlRequest.setValue("application/json", forHTTPHeaderField: "Content-Type");
        
        let postString = "product_name=\(product_name)&code=\(code)&brands=\(brands)&Comp1Mat=\(Comp1Mat)&Comp2Mat=\(Comp2Mat)"
        urlRequest.httpBody = postString.data(using: .utf8)
        
        let task = URLSession.shared.dataTask(with: urlRequest) { (data, response, error) in
            if error != nil {
                print("error = \(String(describing: error))")
                return
            }
            print("response - \(String(describing: response))")
            let responseString = NSString(data: data!, encoding: String.Encoding.utf8.rawValue)
            print("response string - \(String(describing: responseString))")
        }
        task.resume()

    }

Error:

response - Optional(<NSHTTPURLResponse: 0x281191600> { URL: http://recyclingmadesimple.xyz/insertproduct.php } { Status Code: 500, Headers {
    Connection =     (
        "Upgrade, close"
    );
    "Content-Length" =     (
        0
    );
    "Content-Type" =     (
        "text/html; charset=UTF-8"
    );
    Date =     (
        "Thu, 17 Jun 2021 12:38:25 GMT"
    );
    Server =     (
        Apache
    );
    Upgrade =     (
        "h2,h2c"
    );
    Vary =     (
        "User-Agent"
    );
    "X-Powered-By" =     (
        "PHP/7.4.16"
    );
} })
response string - Optional()

Many thanks.

Answer 1

I got this working after taking on board the suggestions. Thank you all. I changed the parsing to JSON in swift and improved the binding of variables in the PHP file.

PHP:

<?php

$code = $_POST["code"];

// Create connection
$con=mysqli_connect("localhost","X","X","X");

// Check connection
// Return the error code if the connection is not successful.
if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}


/* create a prepared statement */
// A prepared statement is one that is loaded initially and then executed many times over and over when we attach (bind) new variables to the placeholders we have inserted. We use ? marks to signify placeholders than must be later filled with values.
$stmt = mysqli_prepare($con, "SELECT product_name, brands, Comp1Mat, Comp2Mat, Comp3Mat, Comp1Name, Comp2Name, Comp3Name FROM Products WHERE code=?");


// Bind the parameters the user has entered with the ? marks in the SQL query
mysqli_stmt_bind_param($stmt, "s", $code);

/* execute query */
mysqli_stmt_execute($stmt);

// Bind the results we get with some new variable that we will output in the print statement.
mysqli_stmt_bind_result($stmt, $product_name, $brands, $Comp1Mat, $Comp2Mat, $Comp3Mat, $Comp1Name, $Comp2Name, $Comp3Name);

/* fetch value */
mysqli_stmt_fetch($stmt);

//create an array
$emparray = array("code" => $code, "product_name" => $product_name, "brands" => $brands, "Comp1Mat" => $Comp1Mat, "Comp2Mat" => $Comp2Mat, "Comp3Mat" => $Comp3Mat, "Comp1Name" => $Comp1Name, "Comp2Name" => $Comp2Name, "Comp3Name" => $Comp3Name);
printf(json_encode($emparray));

 
// Close connections
mysqli_close($con);
?><?php

Swift:

func findSingleProduct(code: String){
        // Completes an SQL query on the Products database to find a product by it's barcode number.
        // prepare json data
        let insertProductURL = URL(string: "http://recyclingmadesimple.xyz/service.php")!
        var urlRequest = URLRequest(url: insertProductURL)
        urlRequest.httpMethod = "POST"
    
        let postString = "code=\(code)"
        urlRequest.httpBody = postString.data(using: String.Encoding.utf8)
        
        let task = URLSession.shared.dataTask(with: urlRequest) { data, response, error in
            guard let data = data, error == nil else {
                print(error?.localizedDescription ?? "No data")
                return
            }
            let responseJSON = try? JSONSerialization.jsonObject(with: data, options: [])
            if let responseJSON = responseJSON as? [String: Any] {
                let code = responseJSON["code"]!
                let product_name = responseJSON["product_name"]!
                let brands = responseJSON["brands"]!
                let Comp1Mat = responseJSON["Comp1Mat"]!
                let Comp2Mat = responseJSON["Comp2Mat"]!
                let Comp3Mat = responseJSON["Comp3Mat"]!
                let Comp1Name = responseJSON["Comp1Name"]!
                let Comp2Name = responseJSON["Comp2Name"]!
                let Comp3Name = responseJSON["Comp3Name"]!

                
                print(code, brands, product_name, Comp1Mat, Comp2Mat, Comp3Mat, Comp1Name, Comp2Name, Comp3Name)
                
            }
        }

        task.resume()
    }

Answer 2

When you get a 500 error, that could be a malformed request. Or it could be a syntax error in the PHP code. Or it could be both.

In this case, it is likely the PHP syntax error, as there is a missing ) at the end of the prepare call. But it could also be the former (as the Swift code is not percent encoding the application/x-www-urlencoded request).

---

When you get 500 codes, look at the end of the server’s error_log (generally in the directory where your PHP files are located). In this case, I looked and it said:

[19-Jun-2021 13:45:00 America/Boise] PHP Parse error: syntax error, unexpected ';', expecting ')' in /.../insert.php on line 103

That is a syntax error in the PHP that must be fixed.

---

That having been said, there is a problem in the Swift code, too. It is manually building the body of the HTTP request. One should percent encode the values in the body of the request. There are libraries, like Alamofire, will automatically percent encode application/x-www-urlencoded requests (which this is). If writing one’s own Swift code to build the request, you want to percent encode them manually as outlined in HTTP Request in Swift with POST method.

Note, if (a) your request includes characters that need percent encoding (e.g., spaces or certain symbols); and (b) you fail to do so, then that, too, can result in 500 error from the web server. Again, the error_log file would tell you what went wrong, but percent-encoding the body of the URLRequest is important if the values might include any reserved characters.

---

That having been said, there are a few other observations:

1. Your code, as provided in the question, does not quite make sense. You are storing the result of the prepare call (which is a “statement”) into the variable called $sql. (As an aside, because it is a statement, naming that variable $stmt or $statement would make more sense.) You later supply that to query. But query is expecting a SQL string, not a “statement” as returned by prepare. You should, instead, just check the return values from prepare, bind_param and execute. Do not call query after having prepared, bound, an executed the statement.

2. The s vs i values in bind_param do not match up with the default values that you have given your five variables with the content of the $_POST. You might want to double check that.

3. Not directly related to the question at hand, but I would make a few suggestions. Namely, when writing a web service, like this, I might suggest suggest the following:

   • Set the HTTP status code (e.g. http_response_code(201) on successful insert, http_response_code(422) on insertion failure).

   • I would also return results as JSON (e.g. use PHP associative array for the results and then use json_encode to build the response to be echo’ed by the PHP), rather than text string. Then the client app can easily parse the responses.

   • I would set the Content-Type (e.g., header("Content-Type: application/json")) so that the client app knows how to parse the response.

4. A very minor/subtle observation: In the PHP, there is a space before the initial <?php. You don’t want any characters before the PHP starts. It can cause very subtle problems. (Hopefully it is just a typo introduced during the preparation of the question, but just a word of caution.)