This is TikiWiki v1.9.7 -Sirius- © 2002–2005 by the Tiki community. Mon 06 of Sep, 2010 [23:34 UTC]
|
Last blog posts
Login |
Blog: My-Ess-Que-Ell
Created by admin on Tue 21 of Mar, 2006 [18:32 UTC]
Last modified Fri 30 of Jun, 2006 [21:58 UTC]
Description: Personal notes on life at MySQL
Adaptive Firewall to help with malicious SQL injection?
A few weeks ago, I blogged about an adaptive firewall on my NetSec blog.
While casually checking through the access logs, I noticed that someone had tried to get admin passwords of my wiki site using SQL injection. Here is the actual hacking attempt: 41.250.10.3 - - [28/Jun/2006:04:42:14 -0700] "GET /tiki-usermenu.php?find=&offset=[SELECT%20uid%20FROM%20admins%20WHERE%20login=''%20OR%20'a'='a'%20AND%20password=''%20OR%20'a'='a'] HTTP/1.1" 200 123 "-" "Mozilla/5.0 (Windows; U; Windows NT 5.1; fr; rv:1.8.0.4) Gecko/20060508 Firefox/1.5.0.4" Someone from Africa (looking at the IP number), trying to get the list of admin accounts without passwords. So, it occurred to me, that you could easily add a few more regexp triggers to an adaptive firewall, to catch SQL injection attacks, and thus either block or alert on SQL injections... You would basically build some patterns to detect SELECT-FROM or UPDATE-SET-WHERE clauses, obviously case insensitive. While an interesting idea, and perhaps of value to people who want to pro-actively guard potential/possible holes, this does also have some drawbacks:
I offer this as an idea, perhaps I will augment my adaptive firewall scripts, and publish revised version on the NetSec? blog.
Fifty lines of Perl to protect against web site defacing
I was following conversation on LogAnalysis mailing list (LogAnalysis@lists.shmoo.com) and one of the members suggested that finding out if a web site is defaced is one of the valuable things to find out.
Realizing, that all my hosted web sites are stored in a MySQL table (for automated management) — what would it take to add the functionality? Just add one column for the main page "page TEXT" (and a flag "verify CHAR(1)" in case you don't want checking on a domain)... and add 50 lines of Perl... !/usr/bin/perl
# NOTE: Your wget needs to be at least v1.8 to handle PHP based pages
# NOTE: Could use Algorithm::Diff -- if it was more widely available
use strict;
use DBI();
my $debug = 0;
# Connect to sites database
my $dbh = DBI->connect("DBI:mysql:database=floyd;host=localhost", "USERNAME", "PASSWORD", {'RaiseError' => 1});
# Query out of the sites database all proper domains (not subdomains), that have verify flag set to yes
my $sth = $dbh->prepare("SELECT domain,page,contact FROM sites WHERE verify = 'Y' AND domain NOT LIKE '%.%.%'");
$sth->execute();
while (my $ref = $sth->fetchrow_hashref()) {
my $domain = $ref->{'domain'};
$debug && print "$domain\t";
my $page = `wget -q -O - $domain`;
my $orig = $ref->{'page'};
if ($orig) {
if ($orig eq $page) {
$debug && print "Complete match\n";
} else {
open(OUTFILE1, ">/tmp/verify1.$$.txt") or die "Can't open /tmp/verify1.txt: $!";
print OUTFILE1 $orig;
close(OUTFILE1);
open(OUTFILE2, ">/tmp/verify2.$$.txt") or die "Can't open /tmp/verify2.txt: $!";
print OUTFILE2 $page;
close(OUTFILE2);
my $res = `diff -abBdw /tmp/verify1.$$.txt /tmp/verify2.$$.txt`;
my $count = () = $res =~ /\n/g;
$debug && print "CHANGE LINES: $count\t\t";
if ($count {'contact'}\nCc: root\nFrom: root\n";
print MAIL "Subject: Warning: your site $domain index page has changed substantially ($count)\n\n";
print MAIL "diff of the original page:\n$res\n";
close MAIL;
}
}
} else {
$debug && print "No imprint -- storing initial\n";
$dbh->do("UPDATE sites SET page = " . $dbh->quote($page) . " WHERE domain = " . $dbh->quote($domain));
}
}
unlink("/tmp/verify1.$$.txt");
unlink("/tmp/verify2.$$.txt");
$sth->finish();
$dbh->disconnect();Add into your crontab, and you're set... MySQL table (text column) is such an easy place to store an imprint of the index page... Perl is ideal for parsing the text, wget(1) for pulling the page, and diff(1) for diff'ing the pages (though I could have used the embedded Perl equivalents — but I wanted to just put this quickly together, instead of romping around in CPAN). Once again happy to have all my data (and now some more) in a MySQL database. This blog entry will also appear in my Network Security (NetSec) blog with more details.
Free hot backups for MySQL on Linux
"Hot Backups" of your MySQL Database on Linux
Would you like to take consistent snapshot backups of your entire database without a SAN or external software, and without slowing or locking your database for the duration? RedHat? Enterprise Linux (and Centos Linux) install an LVM by default. LVM, Logical Volume Manager is a layer that sits on top of any file system (default ext3). You can tell if you're already running on an LVM, by checking your file system with "df -h" — an entry like "/dev/mapper/VolGroup01-LogVol00" shows that your disk is mapped through the LVM. LVM brings you features such as the ability to grow and shrink volumes, add hard disks without migrating data, RAID0, and for the purpose of this article: file system SNAPSHOTS. FLUSH TABLES WITH READ LOCK; \! lvcreate --size 100m --snapshot --name snap /dev/VolGroup01/LogVol00 UNLOCK TABLES; Will lock and flush all tables in the current database to disk. As soon as lock and flush is acquired, a snapshot called "snap" is created (instantaneously), and table locks are released. You can use lvdisplay(8) to look at active volumes. Remember, that you must have dm-snapshot.ko module loaded (modprobe dm-snapshot) Next mount the new snapshot volume, backup it up, and release the snapshot mkdir /snap mount /dev/VolGroup01/snap /snap # This is where you back up whatever you need from /snap, I prefer using rsync(1) umount /snap lvremove /dev/VolGroup01/snap rmdir /snap While you back up the snapshot, your database is running its normal business, slowing down only as much as you read from the file system, i.e. the disk access is the only slowing factor — meaning that you can back up at file system speed! Snapshotting is also a handy undo feature, when doing schema changes (ALTER TABLE), or other database maintenance work, such as upgrades.
Sysadmin style hack: versioned database backup
Really Versioned Database Backup...
...Or abusing a version control system I've always liked to keep my /etc configuration files in a source control system, since it both helps me document changes, as well as quickly roll back to an earlier date. Then, I implemented some systems, such as web hosting and email configurations to reside inside a MySQL database, not to mention also some TikiWiki? sites. Now, I wanted to have a versioned backup of the database... something that I could just dial to an arbitrary date, as well as see the changes. How about mysqldump combined with p4 (Perforce) — Yes, I know subversion, cvs and bitkeeper might be polically more correct — but while I use all of them, I'm still fastest with p4, and it is available for free for up to 2 users and 5 clients. So, here's a really simple script (use multiple mysqldump statements for multiple databases): p4 edit *.sql DESC=`date +%y%m%d-%H:%M:%S` mysqldump tiki >tiki.sql p4 change -o | sed s/\/$DESC/ | p4 submit -i Put that in a file, call it from cron(8) once a day and you're set. On the first time, you need to produce the .sql files from mysqldump, and then add them to the depot with p4 add *.sql NOTE: If you want versioned backups for real, you should build it into your application — this is just a hack — though, it does give you awesome compression (p4) and a wealth of tools, with only four lines of script. NOTE: This is ok for databases/tables up to hundreds of megabytes, but I wouldn't use this for gigabyte size tables.
Fast Databases, like really fast, not percentages, but orders of magnitude?
Fast Database App = Hybrid Database App
Ok, so you're trying to figure out which transactional general purpose database engine is the fastest to build/deploy your app with? Now, since all engines (in the last 10 years) are MVCC, ARIES, ACID... it's going to be hard to find a database (or database storage engine) that is a head and shoulder above the rest... You can find, perhaps up to a 2:1 ratio, for a specialized situation. Remember now, TPC benchmarking was invented to standardiz e the test, while factoring in the cost of hardware — which levels the playing field of specialized hardware vs. unusual performance. On the other hand, you might find a database, that outperforms in a particular niche table design due to a specialized indexing mechanism. What does all this specialized hardware, indexing or such buy you? It will buy lock-in... you are going to end up with a very special app (= non-portable), or very special hardware, ... many ways to say, you painted yourself in a corner. This sort of dawned to me, when thinking of the MySQL Pluggable Storage Engine Architecture... realizing how there can be many "me-too" storage engine, all general purpose, all within 1:2 performance ratio, and all... yawn... The real trick to a killer database app lies in picking a hybrid model. One where you think twice for each table, where (which engine) do they belong to. Since MySQL allows you the choice of different engines, with different semantics... and soon many more engines (come see us at Users Conference). For logging... ah, but that's so expensive, I'll only log the most critical info, and I'll purge (or offline) the tables as soon as I can... NO! Use the Archive Engine — it's cool! You can store as much data as you want, the compression is a killer. You can put 10x more data because of the compression... you can put another 10x more data because you don't pay the transaction speed cost! That's 100x! For really fast lookups... hey, the data in lookups changes rarely. Think ILM, think memory based storage. Use either the HEAP engine, or Cluster! All of a sudden the disk goes out of the equation, all of a sudden you have 1,000x the speed, and no worries about caching, disk speed, or anything similar! Doesn't 1,000x sound good? You're writing the great web site, you have big images, perhaps even music clips, or movie clips... BLObs to the tune of megabytes. Does it really make sense to put that in the standard transactional general purpose storage engine, when you could benefit from the fact, that the BLObs practically never change, that all you need is shove them out fast, that you might even get extra kick from having full text search? Use MyISAM storage engine... Fast, efficient, no overhead, full text search... All data is not created equal. Not all storage engines are create equal, either. Use the right tool for the right purpose. Legacy databases... (repeat after me) If the only tool you know is a hammer, then all the problems start to look like nails...
Pluggable Storage Engines, and the future of databases
Not very subtle title?
But look at databases, and applications using databases... You've got your databases, tables and columns. Exciting? Or yawn... Think of data, not by columns and type, but rather how it lives. - Some data participates in "business logic" — what to show, how to show, related to other tables, etc. - Some data changes often, some is literally static - Some data needs to remain static, try Sarbanes-Oxley?, or J-SOX the Japanese counterpart - Some data is small, but some is large (music, movies, pics) - Some tables need to be transactional, some you couldn't care less So, what — just cram it all in a database...? Consider this — for each of the examples above, there is a penalty you pay, either in storage or performance for your choice. What if you could choose a different set of semantics for each of the tables in your app? (I am assuming, that you are building your app on multiple tables...  )This is, what MySQL's Pluggable Storage Engine Architecture is all about! On a per-table basis, you can choose how you store your data, and what is the nature of the data. The benefits can be significant when building your portal or departmental application, but when you start to build an enterprise application, where you might have more data to store (to the tune of gigabytes, even terabytes), these issues start to really matter. In the upcoming MySQL Users Conference 2006, there will be at least two session discussing these topics. - http://www.mysqluc.com/cs/mysqluc2006/view/e_sess/8029 Tutorial for storage engines - http://mysqluc.com/cs/mysqluc2006/view/e_sess/9234 MySQL Strategy for storage engines I will be writing more as the user conference approaches...
MySQL — or, as I was frequently instructed, you spell it My-Ess-Que-Ell?...
Something everybody learns when starting at MySQL — though few as forcefully as I learned, when staying my first few weeks with Monty. Ok, so I'm no spring chick anymore... it took me two weeks to learn the mantra, and now I have been assimilated. But, look at it from the lighter perspective — if that was the hardest part — then the rest is really easy. If you can handle more Monty stories, check out the interview: http://dev.mysql.com/tech-resources/interviews/taneli-otala-mysql.html But, what I really want to write about in this blog is about databases, or data management — future trends, how to make some entirely new and efficient applicatiions without changing too much. It's all about understanding the nature of data, not all data "being created equal" nor should it be accessed or stored equal. Watch for new entries...
Page: 1/1
1 |
![[hide]](javascript:icntoggle('mod-mnu_application_menu','mo.png');)
