Andrew Nguyen Andrew Nguyen - 27 days ago 21
MySQL Question

UPDATE SQL Database through Async Task on Android with PHP using POST method

I am posting String data from an android app I am developing to an SQL database. (MAMP)-Local server

I am able to 'echo' all of the POST data from the PHP to the android app, so I am fairly confident that the java is solid.

Example in php "echo $username;" I get andrewnguyen22 on my android emulator.
It works for all of my php variables, so i know that the android POST is working fine.

When I hardcode the $POST information and refresh the php page, the code works fine...
But heres the thing that baffles me...

The sql database will not update when using the android application, instead it echos the username. This means that no rows were affected by the php. Can anyone see my error?

Below I have posted my PHP and my Java.

PHP CODE:

require "conn.php";
$username = $_POST["username"];
$fullName = $_POST["fullName"];
$age =$_POST["age"];
$bio =$_POST["bio"];
$year =$_POST["year"];
$gender =$_POST["gender"];
$location =$_POST["location"];
$sql = "UPDATE user_info SET fullName ='$fullName', bio='$bio',age='$age', gender='$gender', location='$location', year='$year' WHERE username = '$username' ";
$do = mysqli_query($conn, $sql);
if(mysqli_affected_rows($conn) >0){
echo 0;
}
else{
echo $username;
}


Android App Code

import android.app.AlertDialog;
import android.content.Context;
import android.os.AsyncTask;

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLEncoder;

/**
* Created by andrewnguyen on 10/23/16.
*/

public class EditProfileBackgroundTask extends AsyncTask {
Context ctx;
AlertDialog alertDialog;
public EditProfileBackgroundTask(Context ctx) {
this.ctx = ctx;
}
@Override
protected void onPreExecute() {
alertDialog = new AlertDialog.Builder(ctx).create();

super.onPreExecute();
}

@Override
protected String doInBackground(String... params) {
String profile_url = "http://10.0.2.2:8888/profile.php";
String method = params[0];
if(method.equals("profile")){
Global global = new Global();
String fullName = params[1];
String age = params[2];
String bio = params[3];
String gender = params[4];
String location = params[5];
String year = params[6];
String username = params[7];

try {
URL url = new URL(profile_url);
HttpURLConnection httpURLConnection = (HttpURLConnection) url.openConnection();
httpURLConnection.setRequestMethod("POST");
httpURLConnection.setDoOutput(true);
httpURLConnection.setDoInput(true);
OutputStream OS = httpURLConnection.getOutputStream();
BufferedWriter bufferedWriter = new BufferedWriter(new OutputStreamWriter(OS, "UTF-8"));
String data = URLEncoder.encode("username", "UTF-8") + "=" +URLEncoder.encode(username, "UTF-8") +"&"+
URLEncoder.encode("fullName", "UTF-8") + "=" +URLEncoder.encode(fullName, "UTF-8") +"&"+
URLEncoder.encode("age", "UTF-8") + "=" +URLEncoder.encode(age, "UTF-8") +"&"+
URLEncoder.encode("bio", "UTF-8") + "=" +URLEncoder.encode(bio, "UTF-8") +"&"+
URLEncoder.encode("gender", "UTF-8") + "=" +URLEncoder.encode(gender, "UTF-8") +"&"+
URLEncoder.encode("year", "UTF-8") + "=" +URLEncoder.encode(year, "UTF-8") +"&"+
URLEncoder.encode("location", "UTF-8") + "=" +URLEncoder.encode(location, "UTF-8");

bufferedWriter.write(data);
bufferedWriter.flush();
bufferedWriter.close();
OS.close();
InputStream IS = httpURLConnection.getInputStream();
BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(IS, "iso-8859-1"));
String response = "";
String line = "";
while ((line = bufferedReader.readLine())!=null){
response+=line;
}

bufferedReader.close();
IS.close();
httpURLConnection.disconnect();
return response;
} catch (MalformedURLException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}

}

return "Create Profile Failure";
}


@Override
protected void onProgressUpdate(Void... values) {
super.onProgressUpdate(values);
}

@Override
protected void onPostExecute(String result) {
if (result .equals("0")) {
super.onPostExecute(result);

alertDialog.setMessage("YAY");
alertDialog.show();
}
else if (result .equals("1")) {
alertDialog.setMessage("NO");
alertDialog.show();

}
else{
alertDialog.setMessage(result);
alertDialog.show();//THIS IS WHERE I CAN SEE THE RESULT andrewnguyen22(as seen in php echo $username)
}
}
}


It displays my username correctly on my android emulator... (through the alert dialog if else on post-execute)

Screenshot of result on emulator

Answer

Since your not escaping your data, a simple ' in any of the posted fields will mess up your concatenated SQL statement. The same goes if a value ends with \. There are probably even more scenarios...

NOTE: Since you already stated that you don't care about security for this script, I'm not gonna preach about the importance of using Prepared Statements. But if anyone else reads this later, use Prepared Statements instead.

Use mysqli_real_escape_string() to escape the inputs, to be sure that a simple character won't mess your statement up.

$username = mysqli_real_escape_string($conn, $_POST["username"]);
$fullName = mysqli_real_escape_string($conn, $_POST["fullName"]);
$age = mysqli_real_escape_string($conn, $_POST["age"]);
$bio = mysqli_real_escape_string($conn, $_POST["bio"]);
// ...do the same for the rest...
Comments