Cleaning up User Input for MySQL
Dealing With Invalid User Data
Case 1 - Invalid HTML Tags
Solution 1 : Stripping out HTML tags before inserting via MySQL
Case 2 : Using apostrophes and quotation marks in comments
Solution 2 : Escaping quotation characters with mysql_real_escape_string()
Case 3 : Preventing SQL injection attacks
Solution 3 : Prepare Your Inputs
One of the things you’re going to encounter when using MySQL with PHP sooner, rather than later, is the issues that arise when you start accepting user input in the form of comments or blog posts. Up until now in this tutorial, we’ve just been using data that we’ve defined ourselves. We’ve been able to control what goes into our database tables.
When dealing with the general public though, we can’t assume anything about the data they submit into our database. In fact, we have to assume the worst, since the worst is what might break our website. Let’s look at a simple example first of all
Let’s assume that we’re working on creating a brand new website containing useful information about microwave ovens. On this website, we want users to be able to leave comments and discussion points about their experiences with microwave ovens, and general chat with other members. In this tutorial, we’re not interested in the general workings of such a site, instead we’re going to look closely at what happens when users submit a wide range of “faulty” discussion posts, and what we can do about them
Imagine that this is the general layout of our comment posting area
<h2>Robin says...</h2> Hi everyone, I love microwave ovens <h2>Bill says...</h2> I, too, love microwave ovens
This is a pretty straightforward layout, with simple headings displaying the name of each poster, and their comment underneath.
The actual process that’s occuring on our fictitious site is
- A user is invited to post a comment using an HTML submission form
- This data is sent to our server, where we process a MySQL command along the lines of “INSERT INTO comments (user_name, user_comment) VALUES (’Robin’, ‘Hi everyone, I love microwave ovens’)“
- We then display this data on the website, using commands like “SELECT user_name, user_comment FROM comments“
Now, this might all seem perfectly harmless, but in stages 2 and 3 there is a lot of room for error, and with a simple implementation like we have above, many opportunities for people to - willlingly or unwillingly - cause more than a little havoc on our website
In our example above, the user Robin was submitting the comment “Hi everyone, I love microwave ovens”. Now, let’s change that. Consider now that Robin is trying to post the comment “Hi everyone <h2>HELLO!</h2></p></p></p><h1>HEELLLOOO!</h1>”. See all those random HTML tags within the comment? This comment will have no problem being accepted into our MySQL query like so
$user_name = $_POST['user_name']; //user_name = "Robin"; $user_comment = $_POST['user_comment']; //user_comment = Hi everyone <h2>HELLO</h2></p></p></p><h1>HEELLLOOO!</h1> mysql_query("INSERT INTO comments (user_name, user_comment) VALUES ($user_name, $user_comment");
Our big problem comes in step 3, when we try and display this as HTML. It’s going to totally mess up our page layout. By allowing our users to submit anything they like, they then have free reign to mess up our page in whatever way they see fit. Fortunately, PHP comes to the rescue and gives us a relatively simple solution to our problem
Using a handy PHP function, we can automatically strip whatever tags we don’t want to allow into our database. The function strip_tags() takes as its arguments, a string and an optional list of allowed tags, and returns a string containing only the characters we want to let in to our database. Consider that we only want to allow the tags <b>, <i> and <u>, for some simple formatting (usually we disallow things like <a> to prevent people from spamming links)
$bad_text = "<h1>We're not going to allow heading texts</h1><b>but we will allow bold</b>"; $good_text = strip_tags($bad_text, "<b><i><u>"); echo $good_text;
If you run this script, and then look at the source of the output (”View -> Source” in most browsers - this is because the tags will be displayed as text, rather than HTML) you will see
<h1>We're not going to allow heading texts</h1><b>but we will allow bold</b>
Now, we could run this function on our input data before accepting it into our INSERT query, to ensure that no bad data gets placed inside the SQL code to mess up our formatting
//user_name = "Robin"; $user_name = strip_tags($_POST['user_name']); //user_comment = Hi everyone <h2>HELLO</h2></p></p></p><h1>HEELLLOOO!</h1> $user_comment = strip_tags($_POST['user_comment'], "<b><i><u>"); //user_comment now contains no unwanted HTML tags mysql_query("INSERT INTO comments (user_name, user_comment) VALUES ($user_name, $user_comment");</em>
You might have noticed that when defining your MySQL query code, you have to pay special attention to the quotation marks used within PHP function calls. For example, in the example
$address = "'Maple House', 41 Avenue Drive, Islington"; mysql_query("INSERT INTO employees (address) VALUES ('$address'));
We are trying to insert the address ‘Maple House’, 41 Avenue Drive, Islington, but see how we’re specifying a house name, and have decided to wrap that in single quotation marks? This will conflict with the syntax of the MySQL function call, since we are already wrapping the whole string in single quotation marks and the INSERT procedure will fail. A solution might be to wrap the address in double quotation marks, but that doesn’t solve the problem, since the whole string could potentially also be wrapped in double quotes. And what about apostrophes? They always use the single quotation mark.
A good solution to this is to use escaping, so that we can use special characters such as double and single quotation marks within our MySQL queries. Imagine we wanted to insert the address “Maple House”, 36 Baker’s Street, Islington. This address contains both single and double quotation marks so we need to be careful. Here is an example of using quote escaping to make our data safe for input
$address = $_POST["address"] //$_POST["address"] is "Maple House", 36 Baker's Street, Islington; $safe_address = mysql_real_escape_string($address); mysql_query("INSERT INTO employees (address) VALUES ('$safe_address')"); //this is equivalent to INSERT INTO employees (address) VALUES ('"Maple House", 36 Baker's Street, Islington')
Here, the PHP function mysql_real_escape_string() will add slashes to all characters that need escaping for entry into a MySQL command, such as single quotes (’), double quotes (”) and other slashes ()
Note: There is a PHP directive, magic_quotes_gpc, which automatically adds slashes to $_GET and $_POST data whenever it is turned on. This is something to consider when dealing with MySQL data input, and you can check the status of this directive by use of get_magic_quotes_gpc()
Note, also: You must have a MySQL connection already set up, using the mysql_connect() function in order for mysql_real_escape_string() to work. Otherwise it’ll just return an error.
One particularly nasty form of attack that can be carried out on your site is the SQL injection attack. In basic terms, this is when a user attempts to send unfiltered data into your database queries to take control of the database and run whatever queries they wish. They could retrieve sensitive data from your database, or even delete every single record on your site.
Here is an example of a simple attempt at an SQL injection attack. Consider this simple MySQL code
$user = $_POST['username']; $password = $_POST['password']; $result = "SELECT * FROM users WHERE name = '$user' AND password = '$password'"; if($row = fetch_mysql_array($result)) { //user is authenticated }
This is a simple example of a (very) basic login script, which simply retrieves the username and a password from the $_POST variable (submitted via an HTML form) and checks to see if this user exists in this database along with this password (note that in a real database, the password should be encrypted, but we’re just using an unencrypted password for our example)
The flaw in our plan lies in the fact that we are using the variables as-they-are, directly inserted into the SQL string. You might not immediately see the flaw in this plan, but consider if we have the following input for the script
$user = "Robin"; $password = "x' OR 'a'='a";
This strange looking example for the variable $password is actually a devious attempt to fool your MySQL code into thinking that a valid attempt has been made to access an area of the site reserved only for authorised users, by making it seem like a valid password has been entered for the user Robin. Let’s see what happens when this value is entered into our SQL string
SELECT * FROM users WHERE name = 'Robin' AND password = 'x' OR 'a'='a'
Here, the condition “password = ‘x’ OR ‘a’='a’” will always evaluate to true since we are checking that ‘a’ equals ‘a’, which it does. It does not matter if we get the correct value for “password”
The best defence against SQL injection attacks is vigilance and to maintain a set of personal coding rules whenever you’re creating MySQL queries. Never allow a situation like the one above to occur, where users have direct access to the values introduced into MySQL code.
Always validate your user defined inputs before accepting them into any SQL strings, and put whatever checks you feel are necessary in place to prevent malicious types from messing up your carefully crafted website.
A simple solution to the above injection example would be to use the mysql_real_escape_string() PHP function to ensure that, whatever input the user provides, it is always checked and treated for such attempts. The following example will prevent any SQL injection attack from taking place
$user = "Robin"; $password = "x' OR 'a'='a"; $result = "SELECT * FROM users WHERE name = '".mysql_real_escape_string($user)."' AND password = '".mysql_real_escape_string($password)."'"; if($row = fetch_mysql_array($result)) { //user is authenticated }
This will attempt to run the MySQL query
SELECT * FROM users WHERE name = 'Robin' AND password = 'x\' OR \'a\'=\'a'
Which is perfectly valid, and will try and match the user Robin with the literal password “x’ OR ‘a’='a” because of the presence of the escaping slashes, and this will fail. Unless that is, of course, the correct password!
September 23rd, 2008 at 9:39 am
in regards to stripping HTML from forms you might want to make your life easier and use something like this.
$comment = mysql_real_escape_string(htmlentities($_POST["comment"], ENT_QUOTES));
i added in variable and MRES out of habbit, but if you were to combine both of your security points you’d want to structure it like that maybe.
November 1st, 2008 at 8:04 am
Can We Implement the same in Java or Jsp itself.
If not then how we should do to overcome these type of the problems.
November 14th, 2008 at 10:38 pm
Thank you very much am highly great full
November 15th, 2008 at 9:56 am
How would one go about making a CMS? Say I still wanted users to add their own or such?
November 28th, 2008 at 10:35 am
Good job! … well done!