Admin Life Errors, Fixes, and Encounters

By default mysql installs itself in /var/db in freebsd. If you have a big database then this folder will quickly get filled up.
Here are the commands to move mysql from /var/db to /usr/local/mysql

cd /var/db
mv mysql /usr/local/
ln -s /usr/local/mysql mysql

cd /usr/local
chown -R mysql mysql

Then start up mysql

done.

PHP MySQL injection

Here is an example of how an SQL injection that was found, monitored, and fixed!

When dealing with a large website with legacy code sometimes it's not possible to check every query after one starts.

One day while reviewing a new clients slow quries via MySQL. I started to notice these types of quries showing up.

# [email protected]: test_db[test_db] @ db.domain.com [12.13.14.15]
# Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 48648
select * from serv_main where id = 71261 and 3=8 union select 1,2,3,concat(0x232425,ifnull(`idClient`,0x4E554C4C),char(9),ifnull(`username`,0x4E554C4C),char(9),ifnull(`pass`,0x4E554C4C),char(9),0x252423),5,6,7,8,9,10,11,12,13,14,15,16 from `test_db`.`private` where idClient>2119021 limit 16215,1 --;

# [email protected]: test_db[test_db] @ db.domain.com [12.13.14.15]
# Query_time: 1 Lock_time: 0 Rows_sent: 1 Rows_examined: 48654
select * from serv_main where id = 71261 and 3=8 union select 1,2,3,concat(0x232425,ifnull(`idClient`,0x4E554C4C),char(9),ifnull(`username`,0x4E554C4C),char(9),ifnull(`pass`,0x4E554C4C),char(9),0x252423),5,6,7,8,9,10,11,12,13,14,15,16 from `test_db`.`private` where idClient>2119021 limit 16217,1 --;

Knowing the table serv_main I knew the developer that wrote that code did not use a UNION.
After a few moments of thinking what was going on I tailed Apaches access log and confirmed what I thought.

120.2.194.136 - - [1/Jan/2010:10:08:40 -0500] "GET /viewAd.php?id=71261%20and%203%3D8%20union%20select%201,2,3,concat%280x232425,ifnull%28%60idClient%60,0x4E554C4C%29,char%289%29,ifnull%28%60username%60,0x4E554C4C%29,char%289%29,ifnull%28%60pass%60,0x4E554C4C%29,char%289%29,0x252423%29,5,6,7,8,9,10,11,12,13,14,15,16%20from%20%60test_db%60.%60private%60%20where%20idClient%3E2119021%20limit%2034642,1%20-- HTTP/1.1" 200 1093 "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.7) Gecko/20091221 Firefox/3.5.7 (.NET CLR 3.5.30729)"

It was an SQL Injection attack. so I did the next logical step (to me at least). Block the offender.

I did this by adding his IP to the .htaccess file along with a bigger subnet block.
Order allow,deny
deny from 120.2.194.136
deny from 120.2.194.
allow from all

Now that the Injections had stopped, the next step was to figure out where it was done, how it was done, and how to fix it.

WHERE was it done)
That was easy to find, it's right there in the access log viewAd.php

How was it done)
$sql = "select * from serv_main where id = " . $_GET['id'];
$result = mysql_query($sql);

Fix)
That was the easy part, and the hard part.

The EASY part was just to just use mysql_real_escape_string.

we have 2 quick options here.

Option 1.

$_GET['id'] = (int) $_GET['id']; //cat whatever is passed into id as an integer.

Option 2.

$sql = "select * from serv_main where id = " . mysql_real_escape_string($_GET['id']); //escape user input
$result = mysql_query($sql);

The HARD part, fix the whole site...
Not having time to check 1000's of quries, here was what I did in a 30 mintue span.

1) include a function somewhere in a global include that can clean all input (or cast all input)
a good place was in our db_include.php file.

function sanitize_user_input(&$input) {

$int_keys = array ('id', 'item', 'item_id' );

foreach ( $int_keys as $key ) {
if ( isset($input[$key]) ) {
if ( !empty($input[$key]) && !is_numeric($input[$key]) )
$input[$key] = (int) $input[$key];
}
}
}

if (isset($_GET)) sanitize_user_input($_GET);

The hard part was easier than I thought.

But the truly hard part started, going though the 1000's of queries to fix them.

Some interesting points and notes.

#http://www.asciitable.com/
# 0x232425 = #$%
# 0x252423 = %$#
# 0x4E554C4C = NULL
# 0x4E554C4C = NULL
# CHAR(9) = Tab
# CHAR(10) = Line feed
# CHAR(13) = Carriage return
select concat(0x232425,
ifnull(`idClient`,0x4E554C4C),
char(9),
ifnull(`username`,0x4E554C4C),
char(9),
ifnull(`pass`,0x4E554C4C),
char(9),
0x252423)