In the previous post, Writing an Android application – investigating Xamarin Forms, I looked at how to get a basic Android application running on my phone. Most applications’ purpose are to represent data to the user in some way and in most cases this data is stored in a database. This post explores the basics of how to safely retrieve data from a database and use it in an application.

Background

An electronic database is an effective way to store large amount of data and retrieve relevant information quickly. Most websites and mobile phone applications make use of a database of some sort. The database can either be locally stored or hosted elsewhere. Different types of databases are available, but this tutorial will focus on using a MySQL database.

MySQL is a relational database and uses SQL, Structured Query Language, for querying and maintaining the database. When we want to retrieve data we will submit a SQL query using PHP.

Only two paragraphs in and we are already talking about SQL and PHP. What have all of that got to do with our application and our database. Why can my application not just access a database directly without making use of SQL or PHP or anything else. The answer is security.

Many applications for Android and iOS requires a central database, a common source of information everyone using the application can access. This necessitate a central database hosted on a web server somewhere in the world, available for all the app users to access. The problem is a central database is also accessible by malicious actors that want to mess up your data for a laugh or in more serious cases, either steal your data or prevent your application or website from working. To prevent this the database is protected, with access only allowed through a predefined interface, written in PHP using SQL.

Getting data from the database

The architecture described in the previous paragraph forms part of the REST (representational state transfer) software architecture. When learning how to get data to my mobile phone application I did not know this. If you are new to this as I am, it is worth to do a search and learn more about the architecture.

So how does it work? When your application need data from the database it contacts a webserver using a PHP service. The PHP service takes the application’s request and retrieve the data from the database, package the data, usually a JSON response, and send it back to the application.

REST API for Android is well explained at phppot.com (also the image source)

Why is the PHP interface needed? For security the database is setup to only accept logins and queries from the local machine. The webserver hosting PHP is local and can thus access the database. Secondly, the SQL queries can be carefully constructed in PHP and therefor can limit what external users can retrieve, or insert, into the database.

Let’s go through a simple example. In this example application we are just retrieving how many entries there are and show that to the user.

Example 1 – How many database entries are there

SQL

The SQL query to get the amount of entries in the database is

SELECT COUNT(*) FROM databaseName

PHP

The next step is to write the PHP that will do the query in the previous section. This PHP file will live on the webserver, the same server as the database, and will be what our application will interface with.

The first step is for the PHP script to open a connection to the database and close the connection once we are done. In my case the two functions described below have been saved in a separate PHP file that gets called by the functions using them, but you can put this all in the same file if you want.

The following PHP code will open a connection with function OpenCon() and close the connection with function CloseCon(). You will need to replace the server name, user name, password and database with that of your database.

<?php
function OpenCon()
{
    $servername = "database.domainHostingYourDB.com";
    $username = "dataBaseUserName";
    $password = "PasswordForDataBaseUserName";
    $dbname = "DatabaseWeWantToAccess";

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

function CloseCon($conn)
{
    $conn -> close();
}
?>

We can now open and close a connection from PHP to the database. Time to do our query.

<?php
//Return number of rows in database

include 'db_connection.php';
$conn = OpenCon();

$sql = "SELECT COUNT(*) FROM databaseName";
$result = $conn->query($sql);

print $result->fetch_assoc()['COUNT(*)'];

CloseCon($conn);
?>

In the query PHP file above we

Include the PHP that will handle the connection to the database
Open the connection
Construct the SQL query
Contact the database with the query
Print the result from the query
And close the connection

PHP done! We can upload this to our webserver and test it immediately by navigating our browser to our webserver and the PHP file we just uploaded, for instance http://yourDomain.com/HowManyRowsInMyDB.php.

Getting the data in the App

Now that we have the API in place, to retrieve the number of rows in our database we need to get our application to call it and display the results. In my example I used the Xamarin Forms framework to develop an app (as described here) coding in C#.

In C# we will create a HTTP client to contact the webserver, call the PHP page and read the response.

HttpClient http = new HttpClient();
string DbInfoPHP = "http://yourDomain.com/HowManyRowsInMyDB.php";
string response = string.Empty;

HttpResponseMessage result = await http.GetAsync(DbInfoPHP);

if (result.IsSuccessStatusCode)
{
    response = await result.Content.ReadAsStringAsync();
}

Response will now contain the number of rows in the database. We can use this to update a label in our Xamarin Forms page which has the identifier ProductAmountInfoBar:

ProductAmountInfoBar.Text = response + " rows in database";

Example 2 – Using JSON

Let’s do something a little bit more complicated. In the previous example we have not send any specific request from the application to the server or received a JSON response back. This example will do both.

In this example we want to retrieve the rows in the database where text in a column matches a word the user has entered in a search bar. As before let’s start with the actual SQL query.

SQL

In this example the database column containing the text we are searching for is called “keywords”. The SQL to search for entries in the database where this column contains the word “HelloWorld” is:

SELECT * FROM `databaseName` WHERE `keywords` LIKE 'HelloWorld'

This will work fine but will only return exact matches. We are interested in all rows where column keywords contain any entry of “HelloWorld”. For instance “HelloWorld and goodbye”, should return a positive results. The following SQL adds the necessary SQL wildcards to return a positive results.

SELECT * FROM `databaseName` WHERE `keywords` LIKE '%HelloWorld%'

Time to write the PHP that will receive the search term from the application and return the results in the JSON format.

JSON

But first, what is JSON? JSON stands for JavaScript Object Notation and is a lightweight format for storing and transporting data.

PHP

Unlike the previous example we expect to receive some information from the webpage or app that will call the PHP we are writing. Information from the application is posted, requesting the webserver to accept the enclosed data. The enclosed data has an associated identifier and the first thing we want to do when our PHP file gets a request is to see if the data enclosed contains the expected identifier. For our example, let’s call this identifier keyWordEntered. To check if we have the identifier present we use isset()

<?php

//Check keyWordEntered identifier has been set
if (isset($_REQUEST['keyWordEntered']))
{
    //Run rest of PHP
}
?>

Preventing SQL injection

The next step we have to take is to ensure that whoever is calling our PHP and sending it information is not trying to do something to our database that we do not want done.

How is this possible? Instead of just sending the search word, a malicious user can instead send a string containing additional SQL. Let’s take the SQL string we intend to use for this example. Instead of the fixed string “HelloWorld” it has been updated below to search for the content of variable $keyWordSent instead.

SELECT * FROM `databaseName` WHERE `keywords` LIKE '%". $keyWordSent ."%'

Now if $keyWordSent contains something like “‘ OR ‘keywords’ LIKE ‘%blablabla'” the resulting SQL query change to the following which will return the content of the entire database.

SELECT * FROM `databaseName` WHERE `keywords` LIKE '%' OR 'keywords' LIKE '%blablabla%'

This malicious use of PHP is called SQL injection and is one of the top web application vulnerabilities. Not only can the attack be used to steal database information but can also be used to insert unwanted data to a database or even remove the database completely.

To prevent SQL injection the MySQL Improved Extension added functions to help, in this case real_escape_string(). This function makes data safe before sending a query to a MySQL database by prepending backslashes to special characters. By prepending special characters MySQL will, as an example, interpret a % not as a wildcard anymore but just as a normal ASCII character.

Now let’s update our PHP to include this check:

<?php

//Check keyWordEntered identifier has been set
if (isset($_REQUEST['keyWordEntered']))
{
    //Open database connection
    include 'db_connection.php';
    $conn = OpenCon();

    $keyWordSent= $conn->real_escape_string($_REQUEST['keyWordEntered']);

    //Close database connection
    CloseCon($conn);
}
?>

Completing PHP file

Let’s get this PHP done. The next step is to do our SQL query:

<?php

//Check keyWordEntered identifier has been set
if (isset($_REQUEST['keyWordEntered']))
{
    //Open database connection
    include 'db_connection.php';
    $conn = OpenCon();

    $keyWordSent= $conn->real_escape_string($_REQUEST['keyWordEntered']);

    $sql = "SELECT * FROM databaseName WHERE keywords LIKE '%" . $keyWordSent . "%'";

    $result = $conn->query($sql);

    //Close database connection
    CloseCon($conn);
}
?>

We now have the results from the query. Time to package it up in JSON and send it back to our application. Let’s add that to our PHP:

<?php

//Check keyWordEntered identifier has been set
if (isset($_REQUEST['keyWordEntered']))
{
    //Open database connection
    include 'db_connection.php';
    $conn = OpenCon();

    $keyWordSent= $conn->real_escape_string($_REQUEST['keyWordEntered']);

    $sql = "SELECT * FROM databaseName WHERE keywords LIKE '%" . $keyWordSent . "%'";

    $result = $conn->query($sql);
    
    //Close database connection
    CloseCon($conn);

    //Create JSON string to return
    $rows = array();
    while($r = $result->fetch_assoc())
    {
        $rows[] = $r;
    }
    $searchResultJSON = json_encode($rows); 

    print $searchResultJSON;
}
?>

And that is it. Our application will now receive a JSON string when a valid search word gets posted to the PHP file. The last step is to send the request in the C# file and retrieve the JSON response.

Getting the data in the App

As before we need to create a HTTP client in our C# file. However, instead of just calling the PHP on the webserver we now need to post data to the webpage. First thing first, let’s get the HTTP client setup:

HttpClient http = new HttpClient();
string DbSearchPHP = "http://yourDomain.com/SearchMyDB.php";
string response = string.Empty;

Next we need to specify what data we will post. In the code below we create a dictionary where the entry with identifier “keyWordEntered” has the value of variable “searchValue”.

var postDictionary = new Dictionary<string, string> { { "keyWordEntered", searchValue } };

We can now go ahead and post to the PHP, sending the data contained in the variable postDictionary.

HttpResponseMessage result = await http.PostAsync(DbSearchPHP, new FormUrlEncodedContent(postDictionary));
if (result.IsSuccessStatusCode)
{
response = await result.Content.ReadAsStringAsync();
}

In the next bit of code we have a function called ShowResult(). In the app this just updates a Xamarin Form page with the number of results found. The response from the HTTP post is parsed and converted from a string array to a list.

JArray resultParsed = JArray.Parse(response);

//If no response or no results show "no results found"
if (resultParsed.Count() == 0)
{
    ShowResult("No results found");
}
else
{
    SearchResults = new List<DBContentType>();
    SearchResults = JsonConvert.DeserializeObject<List<DBContentType>>(response);

    if (resultParsed.Count() == 1)
    {
        ShowResult("Found 1 result");
    }
    else
    {
        ShowResult("Found " + resultParsed.Count().ToString() + " results");
    }
}

One comment for the code above, for the JsonConvert function to deserialise the JSON string to a list of type “DBContentType”, the types content need to match the expected return JSON data exactly.

With that the returned data is available as a list that can be used as you wish in your C# application.

End

And that is it, data has been requested and handled from a C# mobile application using a PHP interface to a MySQL database. Next we will look at how to submit data from the mobile application safely to the database.

karooza.net

Covering electronics and coding for the hobbyist that wants to build everything

How to access a database from an App

Background

Getting data from the database

Example 1 – How many database entries are there

SQL

PHP

Getting the data in the App

Example 2 – Using JSON

SQL

JSON

PHP

Preventing SQL injection

Completing PHP file

Getting the data in the App

End

Related

Leave a Reply Cancel reply