September 13, 2012 By jmcmanuswebroot

A Primer for Secure Coding in PHP and MySQL

PHP is an incredibly popular language for creating dynamic web applications — websites such as Facebook are built on it. This can be attributed to many reasons; it is easy to learn, easy to install and does not require the user to compile code. An unfortunate side effect of the ease of development with PHP is a tendency to ignore security during the development process.

In this post I will discuss some of the ways to make your PHP apps more secure. I will go through creating a PHP web app that connects to a MySQL back end database. The application will be a simple address book. The approach I will take is one of layered security.  There is no sure fire quick method of blocking all attacks, but using the layered security approach we severely limit our exposure.

Two of the attacks we will mitigate are SQL Injection (SQLi) and Cross Site Scripting (XSS). Both of these attacks are performed by exploiting applications that do not properly handle user input. XSS occurs when an application sends the users browser back input that has not been checked for code. If an attacker were to enter something similar to the text below in a form field, it would cause the browser to execute the code and create a pop up on the screen displaying the text “XSS”.

”><script>alert(‘XSS’)</script>

Similarly SQLi is caused by an attacker running unplanned SQL code against the database. An example would be if an attacker were to enter the following in a form, the browser would display all the data in the user table:

;select * from users;

I will use PHP built-in methods such as htmlspecialchars(), mysqli_real_escape_strings() and use prepared statements to help prevent the attacks mentioned above.

In our sample training contact list app, the database will store the following fields:

●First Name

●Last Name

●email address

Image

To create the database, I ran the following code as a privileged user, it is attached at the bottom of this post as createdb.sql.

 mysql -uroot -p -h 127.0.0.1 mysql

CREATE DATABASE `contactsSchema` ;
USE contactsSchema;
CREATE TABLE `contactsTable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `first` VARCHAR(40) DEFAULT NULL,
    `last` VARCHAR(64) DEFAULT NULL,
    `email` VARCHAR(256) DEFAULT NULL
    PRIMARY KEY (`id`)
) ENGINE=InnoDB; 
CREATE USER 'contactsUser'@'localhost' identified by 'superSecretPassword';
GRANT SELECT, INSERT, UPDATE, DELETE
    ON contactsSchema.contactsTable TO 'contactsUser'@'localhost';

INSERT INTO contactsTable (id, first, last, email)
values(null, 'Alexander', 'Bell', 'alexander@bell.com');

For the application, I will forego the authenticated login functionality, that is a bit out of scope for a primer, I’ll cover that in another post.

In the PHP page, which is attached at the bottom as index.php, I will highlight some of PHP code that makes this contacts app more secure.

1. strip_tags()

#User passed in vars
isset ( $_REQUEST['i'] ) ? $i = strip_tags($_REQUEST['i']) : $i = "";
isset ( $_REQUEST['s'] ) ? $s = strip_tags($_REQUEST['s']) : $s = "";

I am only allowing variables from the user that I am specifying. Next I am running strip_tags() on them to remove any html tags in the text. This however does not get all extra characters.

2. mysqli_real_escape_string()

$first=mysqli_real_escape_string($db, $first);

In the above code snippet, the mysqli_real_escape_string() method is used to escape any special characters. This is used as the variable is provided by the user. Anything the user enters should be sanitized. However mysqli_real_escape_string() does not protect against all threats.

3. Prepared Statement

if ($stmt = mysqli_prepare($db,
 "INSERT INTO contactsTable SET first=?, last=?, email=?, id=''")) {

               mysqli_stmt_bind_param($stmt, "sss", $first, $last, $email);
               mysqli_stmt_execute($stmt);
               mysqli_stmt_close($stmt);
}

In this code we are using prepared statements to limit the functionality of the query. In this example, I am only passing in variables to execute in this query, it prevents nested queries, which are a common SQLi attack.


4. htmlspecialchars()

function displayResults($first, $last, $email) {

       echo "<tr> <td>" . htmlspecialchars($first)  . "</td>";
       echo "<td>" . htmlspecialchars($last)  . "</td>";
       echo "<td>" . htmlspecialchars($email)  . "</td></tr>";
}

In the above example, I am disabling code that may be stored in the database or passed in by the end user from being rendered as executable html in the browser. The command htmlspecialchars() replaces characters like < with markup equivalent such as &LT;.

Conclusion

In this example application, I have applied multiple layers of security to prevent attacks such as cross site scripting and SQL injection. Clearly this is a simple application, but the code is reusable and you can build on it when you create your own applications.

Code Examples Used in Writeup:

createdb.sql

CREATE DATABASE `contactsSchema` ;
USE contactsSchema;
CREATE TABLE `contactsTable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `first` VARCHAR(40) DEFAULT NULL,
    `last` VARCHAR(64) DEFAULT NULL,
    `email` VARCHAR(256) DEFAULT NULL
    PRIMARY KEY (`id`)
) ENGINE=InnoDB; 
CREATE USER 'contactsUser'@'localhost' identified by 'superSecretPassword';
GRANT SELECT, INSERT, UPDATE, DELETE
    ON contactsSchema.contactsTable TO 'contactsUser'@'localhost';

INSERT INTO contactsTable (id, first, last, email)
values(null, 'Alexander', 'Bell', 'alexander@bell.com');

Index.php

<?php
//file: index.php 
//purpose: example contact list
//version: 1.0 
//date: 2012/08/30
#User passed in var run strip tags on input
isset ( $_REQUEST['i'] ) ? $i = strip_tags($_REQUEST['i']) : $i = "";
isset ( $_REQUEST['s'] ) ? $s = strip_tags($_REQUEST['s']) : $s = "";
isset ( $_REQUEST['st'] ) ? $st = strip_tags($_REQUEST['st']) : $st = "";
isset ( $_REQUEST['first'] ) ? $first = strip_tags($_REQUEST['first']) : $first = "";
isset ( $_REQUEST['last'] ) ? $last = strip_tags($_REQUEST['last']) : $last = "";
isset ( $_REQUEST['email'] ) ? $email = strip_tags($_REQUEST['email']) : $email = "";

#Database Connection 
function connectDB(&$db){
 $db_host='localhost';
 $db_user='contactsUser';
 $db_pass='superSecretPassword';
 $db_name='contactsSchema';
 $db = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
 if (mysqli_connect_errno()) {
 print "<br> Houston we have a problem! <br>
 There seems to be an error connecting to the MySQL Database. 
 <br> The error we hit was: <br> " . htmlspecialchars(mysqli_connect_error()) .
 "<br> error code " . htmlspecialchars(mysql_errno());
 exit;
 }
}
//Add new entries to DB
function addContact($db, $first, $last, $email) { 
 $first=mysqli_real_escape_string($db, $first);
 $last=mysqli_real_escape_string($db, $last);
 $email=mysqli_real_escape_string($db, $email);
 if ($stmt = mysqli_prepare($db, "INSERT INTO contactsTable SET first=?, last=?, email=?, id=''")) {
 mysqli_stmt_bind_param($stmt, "sss", $first, $last, $email); 
 mysqli_stmt_execute($stmt); 
 mysqli_stmt_close($stmt);
 } else {
 echo "Error Adding Contact";
 }
}
//Table Header 
function resultTableHeader() {
 echo "<table width=60%>
 <tr> <td bgcolor=#D8D8D8 width=30%> <b><u> First </b> </u> </td>
 <td bgcolor=#D8D8D8 width=30%> <b><u> Last </b> </u> </td> 
 <td bgcolor=#D8D8D8 width=40%> <b><u> eMail </b> </u> </td> </tr>";
}
//Results Display
function displayResults($first, $last, $email) {
 echo "<tr> <td>" . htmlspecialchars($first) . "</td>";
 echo "<td>" . htmlspecialchars($last) . "</td>";
 echo "<td>" . htmlspecialchars($email) . "</td></tr>";
}
//Coonect to the DB to begin
connectDB($db);
echo "
<html>
<head>
<title> Contacts </title> 
</head>
<body> 
<center>
<h3> Contact List </h3>
<table width=60%> 
<tr>
 <td bgcolor=#D8D8D8 width=30%> <a href=index.php?s=1>Add New </a> </td>
 <td bgcolor=#D8D8D8 width=30%> <a href=index.php?s=2>Search</a> </td>
 <td bgcolor=#D8D8D8 width=40%> <a href=index.php>Back</a> </td>
</tr>
</table>
";
if ($s == Null) { //If no options, just query the whole table
 resultTableHeader();
 $query="SELECT first, last, email FROM contactsTable"; 
 $result=mysqli_query($db, $query); 
 while($row=mysqli_fetch_row($result)) {
 displayResults($row[0], $row[1], $row[2]);
 }
 echo "</table>";
}elseif ($s == 1) { //Add new entry
 if ($first == Null) { //If we don't have user input yet
 echo "<form method=post action="index.php"> ";
 resultTableHeader();
 echo " 
 <td> <input type=text name="first" value=""></input> </td> 
 <td> <input type=text name="last" value=""></input> </td> 
 <td> <input type=text name="email" value=""></input> </td> 
 <tr> <td colspan=3 align=center><input type=hidden name="s" value="1">
 <input type=submit name="Submit" value="Submit"> </td </tr>
 </table>";

 } elseif ($first != Null) {
 addContact($db, $first, $last, $email) ;
 echo "Added new Entry <br>" . 
 htmlspecialchars($first) . "<br>" .
 htmlspecialchars($last) . "<br>" . 
 htmlspecialchars($email) . "<br>";
 }
}elseif( $s == 2) {
 if ($st == Null) { 
 echo "Enter Search Term: 
 <form method=post action="index.php">
 <input type=text name="st" value=""> 
 <input type=hidden name="s" value="2">
 <input type=submit value="Submit" name="Submit"> 
 </form>" ;
 } else { 
 $st=mysqli_real_escape_string($db, $st);
 resultTableHeader();
 if ($stmt = mysqli_prepare($db, "SELECT first, last, email FROM contactsTable WHERE 
 first LIKE CONCAT('%', ? ,'%') or
 last LIKE CONCAT('%', ? ,'%') or
 email LIKE CONCAT('%', ? ,'%')")){
 mysqli_stmt_bind_param($stmt, "sss", $st, $st, $st); 
 mysqli_stmt_execute($stmt); 
 mysqli_stmt_bind_result($stmt, $first, $last, $email);
 while(mysqli_stmt_fetch($stmt)) {
 displayResults($first, $last, $email);
 }
 mysqli_stmt_close($stmt);
 }
 echo "</table> ";
 } 
}
echo " 
 </center>
 </body>
</html>";
?>
Share Button
0 comments
true