MySQL and PHP

We use a MySQL database to store data needed for the WebRTC communication. SQL stands for Structured Query Language, a programming language specifically designed for managing databases and their data.

PHP is another programming language. The WebRTC app uses several short PHP programs, run on the web server, to store and retrieve data from the database. This data is exchanged between the two participants in the WebRTC conversation.

Database structure

A database comprises tables that contain columns and rows of data, similar to a spreadsheet. The database used for the WebRTC app is very simple. It has just one table, named Calls. The table has three columns: callNo (to identify a call), callerSdp (the caller's Session Description Protocol or sdp), and responderSdp (the sdp of the responder).

To create the database, see the menu options at the web site of your hosting company. Then to create the database tables, your web hosting site probably provides you with an online tool called phpMyAdmin. Alternatively, you can run the SQL code below.

SQL code

[toggle comments]

CREATE TABLE Calls (callNo mediumint unsigned, callerSdp text, responderSdp text, PRIMARY KEY (callNo))

It will come as no surprise that this SQL CREATE TABLE statement creates a table named Calls. Its columns are given in brackets. The first column is called callNo and can contain only unsigned (positive) numbers of type mediumint (a medium-sized integer, which can go up to 16777215, plenty for our needs). The other columns, callerSdp and responderSdp, can contain only text (strings of characters). The PRIMARY KEY keyword makes its argument, the callNo column, the primary key of the table. This creates an index for the column, making it faster to find the row with a specific value in that column without having to search sequentially through the table.

Listings

Each of the following PHP programs is called by the webrtc.js JavaScript program as an XMLHttpRequest.

connect.php

The first program creates a connection to the database. It is used by every other php program. The program listing isn't exactly as it appears in the actual program: where you see dbservername, databasename, dbusername and password you should replace these with the correct values for your database.

<?php

All PHP programs must start with this line so the computer knows they are written in PHP

try {

In common with most programming languages, PHP allows you to detect errors when a program runs. The try statement tries to execute the code within the curly brackets, but if it fails, it catches the error. This is vital in this case as otherwise the database name and password could be displayed to the user, an obvious security risk.

  $db = new PDO('mysql:host=dbservername;dbname=databasename;charset=utf8', 'dbusername', 'password', array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

This is a complicated statement, so we'll break it down. Firstly, all variables in PHP have a $ prefix. So the $db variable is assigned to a newly created PDO (PHP Data Object). Using PDOs to access and maintain database records is extremely secure, so we use it exclusively. Previously used methods were prone to injection attacks, meaning someone could supply 'data' that was in fact SQL code. This code could hack into the database to reveal its contents or change its records. This is impossible with the prepared statements we use in these PHP scripts, as the data and SQL code are sent separately. The arguments we supply to the PDO constructor give details of the database we want to connect to: the type of database (mysql), the hostname, the database name, and the password. When you enter or edit the program, you should fill these in with the details you can get from your web host. The last argument is an array of attributes, which exist as key => value pairs similar to those we saw with associative arrays in JavaScript. PDO::ATTR_EMULATE_PREPARES => false sets the PDO::ATTR_EMULATE_PREPARES key to the value of false, which turns off emulation of prepared statements. This prevents possible SQL injection attacks if the database doesn't support prepared statements and so requires their faking. The second attribute puts PDO into exception mode; in the event of an error, an exception is thrown.

} catch(PDOException $e) {

If there is a problem connecting to the database, a PDO exception is triggered, which we catch in order to exit from the program cleanly. The $e is a dummy argument to which an error message is sent, but in fact we don't want to display it.

  exit;
}

The PHP exit function quits the program. The JavaScript XMLHttpRequest that called the PHP program will receive no return data and so will know to log an error message and abort the program.

?>

Every php program must end with this line


storeSdp.php

This program is called by the postLocalData() function in the JavaScript program. It stores the local sdp of a user. For the caller, it creates a random call number for the call and creates a row in the Calls table which stores the callNo and the callerSdp. For the responder, who provides the call number that is in the query portion of their web address (?callNo=), it updates the Calls table to add their responderSdp.

<?php
require 'connect.php';

The PHP require statement runs the file specified in quotes, in this case the connect.php program, which creates a database connection called $db for the rest of the program to use.

$data = explode('~', file_get_contents('php://input'), 2);

When data is posted to a PHP program using the POST http method, it is placed in the php://input file, the contents of which can be read into a string with the PHP file_get_contents function. The data we're passing from the JavaScript to this php file is a string comprising the callNo (if this is the responder, not the caller) followed by the localSdp, separated by a tilde (~) character. We separate this string into its constituents using the explode method, which will return a numeric array, the first element of which is the callNo and the second the localSdp.

if ($data[0]) { //only the responder will have a valid callNo

$data["callNo"] returns true if it is non-zero, which will be the case if this is the responder, who will have gleaned the callNo from the query portion of the web address.

  $stmt = $db->prepare("UPDATE Calls SET responderSdp=? WHERE callNo=?");

$db is our new PDO object. To access the properties and methods of an object in PHP, we use the arrow operator -> (also called the object operator). In this line, we prepare a statement using the prepare method of the $db database object, which takes as a string argument the SQL code we want to run. The SQL UPDATE statement updates the row in the Calls table where the callerNo equals the one supplied, setting the responderSdpIce to the given value. The use of a question mark (?) to represent each data value demonstrates how PDO prevents injection attacks. The data is supplied separately when the statement object ($stmt) is executed in the following line. This is why PDO is so secure: code and data are given separately, so data could never be run as code.

  $stmt->execute(array($data[1], $data[0]));
  }

The execute method of the statement object is passed an array of all the data items, one for each question mark included in the prepare method above. Data is allocated to the question marks in the order they occur. The first question mark in the prepared statement is given the value of $data[1], which is the sdp, the second the value of $data[0], which is the callNo.

else {

If this is the caller.

  $stmt = $db->prepare("INSERT IGNORE INTO Calls VALUES(?, ?, '')");

The SQL INSERT statement inserts a new record or row into the Calls table. The values we want to put in each column of the new row are specified as arguments of the VALUES keyword. Every column must be assigned a value. Here, the first two columns (callNo and callerSdp) are given ? values to be completed by the execute method below. The last column, responderSdp, is given an empty string value.

  do

A do...while loop does a particular operation or block of operations while the specified condition is true. This loop generates a random number to use as the Call No until it finds one that hasn't already been used in the database.

    $callNo = rand(10000,16777215);

The rand function returns a random number between the two numbers given as arguments, here 10000 and 16777215. This gives us a random call number, so that only the intended responder can participate in the call instigated by the caller. For security, we want the random number to contain at least five digits.

  while (!$stmt->execute(array($callNo, $data[1])));

Executes the prepared statement above, putting the random number in the first column (callNo) and $data[1] in the callerSdp column. The callNo column is the primary key of the table, so that it is indexed and accessed more quickly. The primary key must be unique, so if the callNo already exists in the table, the insert is rejected. To prevent an error being generated, we use the IGNORE keyword in the INSERT statement, which causes the execute method to return a value of 0 instead of an error. In PHP (and JavaScript), 0 evaluates to false, so by placing the NOT (!) sign before $stmt, we are saying "do...while zero is returned": the loop will repeat until a callNo is found that isn't currently in the table.

  echo $callNo;
  }
?>

The echo statement would normally output its argument to the screen, but since this program was called by webrtc.js using an XMLHttpRequest, it is returned in the response property of the XMLHttpRequest object, which itself is returned to the receiveCallNo callback function in webrtc.js. We use the echo statement to return the callNo to the function.


fetchSdp.php

This program is called as an XMLHttpRequest by the getRemoteData() function in the JavaScript program, with the callNo as its sole argument. It returns the peer's column of the Calls table: the responderSdp if the caller is requesting or the callerSdp if the responder is requesting.

<?php
require 'connect.php';
$callNo = $_GET["callNo"];

This program is called by webrtc.js as a GET request, where the arguments are appended to the url as a query string, starting with a ? and containing a series of key=value terms separated by the ampersand (&) character. The arguments from a GET request are delivered to the PHP program as elements in the $_GET associative array, so $_GET["callNo"] will be the callNo.

if (is_numeric($callNo)) {

Rather than using a prepared statement, we can just test whether the callNo is numeric using the PHP is_numeric function. This returns true if its argument is numeric. This way, SQL injection attacks, which rely on the insertion of malicious strings purporting to be data, are blocked.

  if ($callNo > 0) // caller has +ve callNo

We can identify the caller as it has a positive callNo, whereas the responder is given a negative callNo to differentiate it.

    $stmt = $db->query("SELECT responderSdp FROM Calls WHERE callNo=$callNo");

The PDO query method of the $db database object executes an SQL statement without preparation, returning a statement object which we process using the fetchColumn method below. The SQL SELECT statement we use here returns the responderSdp column in any row FROM the Calls table WHERE the callNo column equals the value of the $callNo variable. PHP automatically evaluates variable names within a string, so WHERE callNo=$callNo will evaluate $callNo to its actual value. This only works for strings delimited with double quotes (") not single quotes (').

  else

This is the responder.

    $stmt = $db->query("SELECT callerSdp FROM Calls WHERE callNo=-$callNo");

Returns the string in the callerSdp column from the row where the callNo column equals -$callNo. Remember that the callNo for the responder is the negative of the actual callNo, so we need to negate it to obtain the original.

  if (($sdp = $stmt->fetchColumn()) != '') echo $sdp;
  }
?>

The fetchColumn method of the statement object returns the contents of the selected column (for example, callerSdp) of the first row returned by the select statement (in this case there will be only one). This value is assigned to the $sdp variable. Finally, although we perform an assignment (using an equals sign), the statement will return true or false depending on the value assigned (after the equals sign). In this case, the fetchColumn method only returns true if there were rows satisfying our SELECT criteria. Only if it returns true do we use an echo statement to return the result.


deleteCall.php

This program deletes the Calls table row that has the given callNo. It is instigated under two circumstances by the sendBeacon javascript function in the webrtc.js program: if there is an error, or when the app window is closed. The sendBeacon function sends data (the callNo) as a POST request, which is therefore stored in the php://input folder as in the storeSdp program above.

<?php
$callNo = file_get_contents('php://input');
if (is_numeric($callNo)) {
  require 'connect.php';
  $db->exec("DELETE FROM Calls WHERE callNo=$callNo);
  }
?>

The SQL DELETE statement simply deletes from the Calls table the row that has the given callNo.