Fundamentals of MySQL Query Validation

Fundamentals of MySQL Query Validation

Editor’s Note: This is a guest post from Christian Engel, who runs a German web design/development blog. In the interest of reaching out to an English speaking audience, we’ve collaborated with him to produce the following translation based on one of his original posts.

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:

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?

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 PHP.net.

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:

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:

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:

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.

Be Smart, Not Scared

Using these fundamentals as a starting point, the route to a database become much more clear and secured for incoming data. If nothing else, just internalize the following declaration: “You can NEVER trust in data submitted by a user!”

Posted Thursday, August 6th, 2009 · Back to Top

SPONSOR

Add Comment

6 Comments 5 Mentions

  1. Eric B. Author Editor

    Thanks for the tips. A guy I know once messed up his project with SQL injection.
    .-= Eric B.´s last blog ..Creating a Print Stylesheet =-.

    ·

  2. Conrad Bugeja Author Editor

    What about using to make sure the var is an integer intval($number)?

    ·

  3. Botnary Author Editor

    why not use intval($_POST['textinput']) instead of (int)$_POST['textinput'];

    ·

  4. crazyball Author Editor

    But the use of the “get_magic_quotes_gpc()” function is not recommended. This function will be deprecated and/or disactivated in PHP 6
    .-= crazyball´s last blog ..[Critique] GI-Joe : Le réveil du cobra =-.

    ·

  5. Paratron Author Editor

    Hey, thanks for the comments!

    @Conrad @Botnary
    I admit: I’m a lazy person. I never type more than I have to. And (int) is shorter than intval().
    Thats the secret :D

    @crazyball
    Thats interesting! I just took a look on php.net and saw that you are right! But more important is: Will the automatic escape routine be removed in php6 or be active by default? I can’t figure that out on php.net

    ·

  6. Otis Author Editor

    Thank you for sharing such an amazing material.

    ·

 

Build Internet by One Mighty Roar. Since 2008.