Working with MySQL: Storage

Once you’ve created a MySQL Database, the obvious next step is to try and store data in it. Databases can store all sorts of things: everything from basic visitor feedback to complex weblog systems and even shopping carts. Of course, trying to explain how to create a working shopping cart is way beyond the scope of tutorialtastic, but there’s no reason why we can’t work on other things.

Firstly, you need to decide what to store. For the purpose of this tutorial I’ve decided to use a website survey system. This part of the tutorial deals with collecting and storing the information, including preparing the data to prevent common SQL injections and hacking attempts.

The HTML Form

To store data, you have to collect it first. To save time, and because it works well, I’m using the basic HTML form from my PHP Mail Form tutorial for this with the small addition of a “score” drop-down menu. This HTML is as follows (save it as anything you like, e.g. form.html):

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">

<html>
<head>
    <title>Visitor Survey Form</title>
</head>
<body>
    <form method="post" action="form-process.php">

    Name:<br>
    <input type="text" name="name"><br>
    E-mail:<br>
    <input type="text" name="email"><br>
    Country:<br>
    <select name="country">
        <option>United Kingdom</option>
        <option>United States</option>
        <option>Other</option>
    </select><br>
    Overall Score:<br>
    <select name="score">
        <option>1</option>
        <option>2</option>
        <option>3</option>
        <option>4</option>
        <option>5</option>
    </select><br>
    Comments:<br>
    <textarea name="comments" rows="10" cols="30"></textarea><br>

    <input type="submit" name="submit">

    </form>
</body>
</html>

Creating Our Database Table

Based on our form setup we know that we will need to store the following types of data: Name, E-mail Address, Country, Score and Comments. This will help us create the correct fields for the table in our database. Before we can create it though, we need to connect to the database using the name and password we assigned when we created the database. The basic connection string is as follows (place this in a file called “create.php”):

<?php
$conn = mysql_connect("localhost", "YOUR-MYSQL-USER", "YOUR-PASS");
mysql_select_db("YOUR_DATABASE", $conn);

?>

You must replace “YOUR-MYSQL-USER” etc with the actual details for your database and user. The database host — in this case “localhost” — can usually stay the same. However, if you have trouble connecting to the database when we run the script later on, contact your host to make sure you have the correct value.

Next we construct the statement that adds the table with the necessary fields.

First and foremost, we need a field to uniquely identify each row — this is very commonly created with a name of ‘id’ as an INT (integer = number) type, and this will be our primary key. Although primary keys are more important for complex database normalisation, I like to use one anyway. We also need fields for our shorter data, like Name, E-mail Address and Country. In my opinion the ideal field type for these is VARCHAR (or variable character). MySQL VARCHAR fields support up to 255 characters. As our Score will be purely numerical, this can be another INT field which leaves us with Comments; which, to give the visitor adequate space to leave their thoughts, can be created as a TEXT field.

We need to edit our create.php file and add our statement. At this point we can also add an if statement to ensure the query executes. This is as follows:

<?php
$conn = mysql_connect("localhost", "YOUR-MYSQL-USER", "YOUR-PASS");
mysql_select_db("YOUR_DATABASE", $conn);

$create = "CREATE TABLE `tt_survey` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `name` VARCHAR( 25 ) NOT NULL,
   `email` VARCHAR( 255 ) NOT NULL,
   `country` VARCHAR( 50 ) NOT NULL,
   `score` INT NOT NULL,
   `comments` TEXT NOT NULL,
PRIMARY KEY (`id`))";

if (mysql_query($create, $conn)) {
   echo "Table (and fields) created.";
} else {
   die("Error:". mysql_error());
}

?>

You can now upload the create.php script and run it in your browser. If you get the “Table (and fields) created.” success message, delete create.php: you’re ready for the next step. (If you get an error, a quick Google-search will likely find you a solution.)

Processing the Submitted Data

The next step is taking the data from the form that we created earlier and process the data ready to store in the database. To save time and to prevent me from needlessly repeating myself, you can read the PHP Mail Form tutorial for detailed information on cleaning the data and implementing spam protection. This code is pretty generic and will work well in any script that deals with fetching data from forms. In short, the code (minus the mail function that is unnecessary) is as follows:

<?php
if (!isset($_POST['submit']) || $_SERVER['REQUEST_METHOD'] != "POST") {
    exit("<p>You did not press the submit button; this page should not be accessed directly.</p>");
} else {
    $exploits = "/(content-type|bcc:|cc:|document.cookie|onclick|onload|javascript|alert)/i";
    $profanity = "/(beastial|bestial|blowjob|clit|cock|cum|cunilingus|cunillingus|cunnilingus|cunt|ejaculate|fag|felatio|fellatio|fuck|fuk|fuks|gangbang|gangbanged|gangbangs|hotsex|jism|jiz|kock|kondum|kum|kunilingus|orgasim|orgasims|orgasm|orgasms|phonesex|phuk|phuq|porn|pussies|pussy|spunk|xxx)/i";
    $spamwords = "/(viagra|phentermine|tramadol|adipex|advai|alprazolam|ambien|ambian|amoxicillin|antivert|blackjack|backgammon|texas|holdem|poker|carisoprodol|ciara|ciprofloxacin|debt|dating|porn)/i";
    $bots = "/(Indy|Blaiz|Java|libwww-perl|Python|OutfoxBot|User-Agent|PycURL|AlphaServer)/i";

    if (preg_match($bots, $_SERVER['HTTP_USER_AGENT'])) {
        exit("<p>Known spam bots are not allowed.</p>");
    }
    foreach ($_POST as $key => $value) {
        $value = trim($value);

        if (empty($value)) {
            exit("<p>Empty fields are not allowed. Please go back and fill in the form properly.</p>");
        } elseif (preg_match($exploits, $value)) {
            exit("<p>Exploits/malicious scripting attributes aren't allowed.</p>");
        } elseif (preg_match($profanity, $value) || preg_match($spamwords, $value)) {
            exit("<p>That kind of language is not allowed through our form.</p>");
        }

        $_POST[$key] = stripslashes(strip_tags($value));
    }

    if (!ereg("^[_a-z0-9-]+(.[_a-z0-9-]+)*@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,6})$",strtolower($_POST['email']))) {
        exit("<p>That e-mail address is not valid, please use another.</p>");
    }


}
?>

This will deal with your average spam-bot, attempts at JavaScript injection and unnecessary profanity and spam-related words. However, as we’re working with a database and not e-mail here, we need to make a small alteration (both for security and data storage purposes).

We need to remove the line with stripslashes() on ($_POST[$key] = stripslashes(strip_tags($value));) and do a check to see for magic_quotes_gpc, and if on, reverse the affects for a more secure alternative (mysql_real_escape_string()). The new code is (also using a variable variable instead of the POST array, for convenience):

        if (get_magic_quotes_gpc()) $value = stripslashes($value);
        $$key = mysql_real_escape_string(strip_tags($value));

Creating the INSERT Query

Once we’ve established that our data is safe, we can construct the query that inserts it into the database. We will make this query ‘future-proof’ — by this I mean we’ll create a query that allows you to make changes to the table structure in the future, without affecting the script. This gives you time to change the script when you choose. To do this we’ll specify exactly which fields we want to insert data into. This also allows us to skip the ‘id’ field, which will populate itself.

So, our INSERT query is:

$insert = "INSERT INTO `survey` (`name`, `email`, `country`, `score`, `comments`) VALUES ('$name', '$email', '$country', '$score', '$comments')";

We can build this into our file, with our connection details from earlier and an if statement to make sure the query is executed or deliver an error if a problem occurs.

<?php
if (!isset($_POST['submit']) || $_SERVER['REQUEST_METHOD'] != "POST") {
    exit("<p>You did not press the submit button; this page should not be accessed directly.</p>");
} else {
    $exploits = "/(content-type|bcc:|cc:|document.cookie|onclick|onload|javascript|alert)/i";
    $profanity = "/(beastial|bestial|blowjob|clit|cock|cum|cunilingus|cunillingus|cunnilingus|cunt|ejaculate|fag|felatio|fellatio|fuck|fuk|fuks|gangbang|gangbanged|gangbangs|hotsex|jism|jiz|kock|kondum|kum|kunilingus|orgasim|orgasims|orgasm|orgasms|phonesex|phuk|phuq|porn|pussies|pussy|spunk|xxx)/i";
    $spamwords = "/(viagra|phentermine|tramadol|adipex|advai|alprazolam|ambien|ambian|amoxicillin|antivert|blackjack|backgammon|texas|holdem|poker|carisoprodol|ciara|ciprofloxacin|debt|dating|porn)/i";
    $bots = "/(Indy|Blaiz|Java|libwww-perl|Python|OutfoxBot|User-Agent|PycURL|AlphaServer)/i";

    if (preg_match($bots, $_SERVER['HTTP_USER_AGENT'])) {
        exit("<p>Known spam bots are not allowed.</p>");
    }

    $conn = mysql_connect("localhost", "YOUR-MYSQL-USER", "YOUR-PASS");
    mysql_select_db("YOUR_DATABASE", $conn);

    foreach ($_POST as $key => $value) {
        $value = trim($value);

        if (empty($value)) {
            exit("<p>Empty fields are not allowed. Please go back and fill in the form properly.</p>");
        } elseif (preg_match($exploits, $value)) {
            exit("<p>Exploits/malicious scripting attributes aren't allowed.</p>");
        } elseif (preg_match($profanity, $value) || preg_match($spamwords, $value)) {
            exit("<p>That kind of language is not allowed through our form.</p>");
        }

        if (get_magic_quotes_gpc()) $value = stripslashes($value);
        $$key = mysql_real_escape_string(strip_tags($value));
    }

    if (!ereg("^[_a-z0-9-]+(.[_a-z0-9-]+)*@[a-z0-9-]+(.[a-z0-9-]+)*(.[a-z]{2,6})$",strtolower($_POST['email']))) {
        exit("<p>That e-mail address is not valid, please use another.</p>");
    }

    $insert = "INSERT INTO `survey` (`name`, `email`, `country`, `score`, `comments`) VALUES ('$name', '$email', '$country', '$score', '$comments')";
    if (mysql_query($insert, $conn)) {
        echo '<p>Data successfully inserted.</p>';
    } else {
        echo '<p>There was an error while trying to insert the data into the database.</p>';
        die("Error:". mysql_error());
    }
}
?>

Save the file as form-process.php and there you have it — a database, table, basic form and the script that inserts the data into the database. Your survey results are ready for collecting!

Comments

  1. Nice tutorial, I would add a mention about security and sql injection which can be mitigated using the mysql_real_escape_string once connected to mysql.
    Also it’s a good idea to create a mysql user (for the connection) that only has insert and update rights to prevent anyone running a malicious drop tables query against your database. As a last off, the mysql pdo connection method offers parameterised queries which can also guard against sql injection. Thanks

    • Lots of good advice Steve. Of course it’s also worth noting that the mysql_ functions are now deprecated too, and mysqli should be used, but this piece is 8 years old!

Speak Your Mind

*