Admin Life Errors, Fixes, and Encounters


Move MySQL database directory

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


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.

# User@Host: test_db[test_db] @ []
# 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 --;

# User@Host: test_db[test_db] @ []
# 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. - - [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: 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
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);

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.

# 0x232425 = #$%
# 0x252423 = %$#
# 0x4E554C4C = NULL
# 0x4E554C4C = NULL
# CHAR(9) = Tab
# CHAR(10) = Line feed
# CHAR(13) = Carriage return
select concat(0x232425,

Filed under: Errors, MySQL, PHP No Comments

mysql replication error

MySQL replication broke after a server crash. When trying to start it backup this error shows up.

100303 10:09:54 [ERROR] Slave: Query caused different errors on master and slave. Error on master: 'Invalid error code' (126), Error on slave: 'no error' (0). Default database: 'db_name'. Query: 'INSERT DELAYED INTO stats (clientID, date_accessed, current_url, referred_by, unique_id)
VALUES (0, now(), '/index.php', '', '076c47a32066f611d60e107b3c6be475')', Error_code: 0
100303 10:09:54 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000463' position 207592

After a little searching, in order to fix the problem I just have to repair the table with the following command.

mysql> repair table stats;

In my case the query is fine, the reason why it is failing may be
index file is crashed or file format is wrong. Here is a list of error codes that may be helpful to other people.
126 = Index file is crashed / Wrong file format
127 = Record-file is crashed
132 = Old database file
134 = Record was already deleted (or record file crashed)
135 = No more room in record file
136 = No more room in index file
141 = Duplicate unique key or constraint on write or update
144 = Table is crashed and last repair failed
145 = Table was marked as crashed and should be repaired
28 = You ran out of disk space. Delete unnecesary files, or move the MySQL data files to another partition.
1045: Access denied for user: = You forgot to specify a password, or you used the wrong one. Use the -u and -p options when starting the client. (use mysql --help to see how)
1046: No Database Selected = You must use the command USE database_name or the mysql_select_db() function to select a database to use.
1062: Duplicate entry 'x' for key n = You have attempted to insert or update data that would break the integrity of a UNIQUE index (most likely the primary key). You must redefine either the data you're trying to insert, or the indexes which you have imposed on the table.
1064: Syntax error = You wrote something that doesn't match the syntax definitions in the manual. When you have checked everything else, here's something to consider: A common error is to put a space between a function name and its opening parenthesis '('. Another common error is to use a reserved word out of its normal context.
2000: parse error near ... = see explanation for 1064: Syntax error
2002: Can't connect to local MySQL server = The MySQL socket file (usually, /tmp/mysql.sock) is missing. See the notes on connecting to the server.

Filed under: Errors, MySQL, Unix No Comments

memcached php mysql freebsd

Too many slow queries running on MySQL and some can not be fixed without a major change? Here's a potential solution, cache it!

Step 1) Install memcache, in this case on FreeBSD

1. cd /usr/ports/databases/memcached; make install clean
2. vi /etc/rc.conf, and add memcached_enable="YES"
3. /usr/local/etc/rc.d/memcached start
4. run "netstat -an" to check that a process is listening on TCP 11211
5. cd /usr/ports/databases/pecl-memcache ; make install clean
6. /usr/local/bin/php -i | grep -i 'memcache'
7. Play with memcached in PHP scripts

step 2)
use memcache in your scripts!

Filed under: MySQL, PHP No Comments