Just listing a few useful MySQL Queries I use all the time to maintain my databases. They are all mainly about keeping track of database size and performance.
The following gem returns all databases and their physical sizes and free space available. querying information_schema in this manner is always slow so watch out when you run this.
SELECT
table_schema db_name,
sum( data_length + index_length ) / 1024 / 1024 db_size_MB,
sum( data_free )/ 1024 / 1024 free_MB
FROM information_schema.TABLES
GROUP BY table_schema;
The following gem returns the top 10 largest tables across all databases. I found it in the MySQL Performance blog, and it’s safe to say I use it all the time. The query is quite heavy on large databases, and may take a while to return your results as the above, so don’t use it in the middle of the day during business hours unless you want your sites to run slowly.
SELECT
CONCAT(table_schema, '.', table_name) table_name,
CONCAT(ROUND(table_rows / 1000000, 2), 'M') total_rows,
CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G') total_data,
CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G') total_index,
CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
CONCAT(ROUND(data_free / (1024 * 1024), 2), 'MB') total_free,
ROUND(index_length / data_length, 2) index_to_data_ratio
FROM
information_schema.TABLES
ORDER BY data_length + index_length DESC
LIMIT 10;
The following lists all collation and character sets of all tables on all databases.
SELECT
CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) table_name,
CHARACTER_SET_NAME, TABLE_COLLATION
FROM INFORMATION_SCHEMA.TABLES
INNER JOIN INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
ON (TABLES.TABLE_COLLATION = COLLATION_CHARACTER_SET_APPLICABILITY.COLLATION_NAME)
WHERE
TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA')
ORDER BY TABLE_SCHEMA ASC, TABLE_NAME ASC
Lists active processes. (MySQL > 5.1)
SELECT * FROM information_schema.PROCESSLIST WHERE COMMAND != 'Sleep'
Slow running queries. (MySQL > 5.1) You may kill any processes with the “KILL” command and the ID
SELECT
ID, INFO, USER, HOST, TIME
FROM information_schema.PROCESSLIST
WHERE
COMMAND != 'Sleep' AND TIME > 4
ORDER BY TIME DESC;
Catching Zencart Hack Attempts
So, one morning I noticed quite a lot of 404 errors recording on a client’s web server logs and decided to take a look at them. Turns out that between 12:00am to 4:00am we would get about 4 requests a minute to different paths in the admin folder of our Zencart installation.
Keep in mind that we don’t have an admin folder, as any one from the Zencart community would tell you, ALWAYS RENAME THE ADMIN FOLDER AFTER INSTALLATION!.
So, what did the requests look like? They were calls to known old security issues that have been patched for a while now in the vanilla Zencart installation. They looked like this:
- /admin/sqlpatch.php/password_forgotten.php
- /admin/record_company.php <-- this was fun(scarry?) to see. They were attempting code injection
So what can we do with the knowledge that we are getting hack attempts on the old(non existant) admin folder?
Simple, record their IP addresses. Then block them at the firewall level(if you like). Here is a simple setup of a php file that will record the requests attempts in the admin folder, then you can review those records and figure out if you want to ban any IPs or not. We’ll use a .htaccess file with mod_rewrite module to redirect all requests in the admin folder to a single php script.
This is the script that will record all the request attempts: /admin/hackthis.php
<?php
//error_reporting(E_ALL & ~E_NOTICE);
$toEmail = false;
$fromEmail = 'noreply@mydomain.com';
$sendEmailAlert = false;
function getClientIP() {
// taken from: http://www.kavoir.com/2010/03/php-how-to-detect-get-the-real-client-ip-address-of-website-visitors.html
$test = array(
'HTTP_CLIENT_IP',
'HTTP_X_FORWARDED_FOR',
'HTTP_X_FORWARDED',
'HTTP_X_CLUSTER_CLIENT_IP',
'HTTP_FORWARDED_FOR',
'HTTP_FORWARDED',
'REMOTE_ADDR');
foreach ($test as $key) {
if (array_key_exists($key, $_SERVER)) {
foreach (explode(',', $_SERVER[$key]) as $ip) {
if (filter_var($ip, FILTER_VALIDATE_IP) !== false) {
return $ip;
}
}
}
}
return "";
}
// move dir to root, so we can include zencart include files
$root = realpath(dirname(__FILE__) . "/../") . '/';
chdir($root);
require($root . 'includes/application_top.php');
// get the attempted url
$url = $_SERVER['HTTP_HOST'] . '/admin/' . $_GET['__url'];
// the ip of the request
$ip = getClientIP();
// lets record the request variables into a json string so we can fit it in a text column
$request_data = json_encode(
array(
'REQUEST' => $_REQUEST,
'GET' => $_GET,
'POST' => $_POST,
'RAW_POST' => file_get_contents('php://input')
)
);
// reverse dns look up of the ip
$host = ($ip?gethostbyaddr($ip):'');
// the request referer, can't really trust it... but we'll record it anyways
$referer = $_SERVER['HTTP_REFERER'];
// quietly record this attempt at retrieving this file. It's probably a hack attempt
$query = "
INSERT INTO hack_attempt
(url, ip, referer, request, host)
VALUES
(:url, :ip, :referer, :request, :host)";
$query = $db->bindVars($query, ':url', $url, 'string');
$query = $db->bindVars($query, ':ip', $ip, 'string');
$query = $db->bindVars($query, ':referer', $referer, 'string');
$query = $db->bindVars($query, ':request', $request_data, 'string');
$query = $db->bindVars($query, ':host', $host, 'string');
$db->Execute($query);
if ( $toEmail ) {
$emailBody = "[$host]$ip Attempted to access admin files!";
$header = 'FROM: ' . $fromEmail . "\r\n" .
'X-Mailer: PHP/' . phpversion();
@mail($toEmail, "[HACK ATTEMPT] $url", $emailBody, $headers);
}
// send out a 404
header("HTTP/1.0 404 Not Found");
// you can modify the rest as you like from here
?>
<!DOCTYPE html>
<html>
<head>
<title>File Not found!</title>
</head>
<body>
<p>File Not Found!</p>
</body>
</html>You can optionally set $toEmail to your email address to get an alert on every request attempt, but that may get out of hand if you are getting bombarded by requests
The sql table looks like this:
CREATE TABLE `hack_attempt` (
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
`url` varchar(512) NOT NULL,
`ip` varchar(64) default NULL,
`referer` varchar(512) default NULL,
`request` text,
`host` varchar(64) default NULL,
PRIMARY KEY (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and the /admin/.htaccess file looks like this:
RewriteEngine On
RewriteRule (.*) hackthis.php?__url=$1 [QSA]
So, from now on you’ll get a record of all IPs trying to access anything in the admin folder. But do remember that this will only work if you had renamed your admin folder to something else. So, the idea here is to catch bots trying to access admin files that don’t exists and block them from ever visiting the site again. You do need to add:
Disallow: /admin/
in your robots.txt file to keep google bot, or other search engine bots from going in that directory. So you should only get misbehaving or malicious bots recorded in your hack_attempt table.
So, where to go from here? Well, you can take those IP addresses and add them to your firewall filters so they can never attempt anything on your server ever again. Some people will argue that IP blocking is harsh and easy to circumvent by using proxies, but what can you really do when seeing blatant hacking attempts from those IPs?
How to offer personalized sms alerts from your site or service
Want to offer personalized sms alerts to your site users? Here is a quick how to.
Receiving instant alerts when an important event happens is always useful. Below is a list of known email sms gateways that you can use to convert an email to a text message:
AT&T: <cell phone number>@txt.att.net
Sprint PCS: <cell phone number>@messaging.sprintpcs.com
Verizon: <cell phone number>@vtext.com
T-Mobile: <cell phone number>@tmomail.net
Virgin Mobile: <cell phone number>@vmobl.com
Nextel: <cell phone number>@messaging.nextel.com
Boost: <cell phone number>@myboostmobile.com
Alltel: <cell phone number>@message.alltel.com
US Cellular: <cell phone number>@email.uscc.net
MetroPCS: 1<cell phone number>@mymetropcs.com
By replacing <cell phone number> with a cell phone number you can send an email to just about any cell phone. Also, you should keep the text as short and concise as possible, as I’ve noticed some carriers(I’m looking at you AT&T) will split longer messages into several text messages, which might be quite annoying.
I use it to receive server temperature alerts at work when our server room starts getting too hot, which should never happen under normal circumstances but does happen when AC fails to cool the room on hot summer days.
You can also use it to offer personalized sms alerts for your website services and such. But you’ll either have to ask your users for what cell phone service they use or send the email to all the gateways at once.
Another use is to forward your google alerts to your cell phone. You can setup a filter in your google email account that will forward all google alerts to your cell phone. This is an excellent way to keep up to date on your website SEO, if you are tracking which websites mentions your domain, blog or whatever.
If you have a shopping cart, you could also offer to send a text message to your customers when the item(s) they purchased has been shipped to them. That’s just another little way to wowing your customers I think.
Here is Wikipedia’s expansive list of known SMS Gateways: http://en.wikipedia.org/wiki/List_of_SMS_gateways.
Now go find ways to flood your cell with alerts, and let me know of other cool ways to use sms. 
Quick and dirty Zencart and Drupal theme integration
So, recently I had to find a way to combine a zencart shopping cart and a drupal blog site into one website. There are no off the shelf ways of doing this that I could find so I wrote my own.
What I did to make my life easier was have drupal “fit” itself around the zencart template. That way any changes that need to be done to the site template, can be done to zencart only, and drupal would just mirror the changes.
The way to achieve this is to have zencart serve an empty page(not blank!) that my drupal installation could scrape on demand and use as a template. Then on the zencart template, I strategically placed html comments marking beginnings and endings of blocks that drupal could parse out and place it’s content around. Below we have an example of such a setup. Imagine this snippet comes from the empty zencart page:
< html>
<!– bof-head_tag –>
<head>
<!– eof-head_tag –>
<!– bof-title –>
<title>Zencart page title</title><!– eof-title –>
<!– bof-zc_meta–>
<!– eof-zc_meta –!>
</head>
<!– eof-html_top –>
<!– bof-body_top –>
<body>
<!– eof-body_top –>
<!– bof-body_bottom–>
</body>
<!– eof-body_bottom–>
</html>
The example is very simple and not what a site template should look like, there would be other tags there placing columns and such, but for the purpose of this example it will do. The html comments in bold above can be replaced in drupal with any of the drupal content needed to make the template work there. For example, the <!– bof-title –> <!–eof-title –> pair are replaced with the drupal title content instead of keeping the zencart title. This way we can pretty much reconstruct any zencart template in drupal by either replacing the comment pairs or selectively rendering the content of the pairs as we need them on the drupal template. Do note that to make life easier I use the Zen Drupal theme. It makes this whole process much, much cleaner and nearly trivial( if you know what you are doing ).
As an example, here is the site of the client that needed the zencart/drupal template integration: http://www.monkeyhydro.com, If you click on the blog link, drupal loads and scrapes the empty page and places its blocks where required.
I did have to add a few other things to the setup, like account log in and shopping cart item count. This works by fetching the info using ajax from zencart. That’s just a little json web-service that gives drupal enough info to reconstruct the links on the top right corner. Since there was no need(at the moment) for drupal accounts to be synced or integrated to drupal, there is no account login for drupal. That may come in the future still…
A full example may take a little time to get ready, but will soon come. I will package the zencart webserivice and drupal parsing code for the zencart template. Once that’s done I’ll be posting it here.
Also, as a side note. This process can be also done in reverse, where the site is drupal centric so the main template is maintend in drupal and zencart does the scraping and fitting. I’ve also done this once but does prove to be a bit of longer process since zencart templates are spread through so many template files.