Anybody that codes a script that accepts user input and inserts data into a sql database must make sure the database’s integrity is protected. I have read book that teach PHP that don’t even address this necessary, critical subject. Imagine the valuable data in your databases is butchered or even wiped out completely. Is your database safe from SQL injection attacks?

To begin with, let’s define a SQL injection attack. What is SQL injection? SQL injection attack occurs when an attacker exploits an user input mechanism on a Website to change the outcome of a MySQL query.

SQL injection example
Let’s say we programmed a login form that prompts the user to enter an username and a password. The input is processed using the following code:

// Query database to check if there are any matching users
$query = "SELECT * FROM users WHERE username='".$_POST['username']."' AND password='".$_POST['password']."'";
mysql_query($query);

We didn’t escaped $_POST['username'] nor $_POST['password']. An attacker could exploit this vulnerability by simply entering the following in the password field:

‘ OR username LIKE ‘%

The query that will executed on the database would be…

// This command will select all records in the user table and grant login access.
// In other cases, an attacker could even delete the whole table!
SELECT * FROM users WHERE username='' AND password = '' OR username LIKE '%'
echo $query;

That’s why you should always escape data from external sources before running the query. By “external source” I mean $_POST or $_GET data gathered by a form or $_GET data gathered by a hyperlink. Again: escape all data from user input before running the query. Write this rule on a bright yellow sticky note and place it on your monitor. Set a daily remainder on your cellphone calendar every morning. Open up Photoshop and design a desktop wallpaper that reads ESCAPE ALL DATA FROM USER INPUT. Write it on your forehead. A L W A Y S escape data from external sources.

Now, there is the right way and the wrong way to escape data. Well, at least on my humble opinion. Some articles online will teach you how to escape data using magic quotes. I do not recommend using magic quotes to escape data. On the contrary, I encourage you to disable magic quotes.

What is magic quotes?
Magic quotes is a process that escapes incoming data to a PHP script. For example, when it is set to On then single quotes, double quotes, backslashes and NULL characters are escaped with a backslash automatically. It affects GET, POST and COOKIE data.

Turn off magic quotes, NOW!
If the magic quotes setting is set to On, you might end up inserting extra slashes to your database. For example, let’s say you coded a form that prompts for 3 required fields: name, email and a brief comment. So you code the script in a way that it checks the user doesn’t leave any of those fields empty. If the user fails to submit an email address, your script will display an error prompting the user to enter an email address and the contents of the name and comment fields will re-display what the user has entered so far.

The re-displayed text might have slashes the magic quotes setting added.

What’s wrong about this approach? You may end up with extra slashes and now you would have to strip them out.

The MySQL injection attack solution

My recommendation is switch off magic quotes and escape characters using mysql_real_escape_string.

The mysql_real_escape_string function escapes special characters in a string for use in a SQL statement. This function returns the escaped string on success, or FALSE on failure.

The following characters are affected:

• \x00
• \n
• \r
• \
• ‘
• “
• \x1a

Whenever you are going to process input data from a form, use the mysql_real_escape_string function. It’s as simple as the following…

$name = mysql_real_escape_string($_POST['name']);
$email = mysql_real_escape_string($_POST['email']);
$comments = mysql_real_escape_string($_POST['comments']);

I recommend the use of this function over other methods of escaping quotes and other special characters because mysql_real_escape_string calls the MySQL library, it’s a MySQL developer’s solution rather than a solution provided by the PHP developers.

Do you want to encourage your visitors to say hi when they visit your friendly Website? This tutorial is divided in 4 phases and will teach you how to create a simple PHP guestbook using only one PHP file.

We will create the PHP/MySQL Guestbook in four phases

1. Setting up the database
2. Creating the Guestbook form
3. Coding the PHP script that will process the guestbook submissions
4. Coding the PHP script that will display the guestbook entries

1. Setting up the database

The table’s name will be guestbook. We are going to setup the following columns:

• id – the unique identifier for a guestbook entry
• name – the visitor’s name
• email – the visitor’s email
• comments – the message
• datetimecreated – to record the date and time the visitor submitted an entry

If you haven’t created a database yet, using whatever interface you use for working with MySQL databases, you can execute the following command to create a database.

CREATE DATABASE mywebsite;

You can execute the following command to create our guestbook table.

CREATE TABLE guestbook
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name TEXT,
    email TEXT,
    comments TEXT,
    datetimecreated DATETIME NOT NULL
);

2. Creating the Guestbook form

We have 3 fields where the visitor can enter data and 1 hidden field with a preset value:

• name: for the user to input his/her name
• email: for the user to input his/her email address
• comments: guestbook comments

process: is a hidden fields that will send a value of 1 when the user clicks the Send Message! button. We use this hidden field to let the PHP code know that the user has clicked on the button and that it should process the code that inserts the data in the database

When the user clicks the Send Message! button, a $_POST array is created. $_POST is an array of variable names and values sent by the HTTP POST method. The $_POST variable is used to collect values from a form that uses the POST method.

3. Coding the PHP script that will process the guestbook submissions

if(isset($_POST['process']) and $_POST['process']==1) {
    $name = trim($_POST['name']);
    $email = trim($_POST['email']);
    $comments = trim($_POST['comments']);

    $query = 'INSERT INTO guestbook SET
         name = "'.$name.'",
         email = "'.$email.'",
         comments = "'.$comments.'",
         datetimecreated = NOW()';

    if(mysql_query($query)) {
        echo '
Thank you for signing our guestbook!

';
    }
    else {
        echo '
Sorry, your entry could not be submitted.

';
    }
}

if(isset($_POST['process']) and $_POST['process']==1) allows us to process the code inside the if statement ONLY when the submit button has been clicked.

We build the query using the values entered into the form and the NOW() MySQL function which returns the current date and time as a value in ‘YYYY-MM-DD HH:MM:SS’ format.

The msyql_query() function returns TRUE if the email was successfully sent, FALSE otherwise. If the function was executed successfully, a thank you message in blue letters will be displayed. If the function returns a FALSE value (the query was not successful), the code echos an error message in red letters.

4. Coding the PHP script that will display the guestbook entries

$query = 'SELECT * FROM guestbook ORDER BY datetimecreated DESC';
$result = mysql_query($query);
if(!$result) {
    echo '
Could not retrieve guestbook entries.

';
}
else {
    while($row = mysql_fetch_array($result)) {
        echo '

';
        echo ''.$row['name'].'';
        echo $row['email'].'';
        echo date('F j, Y', strtotime($row['datetimecreated'])).' at '.date('g:i a', strtotime($row['datetimecreated'])).'';
        echo $row['comments'];
        echo '

';
    }
}

The query will sort the results from newest to oldest by including the ORDER BY clause.

For SELECT statements, mysql_query returns a resource on success, or FALSE on error.
In our script, !$result will equal to TRUE on error. This is because of the way the ! logical operator works. For example, !$result is TRUE if $result is not TRUE. We could translate it as “if $result is not TRUE”. Consequently, if !$result is FALSE, the if statement evaluates to TRUE and an error message in red letters will be displayed.

If mysql_query is successfully executed the while loop will display the text of each entry in a paragraph.

There we go! With a single file we are able to add and view entries to our guestbook.

TIPS
When accepting data from forms you should always validate the data and be protected against SQL injection attacks. If you don’t know what a SQL injection attack is and how to protect from them, it is imperative you get informed about this important subject. Don’t risk the integrity of your databases. Please read Protect your script from hackers: SQL injection attack prevention.