As always, make sure you've fully read the disclaimer so that you understand our policy BEFORE reading this article!

Database - Protect Yourself

As every web developer knows, when code is involved in creating a website, generally a database of some sort is involved as well. Therefore, being familiar with safe database security practices is a must. If you aren’t, your website(or your clients!) could be hacked with ease. Fear not, there are steps to avoid this issue.

Step 1: Don’t Ever Strip!

The Issue at Hand

User input is quite a tricky area. Too often programmers simply anticipate what they believe a user might attempt to input into their web application, when actually, much to their surprise, users end up trying every combination possible one way or another(on purpose or not). The first time most developers realize this to be an issue is the first case that something goes wrong because a user inputted something unexpected, that maybe called an error, which brought it to the developers attention.

Generally, if the developer isn’t well experienced(especially with databases), they will simply strip out what the user inputted so that that issue is resolved. But what happens when another user accidentally enters in another unexpected value? The developer then has to change his code once more, accommodating for that value and making sure to strip it also.

What ends up happening is that the developer has to keep modifying his code because more unexpected values will be inputted by users.

Solving with Accuracy

Essentially, the developer’s mindset is what should be changed. While he may be able to program valid code, the issue isn’t in the code, it’s in the mindset of how the problem exists and what to do about it.

Instead of actually stripping out values, the developer should ONLY accept correct values in the first place. For example, if the developers web application asked for the users First Name and Last Name, we can assume(within reason) that only alphanumeric values should be allowed. If the user types something that is not an alphanumeric value, then an error should be returned to the user telling him to enter an appropriate value.

What this does is that it creates the most opportune situation for the developer that one can hope for. The developer now only has to specify what he wants the users to input, not what he doesn’t want them to input.

Step 2: Database Interface

Unnecessary Code

Assuming you’ve bundled up your validation code to make sure what value the user inputted is alright, how many times are you actually calling that code? What happens is developers create a validation function or class, but they often then directly make query calls to their database all throughout their code and then are forced to call the validation code again and again. This creates a large amount of unnecessary code.

The Man Interface in the Middle

Now matter what language a developer is dealing with, an interface should be created to go in between the database and the developer. That way the developer can simply call methods from his database interface and things like validation are handled all in one spot. For instance, this is a quick PHP example:

class dbInterface
{
    function query($input)
    {
        $validated_input = $inputValidation->validate($input);

       $sql = "SELECT * FROM table WHERE name = '" . $validated_input . "'";

        mysql_query($validated_input);
    }
}

//Example usage: $dbInterface->query($input);

The example is by no means an actual representation of real functions, but rather a general concept on what could/should be done.

However, the most effective and efficient way to solve this issue would be to call this query inside of other classes such as a User class, etc. Also, other arguments should be supplied to the validate method to tell it in some way or another, what type of input it is checking.

Note: A lot of MVC Frameworks provide features like this that you can take advantage of without having to write your own.

Step 3: Encrypting/Hashing

Every database generally has at least one item that needs to be kept secret via encryption/hashing. The most common item is a user password, which definitely should not be stored in plain-text in the database because if someone got a hold of the database(via hacking the server or an exploit in some piece of code), then that person could simply read every password as clear as day. Definitely not a good thing.

// Perform the encryption
  $salt = substr($typed_in_password, 1, 3);
  $encrypted_password = crypt($typed_in_password, $salt);

  // Build the query

  $query = "SELECT username FROM members WHERE
      username = '$typed_in_username' AND
      password = '$encrypted_password'";

  // Execute the query

  if (mysql_numrows(query($query)) == 1) {
      // Do something here, since password was correct
  }

As you can see from the above code, we’re using PHP’s crypt() function to actually encrypt the password the user typed in, with the salt being a substring of the password itself. We then build a query to actually check if the encrypted password matches the users encrypted password stored in the database. Quite simple, but it protects us in a major way. There are many different encryption/hashing schemes to choose from, so you’re bound to find exactly what fits you best.