Brandon Checketts

Web Programming, Linux System Administation, and Entrepreneurship in Athens Georgia

Category: Programming (Page 3 of 6)

Don’t Use Integers as Values in an Enum Field

July 25, 2008 / / 0 Comments

I just got through fixing a messy problem where a database had a table defined with a couple columns that were ENUM’s with integer values.   This leads to extreme amounts of confusion, because there is a lot of ambiguity when doing queries whether the integer is supposed to be treated as the enumerated value, or as the key.

Imagine a table with a column defined as ENUM(‘0’, ‘1’, ‘2’, ‘3’).  When doing queries, if you try to do anything with that column, it is unclear whether you mean to use the actual value you pass in, or the position.  For example, if I as to say ‘WHERE confusing_column = 2’, it could be interpreted as either meaning the value ‘2’, or the item in the second position (ie; ‘1’).    It is even hard to explain because it is so confusing.

The MySQL Documentation does a decent job of explaining it.   I agree with their recommendation:

For these reasons, it is not advisable to define an ENUM column with enumeration values that look like numbers, because this can easily become confusing.

I ended up converting everything to Tinyint’s. It takes a few more bits per row, but worth it in my opinion to avoid the confusion.

MyTop Stops and Beeps on When a Query Contains Binary Data

July 15, 2008 / / 0 Comments

MyTop is a handy utility for watching the queries being executed on a MySQL server from a terminal window.   It is written in Perl, and is pretty straightforward.  It just does a ‘SHOW FULL PROCESSLIST’ on the database, and then displays the currently running queries.   You can sort by various columns, and in generally is just tons easier than running SHOW PROCESSLIST from the MySQL command prompt.

My database does some inserts that contain binary data.  I noticed that when running mytop, and one of those queries came up, the terminal would beep and it would stop and prompt me to enter something.

To resolve, I added this to about line 970 so that it filters out most non-displayable characters.   Feel free to let me know a better regex to use.  This one is pretty ugly, but works for now. (Also, wordpress might have mangled some of the slashes)

## Try to filter out binary information and still provide all of the necessary detail
$thread->{Info} =~ s/[^\\w\\d\\s\\(\\)\\[\\]\\-\\;\\:\\'\\"\\,\\.\\<\\>\\?\\/\\\\\\\*\\~\\!\\@\\#\$\\%\\^\\&\\*\\-_\\+\\=\\` ]//g;

Poor Performance After Enabling Repliction Due to sync_binlog

July 15, 2008 / / 1 Comment

I was pretty happy with myself with setting up some fairly complicated MySQL circular replication the other night.  I did it far after peak hours so as not to disturb any visitors if it caused any problems.   Everything appeared to be working great until I started watching things the next morning.

I started to notice that the main MySQL server seemed to be running really slow.   One process that we have usually completes in a couple hours, ended up taking well over 16 hours to complete.   I spent the whole day troubleshooting it, which got me familiar with all sorts of handy tools.   ‘mytop‘ is a handy version of ‘top’ for MySQL queries.  I got familiar with iostat for watching disk I/O performance.

In the end, after a whole day of troubleshooting it came down to the ‘sync_binlog‘ setting that I had enabled because I read some howto that mentioned it was useful for the replication master.  My understanding now of the setting is that it causes the operating system to tell the disk to sync the file to disk after each write to the binary log (every UPDATE, INSERT, or DELETE).   The idea is that when the data is sync’d to disk, the drive physically writes it to the drive, instead of keeping it in a cache.    My application does a ton, of inserts, so it was killing performance.

Checking MySQL Replication

June 23, 2008 / / 3 Comments

MySQL replication is pretty easy to set up, but needs a few extra things to make it more reliable. I wrote this quick PHP script to alert me when replication has failed and is more than 5 minutes behind the master.

<?php

$user = 'username';
$pass = 'password';
$host = 'localhost';
// Grant this user permission to check the status with this mysql statement
// GRANT REPLICATION CLIENT on *.* TO 'user'@'host' IDENTIFIED BY 'password';

$threshold = 300;

$db = mysql_connect($host, $user, $pass);

$result = mysql_query('SHOW SLAVE STATUS');
if (!$result) {
    // Make sure that your user has the 'REPLICATION CLIENT' privlege
    echo "Error 'SHOW SLAVE STATUS' command failed\n";
    echo mysql_error()."\n";
    exit(1);
}

$status = mysql_fetch_array($result);

if (!isset($status['Seconds_Behind_Master'])) {
    echo "Error: Seconds_Behind_Master column not found in result\n";
    print_r($status);
    exit(2);
}

if ($status['Seconds_Behind_Master'] > $threshold) {
    $minutes = floor($status['Seconds_Behind_Master'] / 60);
    echo "Error: Slave is $minutes minutes behind the master server\n";
    exit(3);
}

exit(0);
?>

This script is intended to be run periodically from cron. It doesn’t generate any output unless something is wrong. The behavior of cron is that when a script generates output, it will email the output to the user, so make sure that you have mail on your system configured to send you the cron output correctly. The script also exits with a non-zero status on each error, so you might include this in a more complicated script that attempts to do something else based on the status.

I use something like this in a non-privileged user’s crontab:

*/15 * * * /usr/bin/php /path/to/check_replication.php

bcSpamblock Updated to Version 1.3

May 14, 2008 / / 0 Comments

Thanks to jontiw for pointing out a potential problem in my bcSpamblock code.  He noted the the PHP crypt() function returns the salt along with the encrypted value.  My code was passing the salt to the visitor so that an attacker could potentially learn the salt value that a website was using and create valid responses.

I modified the code to strip out that salt before passing it to the user.  I also modified the data used to create the salt so that previous vulnerable version doesn’t use the same value for the site.  The wordpress plugin has also been updated as well.

I was happy to see other people looking through my code and pointing this type of issue out.

Problems to Anticipate When Upgrading From PHP4 to PHP5, and MySQL4 to MySQL5

March 19, 2008 / / 0 Comments

A client website just upgraded from PHP4 to PHP5 and MySQL4 to MySQL5 and completely broke. Doing such significant upgrades should have been tested first, but for some reason didn’t happen. I got invited to fix and ran across several problems:

MySQL queries containing some explicit JOINs broke. A simple query like this doesn’t work in MySQL5:

SELECT table1.*, table2.*
FROM table1, table2
LEFT JOIN table3 on table1.col1 = table3.col1

In MySQL 5, the JOIN operator now has a higher precedence than the comma operator, so it interprets the query differently. See this post or the MySQL documentation for more information. The quick fix is to put parenthesis around the tables in the FROM statement, like this:

SELECT table1.*, table2.*
FROM (table1, table2)
LEFT JOIN table3 on table1.col1 = table3.col1

The other significant problem was in the upgrade from PHP4 to PHP5, the XML parsing functions are completely different. PHP 4 used the domxml extentions, where PHP 5 uses a newere DOM extention.

From https://www.php.net/manual/en/ref.domxml.php:

It will, however, never be released with PHP 5, and will only be distributed with PHP 4. If you need DOM XML support with PHP 5 you can use the DOM extension. This domxml extension is not compatible with the DOM extension.

The solution for fixing this, however is quite a bit more complicated. I had to rewrite the XML producing scripts to use the new functionality. Fortunately, the new DOM functionality is pretty straightforward and easier to write, so porting it from one to the other is fairly straightforward, but does require some effort.

GnuPG Encryption with PHP

February 26, 2008 / / 17 Comments

I found PHP’s documentation on the GnuPG functions to be pretty sparse, so thought I would share some specific steps that I went though in order to get everything working.

Prerequisites

First off, you have to install the GnuPG PHP libraries through pecl. It requires the GnuPG Made Easy (gpgme) packages to get working. The following shell commands will install the OS packages, install the GnuPG PHP libraries, then enable the PHP extension and restart Apache:

# apt-get install gnupg gpgme gpgme-devel

# pecl install gnupg

# echo extension=gnupg.so > /etc/php.d/gnupg.ini

# apachectl restart

Creating GnuPG Keys

Next, you need to create a set of keys to encrypt and decrypt your data. You’ll need to put the keys somewhere where the webserver can read and write to a directory. I’ll use /var/www/.gnupg since that is the default home directory for many Apache installations. After running the gpg command, answer the questions as prompted. User input is red in the output shown below.

# mkdir -p /var/www/.gnupg

# gpg --homedir /var/www/.gnupg --gen-keygpg
WARNING: unsafe permissions on homedir `/tmp/keys'

gpg (GnuPG) 1.4.5; Copyright (C) 2006 Free Software Foundation, Inc.
This program comes with ABSOLUTELY NO WARRANTY.
This is free software, and you are welcome to redistribute it
under certain conditions. See the file COPYING for details.
gpg: keyring `/tmp/keys/secring.gpg' created
gpg: keyring `/tmp/keys/pubring.gpg' created
Please select what kind of key you want:
   (1) DSA and Elgamal (default)
   (2) DSA (sign only)
   (5) RSA (sign only)
Your selection? 1
DSA keypair will have 1024 bits.
ELG-E keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048) 2048
Requested keysize is 2048 bits
Please specify how long the key should be valid.
         0 = key does not expire
      <n>  = key expires in n days
      <n>w = key expires in n weeks
      <n>m = key expires in n months
      <n>y = key expires in n years
Key is valid for? (0) 10y
Key expires at Fri Feb 23 16:35:14 2018 PST
Is this correct? (y/N) y
You need a user ID to identify your key; the software constructs the user ID
from the Real Name, Comment and Email Address in this form:
    "Heinrich Heine (Der Dichter) <[email protected]>"
Real name: Some User
Email address: [email protected]
Comment: This is a key for Some User
You selected this USER-ID:
    "Some User (This is a key for Some User) <[email protected]>"
Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? o
You need a Passphrase to protect your secret key. Enter your passphrase here
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
gpg: /tmp/keys/trustdb.gpg: trustdb created
gpg: key 21CCC3D6 marked as ultimately trusted
public and secret key created and signed.
.... a bunch of random characters here....
gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0  valid:   1  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 1u
gpg: next trustdb check due at 2018-02-24
pub   1024D/21CCC3D6 2008-02-27 [expires: 2018-02-24]
      Key fingerprint = FA45 1EE9 8772 70EF 1CFA  99CE 048A 6139 21CC C3D6
uid                  Some User (This is a key for Some User) <[email protected]>
sub   2048g/A83E754B 2008-02-27 [expires: 2018-02-24]
#chown -R apache:apache /var/www/.gnupg

Make note of the key fingerprint in the 4th from the bottom line. You’ll need this in your PHP code when referencing the key. Also, make sure that you write down your pass phrase somewhere. Your encrypted data will be useless if you don’t have the pass phrase.

Your Application

Now you can write your PHP code that will do the encryption. Here is a sample that encrypts, then decrypts something:

<?php
$CONFIG['gnupg_home'] = '/var/www/.gnupg';
$CONFIG['gnupg_fingerprint'] = 'FA451EE9877270EF1CFA99CE048A613921CCC3D6';

$data = 'this is some confidential information';

$gpg = new gnupg();
putenv("GNUPGHOME={$CONFIG['gnupg_home']}");
$gpg->seterrormode(GNUPG_ERROR_SILENT);
$gpg->addencryptkey($CONFIG['gnupg_fingerprint']);
$encrypted =  $this->gpg->encrypt($data);
echo "Encrypted text: \n<pre>$encrypted</pre>\n";

// Now you can store $encrypted somewhere.. perhaps in a MySQL text or blob field.

// Then use something like this to decrypt the data.
$passphrase = 'Your_secret_passphrase';
$gpg->adddecryptkey($CONFIG['gnugp_fingerprint'], $passphrase);
$decrypted = $gpg->decrypt($encrypted);

echo "Decrypted text: $decrypted";
?>

It would be best to store $passphrase somewhere completely separate from your application configuration. Perhaps an admin user would be required to enter the passphrase when looking up this information. That way your passphrase is not stored in your config file or anywhere that an attacker could potentially gain access to it.

Troubleshooting

Make sure that the web server can write to the GnuPG Home directory. This obviously is not ideal, but seems to be required in the testing that I have done. I’ve been able to set ‘secring.gpg’ to be owned by root, but that does little good since the directory it is in has to be writable.

You can raise the error mode to GNUPG_ERROR_WARNING to generate PHP warnings on GnuPG errors. That might help to track down where errors are occurring

Amazon ECS 3.0 is shutting down

February 5, 2008 / / 0 Comments

Amazon’s long lived ECS 3.0 will be shutting down soon. This was an early version of their API that allowed 3rd party applications to access Amazon’s vast database of books and products. It was very widely used, and it will be interesting to see what kind of impact it has when they turn it off for good on March 31st. I’m sure there are plenty of small sites that will break in some way when they turn it off.

From what I’ve seen, they have been pretty good at notifying customers who are still using it. I’ve gotten several emails about it in the past couple weeks. Despite all of their efforts, though, I’m sure that there has got to be all kinds of small sites that were written at one point and haven’t been touched since.

The easiest way to tell if you have a site that uses it, is to grep through all of your code for ‘AssociateTag’ or ‘SubscriptionId’. Those are the authentication parameters used by the 3.0 version that it shutting down. The newer version of ECS uses a ‘AWSAccessKeyId’ parameter instead.

If you have a PHP or Perl-based website that needs to be upgraded to the new version, you can hire me to fix it.

Performing post-output script processing in PHP

January 18, 2008 / / 5 Comments

After several hours of researching end experimenting, I think I finally came up with a way for a PHP script to display a page, close the connection to the browser, and then to continue processing. The idea is that I can add some potentially lengthy processing to the script by executing it after the browser has closed the connection, but to a visitor, the page appears to load quickly.

I experimented with PHP’s register_shutdown_function, but that doesn’t really do what I need (unless running < PHP 4.0.3). Evidently PHP doesn’t have any way to close STDOUT, like other languages do.

The trick is in sending a Connection: close and Content-Length header. Once a client has received the specified number of bytes, it will close the connection, even though the script may continue. Unfortunately, that means that you need to know the length of the page before displaying it. That can be handled with output buffering, but does make the solution less than ideal.

Here is an example that works for me using PHP 5.1.6.

<?php

$start_time = microtime(true);
function bclog($message)
{
    global $start_time;
    $fh = fopen('/tmp/logfile', 'a');
    $elapsed = microtime(true) - $start_time;
    fwrite($fh, "$elapsed - $message\n");
    fclose($fh);
}

header('Content-type: text/plain');
header('Connection: close');
ob_start();

for ($i = 0; $i < 1024; $i++ ) {
    echo "#";
}
bclog("I'm done outputting my normal content");

// Figure the size of our content
$size = ob_get_length();
// And send the content-length header
header("Content-Length: $size");

// Now flush all of our output buffers
ob_end_flush();
ob_flush();
flush();

sleep(5);
bclog("Now I'm done with all of my post-processing - FYI, content length was $size");
?>

If you hit that page in a browser, you will notice that the browser displays the content and is done right away. However, you can tail that logfile, and see something like this:

0.0002360343933 - I'm done outputting my normal content
5.0019490718842 - Now I'm done with all of my post-processing - FYI, content length was 1024

It is not an ideal solution, but I think that is about as good as it is going to get

« Older posts Newer posts »

© 2025 Brandon Checketts

Theme by Anders Noren — Up ↑