Archive for the ‘PHP/mySQL’ Category

Posted on September 27, 2008 - by snipe

Planning Your Facebook Application

This is part one of a series - the technical how-to of creating the application will be discussed in a separate article. This article is intended to help you plan out your application to best prepare for coding and best leverage the new aspects of Facebook for exposure and social interaction.

(more…)

Posted on August 13, 2008 - by snipe

Managing registration spam in vbulletin

As the administrator of several forums, I don’t even have words to describe how frustrating forum spam has become. On my photo gallery software site, I had to take down the phpbb forums because the signal-to-noise ratio was just out of control. I had been using phpbb for most of the forums I set up for a while, however one of them had become a constant target for hackers and phpBB always seemed to have vulnerabilities. I decided it was worth my sanity to shell out the cash for vbulletin, and overall I’ve been very happy with that decision.

Of course, I had the same issues with spam as I did with phpbb. On one forum that had been around for many years, I was receiving upwards of 60-100 spam registrations a day. I had changed our forum settings to require my approval before anyone could post, which was great at sparing our users from spam posts about viagra, but was doing nothing to help my sanity. Out of those 60 new registrations, *maybe* one was valid. It got to the point where the sheer volume was overwhelming, so the accounts pending approval started to pile up. Unless the new forum member emailed me directly, they simply never got approved. Not a good way to run a forum, for me or for our users. I was feeling frustrating and cranky, and the users were being neglected and denied the ability to participate. Fail².

When I logged into my vbulletin admin two days ago and saw that there were over 1,000 accounts awaiting my approval (and by way of a quick glance through the list realized that 95% of them were spam), I decided I needed to revisit some anti-spam tactics for vbulletin. I was already using vbulletin’s built-in captcha, and had added the NoSpam! plugin a year or two ago - I’ll go into it more in a moment. NoSpam! definitely helped, but as I was in a rush when I installed it, I didn’t force myself to sit down and come up with a range of good questions.

My goal was to find a solution (or several solutions) for registration spamming, not post-spamming - since logic would dictate that if the users who are able to successfully register are not spammers, you don’t have to worry about post-spam.

My first thought was to see if there was an Askimet system for vbulletin. Askimet does such an outstanding job at keeping Wordpress blog comments spam-free that I thought it would be the perfect place to start. A quick Google search turned up less than stellar news. The reviews on Askimet’s vbulletin port were not great, citing many instances of false-positives, which would ultimately end up creating even more work for me in the long-run, since I’d be fielding user complaints of poists not showing up, etc. The more I thought about it, Askimet wasn’t really the right answer anyway, since it screens only posts, not registrations.

More Googling turned up an excellent blog post by Cormac Moylan, appropriately titled Fighting Spam in Vbulletin, where he goes into detail on several of the available options for fighting spam in vbulletin. The article was from 2006, but there were some products listed that I wasn’t aware, so it was very helpful. He, too, agrees that the Askimet port to vbulletin is not as awesome as its Wordpress flagship. In a similar fashion to his post, I’m going to go through the available products and my own conclusions below.

NoSpam!

This plugin allows you to add an additional barrier to the registration process, where the user sees one of a randomized list of questions YOU define, and they have to type the correct answer into a text box. Spambots have been improving their OCR capabilities over the past several years, so an image captcha alone just doesn’t cut it anymore.

With NoSpam!, you create the questions - and the answers - so you’re able to really control the level of screening you want to implement. A simple math question (2 + 2 = __, with possible answers of “four” or “4″, for example) will be harder for a bot to grok than a basic image captcha. NoSpam! did help, and I recommend it. The fact that it was less effective as time wore on is very likely my own fault, since I stuck with basic math problems. I would expect that if spambots can easily detect and fool image captchas, they are probably capable of detecting basic math prompts these days. I’ve since changed the questions to ones that require an actual human to solve, but still easy enough for new users to get through. For example, for the Wench forums, one of my NoSpam! questions is “Fill in the blank - International _______ Guild.” Still not rocket science, but since the questions are more topical to the forum content itself, its doing a better job.
[download]

Enhanced Captcha Image Verification

I’ve only recently installed this one, but it looks like a great tactic to get around spambots - the demo speaks for itself. Its quite brilliant in its simplicity - four boxes with random images, and text that asks you to select a specific image from the group. Certainly easy enough for a real person to complete, but it will be more of a challenge for bots to figure it out. The install in vbulletin was very easy - upload the images, and then install the product by uploading the xml. Couldn’t ask for a simpler plugin.
[download]

Check Proxy RBL on New User Registration

If a bot gets past the first barriers - the standard image captcha, the enhanced image captcha, and the NoSpam! questions, there is one more line of defense - running the IP address of the registration user through the RBL, or Real-time Black List, databases, to see if it matches any of the known spammer IP addresses. If it finds a match, it deletes the signup and can either alert you by private message or by automatically starting a thread in a designated forum category of your choice.

I have just installed this one, so I’m not able to give you a success rate, however Cormac reported an 80% success rate with no mention of false positives. (Update: see my own updated numbers at the bottom of this post.)

This plugin comes with a small handful of RBL server addresses to check against, but this post on the Anti-Abuse Project site offers quite a few more, including:

bl.spamcop.net
cbl.abuseat.org
dnsbl.sorbs.net
socks.dnsbl.sorbs.net
dul.dnsbl.sorbs.net
http.dnsbl.sorbs.net
smtp.dnsbl.sorbs.net
misc.dnsbl.sorbs.net
dnsbl.njabl.org
combined.njabl.org
zen.spamhaus.org
rbl.spamlab.com
accredit.habeas.com
list.dsbl.org
multihop.dsbl.org
unconfirmed.dsbl.org
dnsbl.ahbl.org
dnsbl.burnt-tech.com
bl.deadbeef.com
dnsbl.delink.net
access.redhawk.org
no-more-funn.moensted.dk
spam.tqmcube.com
ko.tqmcube.com
prc.tqmcube.com
dnsbl.tqmcube.com
ubl.unsubscore.com
psbl.surriel.com
blacklist.spambag.org
combined.rbl.msrbl.net
dnsbl-1.uceprotect.net
dnsbl-2.uceprotect.net
dnsbl-3.uceprotect.net
cblless.anti-spam.org.cn
bl.spamcannibal.org
cbl.ni.bg

[download]

Banning E-mail Addresses and IP Addresses

Although this one seems like a no-brainer to me, I should definitely mention it. Vbulletin comes with the capabilities of banning whole or partial email addresses and IP ranges. I have been cultivating my domain ban list for several years, and you’re welcome to snag it and use it for yourself. (My list is fairly aggressive, so it might not be appropriate for everyone - for example, I don’t allow .ru domains at all, since I know none of our members would have a .ru address. You can gank my list here.

Apache’s mod_security

Another option to prevent post spam is to install Apache’s mod_security. Mod_security is an Apache module that provides intrusion detection and prevention for web applications. It aims at shielding web applications from known and unknown attacks, such as SQL injection attacks, cross-site scripting, path traversal attacks, etc - and has the added benefit of blocking spam posting as well.

Mod_security is basically a series of rules and regexes that Apache runs POST and GET data through. If it finds a match to potentially harmful or spammy information sent to the server via a POST or GET method, it will prevent the form submission from going through, throwing a 500 Internal Server Error message and logging the incident to a file.

Although I am a big fan of mod_security, its not going to be for everyone. If your forums are very active, it can really spike your server’s CPU load. Out of the box, its incredibly restrictive (which is good!), and blocks a lot of false-positives. It takes a while to comb through the incident log and refine the rules so that there is a balance between security and legitimate user-submitted content. This is definitely not for the novice, or for someone who needs a quick fix, but it should be considered as an option. You can find the download here and a tutorial on setting it up here.

Still not perfect

So as we’ve seen, there are some steps you can take in vbulletin that will make a significant difference in the amount of registration-spam you experience. These plugins and techniques are geared at intercepting and blocking spambots, specifically - however it should also be noted that sometimes the spammers actually *are* real people - and unless you’re willing to manually screen and approve every forum registration, there isn’t much you can do about those. Anything you could implement that could confuse them or prevent them from registering are the same things that will prevent your legitimate users from registering.

*** Sept 8, 2008 Update ***

I’ve now been running this plugin for about two weeks, and the RBL New User Registration check has prevented over 200 spam registrations. Registrations that would otherwise have made it through all of the aforementioned checks, since the RBL plugin collects the username, which means the registrant had to have gotten to and completed the registration form.

Over 200 spam registrations blocked, and approximately 10-15 total false positives (which could probably be remedied by removing a few of the more aggressive RBL servers from my list.) I can firmly state that the 10-15 false positives, compared to the 60 spam registrations a day I was getting, falls into the win column. Whitelisting an IP takes just a few seconds, so its not a big deal.

The ultimate outcome - these plugins combined have, for now, allowed me to turn off manual registration approval completely - with ZERO spammers making it through the blockades. My users are happier, and I’m happier.

Posted on July 1, 2008 - by snipe

Identify and Fix SQL Injection Vulnerabilities in Web Applications

Scrawlr is a free software for scanning SQL injection vulnerabilities on your web applications, developed by HP Web Security Research Group in coordination with Microsoft Security Response Center.

Scrawlr crawls a website while simultaneously analyzing the parameters of each individual web page for SQL Injection vulnerabilities.

After the scanning process, if it can find vulnerabilities, it will display your database table names as a proof of the possible SQL injection vulnerabilities.

From the HP Scrawlr website:

Technical details for Scrawlr

• Identify Verbose SQL Injection vulnerabilities in URL parameters
• Can be configured to use a Proxy to access the web site
• Will identify the type of SQL server in use
• Will extract table names (verbose only) to guarantee no false positives

Scrawlr does have some limitations versus our professional solutions and our fully functional SQL Injector tool

• Will only crawls up to 1500 pages
• Does not support sites requiring authentication
• Does not perform Blind SQL injection
• Cannot retrieve database contents
• Does not support JavaScript or flash parsing
• Will not test forms for SQL Injection (POST Parameters)

There are some limitations, as noted in the above bulleted list, however this is certainly a good start to help web developers find and correct vulnerabilities in their applications. Download Scrawlr now - Windows Only.

Posted on June 19, 2006 - by snipe

Creating a Multi-Level Listbox in PHP/mySQL

This lets you create a nested multi-level category menu through PHP and mySQL:

Database:
This code is assuming that you have a database table containing your menu options that looks something like this:

Table categories:
+--------+----------------------+-----------+
| id     | name                 | parent_id |
+--------+----------------------+-----------+
|      0 | Main Category 1      | 0         |
|      1 | Main category 2      | 0         |
|      2 | Subcategory 1        | 1         |
|      3 | Subcategory 2        | 1         |
|      4 | Main category 3      | 0         |
+--------+----------------------+-----------+

It is also assuming that the name of your listbox is “cat_id”. This is easily changed, mind you - you just have to change the select code down at the bottom and the “$categories = $_POST['cat_id'];” line to reflect whatever you’re naming it.

<?php

/* ———————————————- */
/* ———— BEGIN PHP SNIPPET —————-*/
/* ———————————————- */
// $current_cat_id: the current category id number
// $count: just a counter, call it as 0 in your function call and forget about it
/* GET THE DROP DOWN LIST OF CATEGORIES */

function get_cat_selectlist($current_cat_id, $count) {

static $option_results;
// if there is no current category id set, start off at the top level (zero)
if (!isset($current_cat_id)) {
$current_cat_id =0;
}
// increment the counter by 1
$count = $count+1;

// query the database for the sub-categories of whatever the parent category is
$sql = “SELECT id, name from categories where parent_id = ‘$current_cat_id’ “;
$sql .= “order by name asc”;

$get_options = mysql_query($sql);
$num_options = mysql_num_rows($get_options);

// our category is apparently valid, so go ahead…
if ($num_options > 0) {
while (list($cat_id, $cat_name) = mysql_fetch_row($get_options)) {
// if its not a top-level category, indent it to show that its a child category
if ($current_cat_id!=0) {
$indent_flag = “  ”;
for ($x=2; $x<=$count; $x++) {
$indent_flag .= “–> ”;
}
}
$cat_name = $indent_flag.$cat_name;
$option_results[$cat_id] = $cat_name;
// now call the function again, to recurse through the child categories
get_cat_selectlist($cat_id, $count );
}
}
return $option_results;
}
?>

You would call the function using something like this:
<select name=”cat_id”>
<option value=”">– Select — </option>

<?php
$get_options = get_cat_selectlist(0, 0);
if (count($get_options) > 0){
$categories = $_POST['cat_id'];
foreach ($get_options as $key => $value) {
$options .=“<option value=\”$key\”";

// show the selected items as selected in the listbox
if ($_POST['cat_id'] == “$key”) {
$options .=” selected=\”selected\”";
}
$options .=“>$value</option>\n”;
}
}
echo $options;
?> </select>

Posted on June 19, 2006 - by snipe

Checkboxes/Multiple Select Boxes in PHP

For the PHP newbie, checkboxes and/or multiple select listboxes can be baffling in the beginning. It’s actually not very hard at all, and is often one of the PHP newbie’s first experience with arrays.

The logic behind checkboxes and multiple select listboxes is identical. Because of this, we’ll get the HTML bit of it done for both:

A. Checkboxes
<input type=”checkbox” name=”foo[]” value=”1″>
<input type=”checkbox” name=”foo[]” value=”2″>
<input type=”checkbox” name=”foo[]” value=”3″>
(etc….)

B. Multiple Select Listboxes
<select name=”foo[]” size=”4″ multiple>
<option value=”apples”>Apples</option>
<option value=”oranges”>Oranges</option>
<option value=”pears”>Pears</option>
<option value=”grapes”>Grapes</option>
<option value=”mangos”>Mangos</option>
</select>

Getting the data out

Now we just have to write the PHP code that will be able to extract the data the user selected from the array we created ($foo[])

<?php

// check to be sure at least one option was selected
$foo = $_POST['foo'];
if (count($foo) > 0) {
// loop through the array
for ($i=0;$i<count($foo);$i++) {

// do something - this can be a SQL query,
// echoing data to the browser, or whatever
echo “<li>$foo[$i] \n”;

} // end “for” loop

} // endif

?>

Posted on June 23, 2005 - by snipe

More About register_globals

If you’ve been directed to this page, that means that you’re complaining about how your variables in a POST or GET aren’t being carried over to the next page. You’ve been sent here because its an *extremely* common pitfall, and yet one that is exceptionally easy to work around if you know what to do - and also aggravating to explain over and over, hence this page.

What it probably is
99.9% of the time, the webserver you’re running your script on has a configuration option called register_globals turned off.

How to find out

Create a blank document and type the following:

<?php phpinfo(); ?>

Save it as something you’ll remember, and upload it to your webserver. That will print out all your configuration settings for your PHP install. On that page somewhere, find the setting register_globals. I bet you ten bucks it says “Off”.

“Aww crap - it says they are off - what do I do?”
Easy… you have one of a few choices… We’ll start with the most recommended way first. Pay attention. This isn’t difficult, but you need to see what’s going on.

We’ll call the variable in question (you know, the one that isn’t showing up, which is why you’re here) $snipevar just for demonstration purposes. You were likely trying to print out or use your variable by just using $snipevar, which is understandable.

However when register_globals are turned off, you’ll have to call your variable as such:

$_POST['snipevar'], $_GET['snipevar'], or $_REQUEST['snipevar']

You can read more about how each of the predefined variables work by going to the php manual page, located here: http://www.php.net/manual/en/language.variables.predefined.php

The super groovy thing about these reserved variables is that they are superglobal - meaning if you use them in functions, you don’t need to specify global $snipevar; in order for the function to be able to see it anymore. Just use the handy dandy superglobal variables, and it will know their value from anywhere in your scripts.

“Okay - I’ll do that, but just so I know, what are my other options?”
You only have a few options… one is to edit your php.ini file and turn register_globals back on. However if you didn’t know what they did in the first place (if you did, why would you be *here* after all) - I *strongly* suggest you not do that.

Your other possible choice - and this one depends on your server setup - is to stick an .htaccess file in the directory you need to turn globals back on in. Your .htaccess file would look like this:

<IfModule mod_php4.c>
php_flag register_globals on
</IfModule>

IF your server is set up to allow htaccess files to override your main settings, this may work for you. If not, youre shit outa luck, so get used to the superglobals!

Posted on June 28, 2004 - by snipe

Cropped Thumbnails using PHP and the GD Library

This code will allow you to create a thumbnail from a segment of the image. In some situations, you want to thumbnail an entire image - but other times, you may want only a piece - for example if you wish to generate square thumbnail images regardless of whether or not the original image is landscape or portrait style.

The basics are as follows:

1. Resize the image, using the $newthumb size value as the SMALLEST measurement (height in the case of landscape images, width in the case or portrait images). So if you had an original image that is 400 pixels wide and 200 pixels tall, it would set the height to 60 (since 60 is the $newthumb height we’ve used in the example below) and then resize the width down according to whatever ratio is that will contrain the proportions.
2. Trim off the extra so that the end thumb is 60×60.

<?php

$orig_path = ‘/path/to/original/file/’;
$micro_path = ‘/path/to/cropped/file/’;
$imagefilename = ‘myimage.jpg’;

$size = getimagesize($orig_path.$imagefilename);

/**
* new cropped thumbnail sizes
*/
$newthumb_width = 60;
$newthumb_height = 60;

/**
* assign friendlier values to getimagesize data
*/
$orig_width = $size[0];
$orig_height= $size[1];

$width_ratio = ($newthumb_width / $orig_width );
$height_ratio = ($newthumb_height / $orig_height);

if ($orig_width > $orig_height ) {
// this is a landscape image
$crop_width = round($orig_width * $height_ratio);
$crop_height = $newthumb_height;

} elseif ($orig_width < $orig_height ) {
// this is a portrait image
$crop_height = round($orig_height * $width_ratio);
$crop_width = $newthumb_width;

} else {
// this is a square image
$crop_width = $newthumb_width;
$crop_height = $newthumb_height;
}

$source_img = imagecreatefromjpeg($orig_path.$imagefilename);
$dest_img = imagecreatetruecolor($newthumb_width,$newthumb_height);

/**
* if you want to crop from a specific area, for example, if you
* want to crop the image from the middle instead of the top left,
* you’ll need to do some more math to replace the 0,0,0,0 bits here.
*/
imagecopyresampled($dest_img, $source_img, 0 , 0 , 0, 0, $crop_width, $crop_height, $orig_width, $orig_height);
imagejpeg($dest_img, $micro_path.$imagefilename);
imagedestroy($dest_img);
?>

Posted on June 27, 2004 - by snipe

Dynamic Watermarks/Text Overlay on Images in PHP

This code can be useful for a number of things, such as making dynamic banners or for adding a copyright type of watermark to photographs or artwork (as we do in snipe gallery). As usual, this will not work for gifs unless you have a version of gd that lets you do that (cuz the folks at Unisys are a bunch of mo-mos).

The example here is taken from the Godsmack sig generator, so it’s designed to create white text with a black drop shadow on a preformatted blank banner.
Note: Remember that PHP must be compiled with jpeg/png/gd support, AND that the font file must be uploaded to the server for this to work. For our purposes, we’ll assume you’re going to take this snippet and make it into its own file, which we’ll call “mkwatermark.php”.

<?php
/* ———————————————- */
/* ———— BEGIN PHP SNIPPET —————-*/
/* ———————————————- */
// specify the file name - you can use a full path, or “../../” type stuff here
// if the image is not in the same directory as this code file
$image = imagecreatefrompng(“gs-banner-sm.png”);

// specify the font size
$font_size = 14;

// in this case, the color is white, but you can replace the numbers with the RGB values
// of any color you want
$color = imagecolorallocate($image, 255,255,255);

// make our drop shadow color
$black = imagecolorallocate($image, 0,0,0);

// and now we do the overlay - the layers of text start top to bottom, so
// the drop shadow comes first

// $image - the base image file we specified above
// $font_size - Well duh. Its the size of the font
// 0 - the angle of the text - we don’t want an angle, so we leave it at 0
// 55 - pixels to the right from the leftmost part of the image
// 35 - pixels down from the top of the image
// $black - the color we defined above
// “../fonts/ARIALBD.TTF” - the location on the server that the font can be found
// “Test Text” - the text we’re overlaying - you can also use a variable here
ImageTTFText ($image, $font_size, 0, 56, 36, $black, “../fonts/ARIALBD.TTF”,“Test Text”);

// Now add the actual white text “on top”
ImageTTFText ($image, $font_size, 0, 55, 35, $color, “../fonts/ARIALBD.TTF”,“Test Text”);
header(“Content-type: image/png”);
imagepng($image);
imagedestroy($image);
}

?>

To print out the image, we would just have to wite the html as:

<img src="mkwatermark.php">

Note about Variables - If you are using any variables outside the file to determine what the code does (for example, making the text a variable as we do with the Godsmack sig generator), be sure to secure your code and check to be sure the user can’t do any damage to your system by entering harmful values.

Posted on June 27, 2004 - by snipe

Dynamic thumbnailing with PHP and the GD library

Although there are loads of ways you can do this, for this example, we’re assuming that the fullsize image is located in a directory called “images”, and the thumbnails will have the same name as the fullsize, but will be copied into a directory called “thumbs”.

<?php
// find out the current size info
$photo_filename = “myimage.jpg”
$path = “/home/snipe/www/images/”;
$image_stats = GetImageSize($path.$photo_filename);
$imagewidth = $image_stats[0];
$imageheight = $image_stats[1];
$img_type = $image_stats[2];
$new_w = 100; $ratio = ($imagewidth / $new_w);
$new_h = round($imageheight / $ratio);

// Find out if we need to resize it by checking to
// see if the original image is larger than the
// defined new width, and making sure the
// resized version does not exist yet

if (($imagewidth > $new_w)&& (!file_exists($path.$photo_filename))) {

// if this is a jpeg, resize as a jpeg
if ($img_type==2) {

$src_img = imagecreatefromjpeg($path.$photo_filename);
$dst_img = imagecreate($new_w,$new_h);
imagecopyresized($dst_img,$src_img,0,0,0,0,$new_w,$new_h,imagesx($src_img),imagesy($src_img));
imagejpeg($dst_img, $path.$photo_filename);

} elseif ($img_type==3) {
// if image is a png, copy it

$dst_img=ImageCreate($new_w,$new_h);
$src_img=ImageCreateFrompng($path.$photo_filename);
ImageCopyResized($dst_img,$src_img,0,0,0,0,$new_w,$new_h,ImageSX($src_img),ImageSY($src_img));
Imagepng($dst_img, $path.$photo_filename);

// Normally if image is neither png nor jpeg
// (ie, invalid image or a gif file), I use
// the fullsize as the thumbnail and just
// resize it through the html size tags. For this
// example tho, we’ll pretend we have a version of
// Gdlib that can handle gif resizing

} elseif ($img_type==1) {

$dst_img=ImageCreate($new_w,$new_h);
$src_img=ImageCreateFromGif($path.$photo_filename);
ImageCopyResized($dst_img,$src_img,0,0,0,0,$new_w,$new_h,ImageSX($src_img),ImageSY($src_img));
ImageGif($dst_img, $path.$photo_filename);

} else {
// if it doesn’t show up as any of the valid formats, give an error
echo ‘error’;

} // endif img_type sequence

}

?>

Posted on June 27, 2004 - by snipe

Dynamic thumbnailing with PHP and Imagemagick

This code formatting is a little off, since the WYSIWG editor seems to have eaten part of it. Sorry.

<?php

/* ———————————————- */
/* ———— BEGIN PHP SNIPPET —————-*/
/* ———————————————- */
// specify your file details
$current_file = “image.jpg”;
$max_width = “150″;

// get the current info on the file
$current_size = getimagesize($current_file);
$current_img_width = $current_size[0];
$current_img_height = $current_size[1];
$image_base = explode(“.”, $current_file);

// this part gets the new thumbnail name
$image_basename = $image_base[0];
$image_ext = $image_base[1];
$thumb_name = $image_basename.“-th.”.$image_ext;

// determine if the image actually needs to be resized
// and if it does, get the new height for it
if ($current_img_width > $max_width) {
$too_big_diff_ratio = $current_img_width/$max_width;
$new_img_width = $max_width;
$new_img_height = round($current_img_height/$too_big_diff_ratio);

// presto chango alacazam
$make_magick = system(“convert -geometry $new_img_width x $new_img_height $current_file $thumb_name”, $retval);

// let us know if it worked or not
if (!($retval)) {
echo “Thumbnail created  -”.$thumb_name;
} else {
echo “Oops - no dice! Script failed cuz your momma doesn’t love you.”;
}
} else {
echo “No need to resize! You’re perfect just the way you are.”;
}

?>