Subscribe via RSS

Fundamentals of MySQL Security

Web 2.0 is a fine thing. You can build whole websites, news systems, or picture galleries – just by using the power of content submitted by users.

But be careful! You can never trust every user when it comes to submitting data. There are always those who may try to compromise your website by submitting bad queries. Even if unintentional, it can be possible for users to destroy your database’s consistency just by entering incorrect data.

In this post, we’ll take a look at some ways to help prevent improper inputs and damaging queries.

Keep the Format Straight

PHP is (unfortunately/luckily) typeless. This means that you can fill any variable with any type of data. A variable that should contain a number, for example, can also take a string value without hassle.

Conflicts in expected format can cause problems. If a user submits a string in a text field where he should only enter a number and your database field is defined to store a number too, the server refuses the query with an error.

If you’re not careful to validate incoming data, a user could manipulate information in the database or even delete it. This is an incredibly unattractive thing for your own projects, but even more so if it happens on a client’s project.

So what can you do to prevent this sort of thing?

To start, you can check if the values in your variables fit the expected scheme. If they don’t, you can turn them into the correct format for processing. Let’s start with numbers.

Converting Numbers

Where you expect a number, there must only be numbers in your SQL queries. You can achieve this by converting the variable into the type integer:

  1. $variable = (int)$_POST['textinput'];

The statement (int) before a variable indicates type casting in PHP. This means: The variable will be converted into the type given by the statement.

Simply put, if it contains “1234” then you will get “1234”. But if, for example, the variable contains a string “hello”, you will receive a “0”. But what if you want to let the user know an error in format?

  1. $variable = (int)$_POST['textinput'];
  2. if($variable == 0) echo “Please fill out all input fields correctly!”;

In the above example, if the user leaves a text field empty or enters a text instead of a number, the error message will be shown.

For more information about type casting take a look at this reference article on

Prevent Escape Characters

Text input has some pitfalls. For example, if you’d like to write two text variables into your database, then your MySQL query would look something like this:

  1. INSERT INTO table (textA, textB) VALUES ('Hello', 'World');

So far, so good. But if a user had entered an apostrophe, the query will go wrong. SQL expects the string-end at the apostrophe (because strings have to be encapsulated in quotation marks or apostrophes), if it stumbles over another one and is confused it will throw a syntax error.

In these cases I advise you to use the following function:

  1. function mask($string)
  2. {
  3. // Masks a string for an sql query.
  4. $string =(get_magic_quotes_gpc()) ? stripslashes($string) : $string;
  5. return mysql_real_escape_string($string);
  6. }

The function mysql_real_escape_string() escapes special characters like apostrophes or quotation marks with a backslash ( \ ), so they won’t be treated as a string ending.

This function also checks for a special PHP setting. Some PHP setups escape strings automatically (to be checked with the function get_magic_quotes_gpc()) – and a double escape would result in fancy backslashes on your website.

So if it’s already escaped – throw them away and escape it with the proper MySQL function.

Note: The function mysql_real_escape_string() works only with a established database connection via mysql_connect()!

A correctly cleaned up SQL query should look something like this:

  1. $varA = mask($_POST['text1']);
  2. $varB = (int)$_POST['number1'];
  3. $varC = (int)$_POST['number2'];
  4. $varD = mask($_POST['text2']);
  5. $sql = “INSERT INTO table (textA, numberB, numberC, textD) VALUES ('$varA', $varB, $varC, '$varD');”;

Another Tip: Create SQL databases and tables with the collation “utf8_unicode_ci” (at the bottom of the dropdown-menu in phpmyadmin) to prevent problems with special characters.

Posted by ABDUL SABOOR Monday, October 5, 2009


Post a Comment