Android Tutorial Connect Android to MySQL Database Tutorial

Status
Not open for further replies.
A new more powerful framework is now available: jRDC2.

This tutorial explains the basic concepts required for creating a connection between Android device and a remote server. In this case it is a MySQL database.
A tutorial for connecting to SQL Server is available here.

Android cannot connect directly to the database server (search for JdbcSQL). Therefore we need to create a simple web service that will pass the requests to the database and will return the response.

For this example I've created a new database that lists the countries population. The data was derived from a UN database.

The database includes a single table named "countries" with three columns:
mysql_1.png

PHP Script

The web service is made of a single PHP script running on the same server.
You have several options for the web service implementation. You can create several prepared statements which will be filled with parameters passed in the request.
Or you can take the complete query from the request and pass it to the database server.
There are high security risks with issuing queries received from an unknown user. If your service is public then you will probably want to choose the first option.
You should also make sure to correctly escape the parameters. See this php method: PHP: mysql_real_escape_string - Manual

In our example I've chosen the second option which takes the query and passes it to the database directly.
I've restricted the database user to SELECT queries (in MySQL configuration).

The PHP code:
PHP:
<?php

$databasehost = "localhost";
$databasename = "xxxx";
$databaseusername ="xxxx";
$databasepassword = "xxxx";

$con = mysqli_connect($databasehost,$databaseusername,$databasepassword, $databasename) or die(mysqli_error($con));
mysqli_set_charset ($con , "utf8");
$query = file_get_contents("php://input");
$sth = mysqli_query($con, $query);

if (mysqli_errno($con)) {
   header("HTTP/1.1 500 Internal Server Error");
   echo $query.'\n';
   echo mysqli_error($con);
}
else
{
   $rows = array();
   while($r = mysqli_fetch_assoc($sth)) {
     $rows[] = $r;
   }
   $res = json_encode($rows);
    echo $res;
    mysqli_free_result($sth);
}
mysqli_close($con);
?>
The script takes the query from the POST data and submits it to the database.
The result is then written in JSON format.

B4A code

Our application displays a list of countries. When the user presses on a country, its population is retrieved from the database and displayed.

upload_2016-8-3_13-4-25.png



The code sends the query and then when the result arrives the JSON is parsed and displayed.
Note that in this case the JSON object is made of an array of one or more maps.

Edit: Code updated to use OkHttpUtils2.
 

Attachments

  • MySQL.zip
    8 KB · Views: 17,449
Last edited:

omar madraba

New Member
Hi every one on this forum,
thanks a lot Erel for your publication.
but just would like to answer me where we should put the php code for the connection with the database mysql

Thnaks.
 

McJaegs

Member
Licensed User
Longtime User
Hey, so when I try to run my php in a browser from the server I get a 404 page not found error. Did I do something wrong?
 

mc73

Well-Known Member
Licensed User
Longtime User
Hey, so when I try to run my php in a browser from the server I get a 404 page not found error. Did I do something wrong?
I think that if you uploaded your php file via ftp to your website, you shouldn't get this type of error. Probably, if you'd open the script from a browser, you would get a '\nQuery was empty' error, which is ok, since you didn't set any parameters. These parameters should be set by your b4a app, as shown in Erel's mysql tutorial.
 

McJaegs

Member
Licensed User
Longtime User
Is it possible to use a PHP script to access a MS SQL database, or do you have to use ASP.net like in the other tutorial?
 

mc73

Well-Known Member
Licensed User
Longtime User
Because it isn't working for me. I get a java error.

In that case, I think you might want to post the part of the code that is generating the error. I am sure, there are members here who can help.
 

jothis

Active Member
Licensed User
I tried this code but I didn't get Country List.
In error log I got an error:
Error: org.apache.http.conn.HttpHostConnectException: Connection to http://localhost refused, StatusCode: -1

Can you tell me how I can fix this ?
 

tcpip

Member
Licensed User
Longtime User
ExecuteRemoteQuery

Hello,
I`m using mySQL and php to connect to a database from B4A. I have a code like this:

Sub button1_Click()
If txt1.Text = "XYZ" Then
ExecuteRemoteQuery1(".......",1)
End If
If txt2.Text = "QWE" Then
ExecuteRemoteQuery2("...",1)
End If
End Sub

The queries are executed, but the ResponseSuccess part executed when it reaches END SUB. Is there any way to full execute the ExecuteRemoteQuery1 and then the ExecuteRemoteQuery2 before END SUB????


thanks
 

Smee

Well-Known Member
Licensed User
Longtime User
There are high security risks with issuing queries received from an unknown user. If your service is public then you will probably want to choose the first option.
You should also make sure to correctly escape the parameters. See this php method: PHP: mysql_real_escape_string - Manual

If using the first option i would assume that a script is saved on the server and the parameters are passed from the device.

Are there any examples of this anywhere?
 

Smee

Well-Known Member
Licensed User
Longtime User
The PHP script is always saved on the server. It does require some PHP knowledge.
I recommend you to start with the script used in this example and once you get it working it shouldn't be too difficult to modify the script and hardcode the queries in the script.

Yes but i am looking to prevent the sql injection. As i understand it sending the password in the example would allow for sql injection. so i am trying to work out how to send the password and username so that it is escaped
 

Erel

B4X founder
Staff member
Licensed User
Longtime User
The password is not sent by the client in this case. The password and username are not the main issue here. You should use SSL (https) if you want to encrypt the connection.

The issue with this implementation is that it lets a malicious user to modify your program and send any query they want to your server.
The way to prevent it is by coding the queries in the PHP script and let the client to only send the required parameters.
 

Smee

Well-Known Member
Licensed User
Longtime User
Ahhhh

I think i understand now. I was always going to have the script on the server anyway. I have to write the server-side script in such a way as to prevent injection from outside attack. correct?

Thanks again Erel
 

trinitysj

Member
Licensed User
Longtime User
An error has occured in sub:
main_hc_responsesuccess (java line: 326)
org.json.JSONException: End of input at character 0 of
Continue?


this is the error it get.. either by doing it myself or downloading the example and using that.. it is the same each time.

how can this be fixed? it is a php or db issue?
 
Last edited:

Globalclown

Member
Licensed User
Longtime User
trinitysj:
sql: maybe you have not entered data into your database?
php: maybe you use php4 or php5/5.1, but JSON needs PHP5.2!
 
Last edited:
Status
Not open for further replies.
Top