Skip to content

Generic MySQL get/set functions with PHP

Sometimes it’s very useful to be able to check whether a particular value exists within a MySQL database, or to retrieve a particular single value from the database based on given criteria. Examples being:

1) Check whether user with user_id = 4 is a valid user
2) Get the username associated with user_id = 6

Whilst it’s very simple to write code to do such a thing, such code becomes very reptetitive. Checking whether a user exists is very similar to checking whether another object exists (for example a customer, a profile, an email account). Retrieving a user’s full name is exactly the same as retrieving a user’s email address, bar a column name.

Having been writing a simple knowledgebase application recently, I came across these problems:

1) Does this article exist?
2) Does this article category exist?
3) Does this author exist?
4) What is the title of the article with id = 11
5) What is the name of the category with id = 16

Each of the above would require individual SQL statements to retrieve the desired information, though may be grouped into two categories:

1) Does ‘x’ exist queries
2) Get ‘x’ where ‘y’ queries

So I came up with some simple, generic, get functions. You simply plug in the name of the table, together with the field name and the data you are interested in and the function does all of the hard work for you.

So we have the following:

// Generic 'is this value in the db?' function
function valueKnown($col, $tab, $value, $dblink)
{

	$t = mysql_real_escape_string($tab);
	$v = mysql_real_escape_string($value);
	$c = mysql_real_escape_string($col);

	$sql = "SELECT $c FROM $t WHERE $c = '$v' LIMIT 1";
	$link=runSql($sql, $dblink);
	if(mysql_num_rows($link)==1)
	{
		return true;
	} else {
		return false;
	}

}

You simply call

valueKnown($col, $tab, $value, $dblink);

To be continued...

Debian on Omnima embedded controller

I recently picked up an embedded controller board from Omnima – http://www.omnima.co.uk/… which I’ve been trying to install Debian on.

Having followed the instructions to install Debian that were given by a helpful chap at http://www.omnima.co.uk/forums/index.php?showtopic=154 I’ve had quite a lot of difficulty getting the kernel to boot properly – usually due to a kernel panic – though I think I’ve finally got it working.

It seems that there are a couple of steps missing from these instructions:

1) Make sure that you enable general setup -> Support for paging of anonymous memory (swap) otherwise you’ll have problems with debconf running out of memory at the debootstrap second stage – which is annoying because it means recompiling and reflashing the kernel image.

2) Make sure that you add init=/etc/preinit to the end of the kernel command.  Add a symlink between /etc/preinit and /sbin/init when you create the USB image.

3) When creating /dev/sda you also need to run “MAKEDEV console” otherwise you’ll get kernel panics like this:

Kernel Panic – Not Syncing : Attempt to kill init!

I also got panics along the lines of Kernel Panic – Not Syncing : Attempted to kill the idle task.  Unfortunately I didn’t manage to get to the bottom of those.

Other tips:

1) It seems that debootstrap creates a log file in /debootstrap/debootstrap.log.  Be sure to check this file if you’re having problems at the point of running debootstrap –second-stage.

2) If you are getting errors about running chroot debconf or similar make sure you check the log file (see tip 1 above).  I was having problems due to a lack of memory (because the swap space wasn’t mounted properly).  To check that swap is mounted:

Run ‘free’ determine the size of the swap space.  If the size of swap is shown as zero then it’s not mounted properly.  Try running ‘swapon -a’ to remount swap.

For reference (and for Google) the error I was getting in the log file was:

Setting up debconf (1.5.24) …
open2: fork failed: Cannot allocate memory at /usr/share/perl5/Debconf/ConfModul
e.pm line 59
dpkg: error processing debconf (–install):
subprocess post-installation script returned error exit status 12

You’ve received a postcard – or not

At around 10.00 UTC this morning (28/01/2010) one of the botnets starting spewing “You’ve received a postcard” spam.

The body of the message is:

Good day.

Your family member has sent you an ecard from 123greetings.com.

Send free ecards from 123greetings.com with your choice of colors, words and music.

Your ecard will be available with us for the next 30 days.

If you wish to keep the ecard longer, you may save it on your computer or take a
print.

To view your ecard, open zip attached file.

The MD5 hash of the initial samples is d0b000d06b3d29933d5baa35b155c898.  HoneySigger picked this file up immediately which is referenced as HS-Suspiciousfile-c81e

The VirusTotal report is here:

http://www.virustotal.com/analisis/55c87cbf22f9389cc42fa7e4aa7273bd5cb7592900d79d3f564afea2642342f0-1264675074

At the current time AV detection isn’t great – 11/41 engines are able to detect the virus, most of them look to be heuristic detections.  Distribution of this file also appears to be relatively low – we’ve only seen around 15 copies of this in the past hour.

False positives in ClamAV – Exploit.PDF-9669

At around 23.00 GMT last night (8th January 201o) I began noticing that I was receiving a large number of virus detections for Exploit.PDF-9669.  Initially I thought somebody was mass-mailing a PDF exploit (perhaps CVE-2009-4324 which isn’t going to get patched until next week), though I soon realised that every one was a false positive.

Digging though the daily.hdb signature file I soon found the offending signature right at the end of the file:

d41d8cd98f00b204e9800998ecf8427e:0:Exploit.PDF-9669

This jumped out at me immediately – since ‘d41d8cd98f00b204e9800998ecf8427e’ is a md5 hash of an empty (0 byte) string – i.e. “”.  It seems that this signature was hitting on any 0 byte string of length 0 – i.e. anything.  Unsuprisingly it was false-positives galore.

I immediately deleted the signature from the definitions file and restarted ClamAV to stop any more legitimate mails being blocked and emailed the ClamAV mailing list to alert them to the problem.  Interestingly (according to posts on the list and elsewhere) it seems that this signature only hits on HTML emails rather than plain-text emails.  It also appears that the problem only affected older versions of ClamAV (I’m running 0.92) – though I can’t see how this is the case.

Given that this signature was bound to introduce false positives it is somewhat concerning how it managed to make it into the daily.cvd release.  As I posted to the mailing list I was very suprised that there is no fail-safe built into the signature generation/release process to ensure that any signatures matching 0 bytes are flagged for review – particuarly when combined with the ‘d41d8cd98f00b204e9800998ecf8427e’ md5 hash.

Luckily the virusdb team soon fixed the problem and daily.cvd version 10274 restored things to normal.  Good job.

iPhone iTunes and NOD32 antivirus

In trying to rescue my bricked Iphone I kept running into the problem that iTunes would complete the downloading of the software update, then at 100% complain that

“There was a problem downloading the iPod software for the iPod …… The network connection timed out. Make sure your network settings are correct and your network connection is active, or try again later.”

Having retried the download numerous times, I finally gave up and did some Googling.  It seems that NOD32 antivirus software will scan the download on completion, though will take so long doing so that iTunes gets fed up of waiting and times out.

Solution: Disable NOD32 components Amon and Imon should be sufficient, though you may want to turn off  Emon and Dmon for completeness.  Don’t forget to turn them back on afterwards!

Simple MySQL connection / query wrappers

When doing lots of PHP & MySQL programming, it is always helpful to have some standard functions for accessing databases – functions which do all of the connection stuff, error handling etc for you. You simply pass in the connection details, and it gives you back a handle.

And so this function was born:

// Connect to a database
function database_connect($host, $username, $password, $database)
{

	// Connect to the server
	// the last value '2' means enable a persistent connection
	$link = mysql_connect($host, $username, $password, true, 2);

	if(!$link)
	{
		die("Could not connect to database server");
	}

	// Select the correct database
	if(!mysql_select_db($database, $link))
	{
		die("Could not select database");
	}

	return $link;

}

You simply call

database_connect("database_host", "database_user", "database_pass", "database_name")

The function then either dies with an error message, or returns you a database connection handle

I also have a standard MySQL query wrapper – enabling you to simply uncomment a line of code and debug all of the SQL statements that are being executed in your application:

// Run an sql command
function runSql($query, $db)
{

//	printf("Using Query: %s\n", $query);

	return mysql_query($query, $db);


}

The only time Windows cares about spaces

So I’m trying to get PHP5 running on Windows 2003, IIS 6, with MySQL. Having installed everything and gotten IIS to talk to PHP I’m trying to load the MySQL extension to PHP.

I’ve uncommented the various entries in php.ini, and copied php.ini to c:\windows, yet phpinfo() still isn’t showing the MySQL extension.

So I try running php -m from the command line – which complains about modules not being found. Having moved the various modules out of the ext directory to the PHP root directory we’re starting to get somewhere – together with uncommenting the ‘php_pdo’ extension (f knows what that does mind you).

In the end it turned out that my path environment variable contained a space so instead of:

PATH=….\….\…;c:\php5

I had:

PATH=….\….\…; c:\php5

That has to be the only time Windows every cares about spaces.

So if you’re trying to get PHP working with MySQL and IIS:

1) Check that you have c:\php5 (or wherever) listed on the end of your enviroment PATH variable (right click my computer, advanced, environment variables).
2) Make sure anything to do with MySQL and PHP pdo is uncommented in php.ini
3) Make sure php.ini is in the Windows directory
4) As a final resort copy the various extension dlls into the root of the php installation (i.e. c:\php5)

I hate Windows sometimes!

Detecting GoogleBot and MSNBot with PHP

At work I’m working on a project to publish a large number of subscription only pages to the web, to be indexed by Google, and hopefully drum up some interest in the website. Whilst we want Google to have full access to the documents (to index, not cache them), anybody who isn’t a search engine will have to cough up money to get the full text of the document (hence no-cache).

Having read a couple of articles on the subject it seems that Googlebot and MSNBot (MSN Live search) are quite easy to identify in a secure manner. The following PHP code should happily suffice:

Note that I only care about MSN and Google, find out the necessary details and add in ask.com etc yourself.

Note also that Google have been known to knock search rankings for people who show them a different version of the site than visitors see – use at your own risk!

See also here http://blogs.msdn.com/livesearch/archive/2006/11/29/search-robots-in-disguise.aspx and here http://googlewebmastercentral.blogspot.com/2006/09/how-to-verify-googlebot.html


function isCrawler()
{

	$ua=$_SERVER['HTTP_USER_AGENT'];
	$ip=$_SERVER['REMOTE_ADDR'];

//	printf("User agent is %s
", $ua); // printf("Remote IP is %s
", $ip); // printf("RDNS is %s
", gethostbyaddr($ip)); // If this looks like a crawler if ( (strpos($ua, "Googlebot")!==false) || (strpos($ua, "MSNBot")!==false) ) { // print "Possibly crawler based on user-agent string
"; // Check the reverse DNS $rdns=gethostbyaddr($ip); if( (substr($rdns, -15, 15)==".search.msn.com") || (substr($rdns, -14, 14)==".googlebot.com") ) { // print "RDNS matches ($rdns)
"; // Check that the RDNS and FDNS match up // - somebody may have spoofed an IP to PTR to *.googlebot.com $fwd=gethostbynamel($rdns); if(in_array($ip, $fwd)) { // print "FWD DNS matches
"; return true; } else { // Failed DNS -> RDNS check return false; } } else { // Failed RDNS check return false; } } else { // Failed user-agent check return false; } return false; }

Blocking spam with Exim4 ACL conditions

Our spam/virus filtering platform works extremely well, with very few false positives, and a very small number of false negatives. Being based around the Exim4 MTA, backed onto amavisd-new, together with SpamAssassin, ClamAV, Trawler (http://trawler.sf.net), ContFilter (http://contfilter.sf.net) and HoneySigger (http://honeysigger.sf.net). Whilst we block the majority of spam and virus content, running all of these daemons and processes against every email is very processor intensive.

Having crafted a couple of simple Exim ACLs in the past which detect simple things like HELO spoofing of the servers hostname, I spent a couple of hours adding to these, adding all manner of rules that will turn spam away at the door – rather than having to go through the whole process of unpacking emails and scanning them.

Beforehand, the Exim logs were mostly full of ContFilter warnings “This message was rejected because the banned word ‘viagra’ was found in the message subject”. Following the addition of these new rules, there is very little email reaching the stage of being scanned.

Below is an extract of the current state of my Exim ACL configuration:

warn message = X-sender-localhost: Sender claims to be ‘localhost’ but they are not me!
log_message = Sender MX claims to be ‘localhost’ but they are not!
!hosts = localhost
condition = ${if match{$sender_helo_name}{localhost}{yes}{no}}

deny message = Sender claims to have a local address, but is not authenticated nor relayed (try using SMTP-AUTH!)
log_message = Forged Sender address (claims to be local user [${sender_address}], but isn’t authenticated)
!hosts = +relay_from_hosts
!authenticated = *
condition = ${if match_domain{$sender_address_domain}{+local_domains}}

drop
log_message = Drop connection due to HELO ($sender_helo_name) not being a FQDN (contains no dot) (See RFC2821 4.1.1.1)
!hosts = 127.0.0.1
condition = ${if match{$sender_helo_name}{localhost}{no}{yes}}
condition = ${if match{$sender_helo_name}{\N^\[\N}{no}{yes}}
condition = ${if match{$sender_helo_name}{\N\.\N}{no}{yes}}

warn message = X-Invalid-HELO: HELO is not a FQDN (ends in dot) (See RFC2821 4.1.1.1)
log_message = HELO ($sender_helo_name) is not a FQDN (ends in dot) (See RFC2821 4.1.1.1)
condition = ${if match{$sender_helo_name}{\N\.$\N}}

warn message = X-Invalid-HELO: HELO is not a FQDN (contains double dot) (See RFC2821 4.1.1.1)
log_message = HELO ($sender_helo_name) is not a FQDN (contains double dot) (See RFC2821 4.1.1.1)
condition = ${if match{$sender_helo_name}{\N\.\.\N}}

deny message = Your IP address ($sender_host_address) reverse DNS looks very much like a dynamic dialup/adsl pool. Either your \
mail system is very badly set up or your machine is part of a botnet. Either way I can't accept mail from \
you. Sorry. If you believe this is incorrect please email support@xxx.yyy for assistance.
log_message = Deny connection from $sender_host_address (rDNS is $sender_host_name) which looks like dynamic IP pool (rule 1)
!hosts = +relay_from_hosts
!authenticated = *
condition = ${if match{$sender_host_name}{[0-9][0-9][0-9]?[-\.][0-9][0-9][0-9]?[-\.][0-9][0-9][0-9]?\.[a-z]\{3,\}\.}{yes}{no}}

deny message = Your IP address ($sender_host_address) reverse DNS looks very much like a dynamic dialup/adsl pool. Either your \
mail system is very badly set up or your machine is part of a botnet. Either way I can’t accept mail from \
you. Sorry. If you believe this is incorrect please email support@xxx.yyy for assistance.
log_message = Deny connection from $sender_host_address (rDNS is $sender_host_name) which looks like dynamic IP pool (rule 2)
!hosts = +relay_from_hosts
!authenticated = *
condition = ${if match{$sender_host_name}{(?:[0-9]\{1,3\}[-\.])\{2\}([0-9]\{1,6\})\{1,2\}[-\.]}{yes}{no}}

deny message = Your IP address ($sender_host_address) is listed in SBL-XBL because of spam or exploits being sent from this address. \
We cannot accept mail from this host. Please see http://www.spamhaus.org/query/bl?ip=$sender_host_address \
for more information.
log_message = Deny connection from $sender_host_address which is in SBL/XBL
dnslists = sbl-xbl.spamhaus.org

Just try getting anything through that lot! Please submit any revisions you make to these rules, share the wealth!

UPDATE:

Having installed the above rules on one of our servers, I’ve been comparing the logs throughout the day with another server (in the same load-balanced cluster), that doesn’t have the rules installed. It seems that simple ACL based spam filtering such as this can (and has) reduced the amount of spam being processed by a factor of 10 (i.e. there is 90% less mail hitting spamassassin that there was previously). For sites processing lots of mail, something as simple as this can make a huge difference (think 1 server required rather than 10).

HoneySigger – website now live

The code itself needs some reworking and refactoring before it’s anywhere near release quality, though I have a basic website up and running for the HoneySigger project – http://honeysigger.sourceforge.net  Hopefully I’ll get the code out within the next few weeks.

That said, there don’t need to have been many (any) mass-mailing attacks in the past couple of weeks.  The last file caught by HoneySigger was on January 19th which was 10 days ago as I type this.  No doubt the stuff will start raining down again, particularly given that Valentines day isn’t far away.

In addition, I also have websites set up for my other projects:

http://trawler.sf.net

http://contfilter.sf.net