Doug Vanderweide

Last weekend I was in Cambridge for the fourth annual New England GiveCamp, which brings together tech and design people with nonprofits in need of their help.

Again this year, I was team lead for The Esplanade Association, a nonprofit which cares and advocates for the eponymous park along the Charles River on the Boston side.

Last year, I lead the team that rebuilt the association’s website on a WordPress backend, tossing in some scheduling and form feedback bells and whistles.

This year, TEA was looking for an interactive map of the park. They had an embedded Google Map, but it was, in a word (and a pun), pedestrian: stock icons, little detail in generic infoWindows, etc. Expedient, but hardly exciting.

I took the project because 1. The Esplanade Association is a dream client (see last year’s recap for why) and 2. I’ve done a pile of work in the Google Maps API.

Once again, I was blessed with a team of very skilled, very dedicated and very amenable people to do most of the work: Nate Bates and Will Klein, both experienced JavaScript and Web UX men; and Bryan Phillips, a graphic designer and GiveCamp fixture. My biggest problem last week was coming up with a polite way to to ask them to wait until we were all together at GiveCamp before programming the entire solution.

That enthusiasm and ownership served us well, even allowing us (well, not “us”; Nate) to tackle fixing the website’s CSS to be responsive to screen size, in effect making it mobile.

Friday

On Friday, we met Ashton Porter and Meghan Buco, TEA’s liaisons for the weekend, and settled upon a basic set of responsibilities.

Nate and Will would take care of finding a mapping provider they liked and put together the JavaScript that would power the front end map. Bryan would work with Ashton and Meghan on getting the icons drawn. I would work on the backend, which leveraged a WordPress custom post type and taxonomy with some metadata.

Nate and Will soon settled on using Leaflet, a JavaScript provider that gives access to several different mapping providers, because they wanted the broadest base of maps from which to choose — namely, as Nate put it, whichever one made the park look nicest. Turns out that was MapQuest.

The basic design for getting points on the map was straightforward: We would use JSON. It would have two primary objects, one for categories — that is, the types of icons we would plot, such as docks, footbridges, monuments, bathrooms, etc. — and another containing the points themselves, keyed to the relevant categories / icons.

Nate and Will worked until about 2 a.m. Saturday, but had a functional map at that time. And after working with Ashton and Meghan, Bryan had the iconography pretty much ready to go first thing Saturday morning.

I spent most of the evening working on GiveCamp stuff in general; I manage the website, Facebook and Twitter accounts, so there was a bunch of stuff I had to get out to the campers. But I managed to hit the sack at about midnight, which is early by GiveCamp standards.

Saturday

On Saturday morning at 9 a.m. we had a functional map and all the art. That was both a complete relief and a bit shocking, as I have been to three GiveCamps previously and not a one of them was anywhere near its goal at 9 p.m. on Saturday, nonetheless 9 a.m.

In fact, I was really the only holdup on our project, as I had not begun work on the WordPress backend.

The concept of the backend was simple: I would create a custom post type in WordPress, with a custom taxonomy, that directly related to the map. The taxonomy would be the map categories, and the custom posts each a point on the map. Ashton and Meghan, who are used to using WordPress, could then add, edit and delete points on the map just as they would any other content.

All my custom posts really needed was a few custom fields: One to record latitude, one to record longitude, and two to record external hyperlink text and links, which they wanted for certain points on the map.

Piece of cake; a few meta boxes would give me the custom fields. I would use the Google Maps API to put a map into the post editor, along with a draggable marker that they could place wherever they wanted the related icon to appear, which in turn would populate the lat / lng textboxes with the correct coordinates.

(It doesn’t matter which mapping API gives coordinates or renders them; after all, a point on the Earth is a fact, and so long as your tiles are to scale, you can mix and match APIs as you please, which is the whole idea behind Leaflet.)

I just needed to hook the CRUD events for the taxonomy and posts, write new JSON any time one of those events fired, and our map would be up-to-date at all times.

It took me about eight hours, largely because WordPress changed how it hooks custom taxonomy CRUD and it’s poorly documented. But by dinner time, we were pretty much done, save some tweaking. Which, again, is amazing.

Nate pretty much spent the entire day fixing TEA’s website skin, and pretty much had it ready to go by 9 p.m. So we were wildly ahead of the game, and while it felt great, I didn’t quite know what to make of it, because normally at that time things have come completely apart.

Interestingly, being ahead of the game was par for the course at this year’s GiveCamp; a few teams were a distance from their goals on Saturday night, but most were “green,” in the vernacular of coordinators Rachel Nichols and Kelley Muir.

Sunday

We did hit a few bumps on Sunday. It turns out we did not fully test the JSON I was sending out via my plugin, and it was causing parsing errors on the live version of the map, a fact we didn’t discover until about 90 minutes before we were supposed to present our work.

I’m glad to say Will was very understanding of my short temper over that ultimately meaningless bump.

As that was getting fixed, the map also shifted on us, putting playgrounds in the middle of streets and parking in the center of retention ponds. Needless to say, that wasn’t good, but it was a quick fix once we had the JSON straightened out. We managed to have everything nailed down with about 5 minutes to go before we presented.

That felt more like GiveCamp.

The Result

TEA is quite pleased with its new map and mobile skin.

You can see both on their website. Just shrink your browser window down to 480 pixels or less to see the mobile skin, and click on “Park Map,” under “About The Park,” to see that.

I cannot say enough good things about the team that made it happen. I wound up helping several other teams and generally being distracted the entire weekend, even in the midst of finally getting around to doing my part of the project, and they pretty much managed themselves.

As I’ve said before, when I am answerable for an end result, I like to provide a responsibility, an expectation and a deadline, and let the person doing the work do the work. Nate, Will and Bryan all performed under that freedom remarkably well.

This year, I stayed at Microsoft NERD, the host of GiveCamp, and I would offer to anyone considering doing that two valuable lessons:

• Concrete floors are hard, so you’ll want more padding than I brought to sleep upon. I broke down early Sunday morning, abandoned my tent and slept on a couch.
• Concrete floors echo sound, so you’ll want to bring some sort of earplugs or headphones, as GiveCamp goes all night, every night, and no matter how hard people try to be respectful, voices carry.
• The lights at NERD are automated to turn on anytime someone moves. Thus, they’re pretty much on all weekend. Bring a sleep mask.

All links in this post on delicious: https://delicious.com/dougvdotcom/more-than-one-way-to-skin-a-map-new-england-givecamp-2013-recap

I’ll be attending New England GiveCamp 2013 this weekend.

GiveCamp is a way for technical people and designers to donate their time to worthy nonprofits. Organized by Jim O’Neil and Kelley Muir and hosted at Microsoft’s New England Research and Development center on the Massachusetts Institute of Technology campus, New England GiveCamp is in its fourth year.

This year I’ll again be working with The Esplanade Association. Last year, I was the leader of the team that revamped their website. It’s a real pleasure to work with them again.

Over the weekend, we’ll be working on an interactive map, probably built on the Google Maps API, of the Esplanade’s many amenities and features. The fellows assigned to this task are already full of ideas and getting to work, so once again, I’ve been very fortunate to have highly motivated, very capable team members assigned to our task.

It’s probably going to be another hectic, exciting weekend. Can’t wait!

All links in this post on delicious: https://delicious.com/dougvdotcom/new-england-givecamp-2013-this-weekend

Not to kiss my own ass, but Microsoft’s official advice on avoiding SQL injection attacks sounds awfully familiar to readers of this blog:

Sanitize (validate) all inputs: “This helps to ensure that the input is free from characters that cause SQL injection attacks.” It also allows you to fix the form and data type of the user input, which pretty much renders basic script kiddie attacks useless.

Parameters, not strings, as query variables: “Creating dynamic queries using string concatenation potentially allows an attacker to execute an arbitrary query through the application.”

In other words, it’s harder to break this:

@person VARCHAR(20); SELECT * FROM table WHERE person = @person;

than it is to break this:

SELECT * FROM table WHERE person = 'some user string';

Stored procedures, not free-form queries: “Stored procedures by themselves do not remove SQL injection vulnerabilities. They only raise the bar on the attacker by hiding much of the underlying database schema.” That is, the attacker can’t easily find out what columns are in a table, or what type of data is in those columns, if you use a stored procedure.

Minimal permissions: “In general, database applications should be using a low-privileged account that has the minimum permissions required to execute the statements submitted to SQL Server.” As in, create a user in your SQL database whose only permission set is to execute your Web-based stored procedures, and connect to the database server as that user.

Those are the basics. And if you don’t understand how to do them, I’ll be putting together a blog series on how to convert your old string-queried Web applications into one secured with stored procedures and proper permissions.

Additionally, Microsoft suggests:

Escape your strings: Microsoft notes that this is important “when SQL identifiers (table names, column names, and so on) might be constructed dynamically.” I’d say, if your solution requires you to dynamically determine table or column names, your solution is doing it wrong; try again. That doesn’t mean you shouldn’t escape strings by default; it means, a dumb or wrong approach isn’t the only reason to do so.

Protect against buffer overruns: “Buffer lengths need to be handled properly to prevent SQL injections through truncation.” In Web development, if you fix the length and type of data, you’ll generally avoid buffer overruns. If you’re taking in bitstreams to dump into BLOBs, that’s another matter altogether; but if you’re skilled enough to do that, you should be skilled enough to armor against overruns.

All links in this post on delicious: http://delicious.com/dougvdotcom/microsofts_-_advice_-_on_-_avoiding_-_sql_-_injection_-_attacks

Some time ago, I received the following e-mail:

Hello
I am interested in getting into computers and designing software and websites. You said in a yahoo post that you did not get a degree but learned everything yourself. How did you do this? Where did you get your information from?
Thanks

My reply:

I started out by playing with Web pages. Then, as people asked me to make things for them, I searched on the Web for examples of how to do it, or read self-help books (think “For Dummies,” “Sams Teach Yourself” and Wrox softcovers) to teach myself how to do things.

If you want to make a career of Web development, my recommendation would be to do so in a more orderly manner than “learn as you go along.”

I would say this: Designing a Web site, and programming it, are two very different skill sets. You can be good at both, and great at one, but it is very difficult to be great at both. Design is left-brain, programming is right-brain.

That doesn’t mean you can’t do both; it means that you should expect to specialize either in design or programming. You may be that rare person who can master both, but expect that one or the other will be your actual focus.

I wrote a post a while back on what I think makes sense for a beginning programmer / designer to self-teach: https://www.dougv.com/2008/10/02/recommended-steps-in-a-web-development-career/

There are a number of fast-track Web development cheats you can undertake these days, that weren’t available when I started out.

The smartest one is to learn WordPress, which is far and away the most popular content management system in the world, meaning there’s plenty of work in it. Start by learning how to make WordPress themes. A great book to learn the basics is WordPress Web Design for Dummies, http://www.dummies.com/how-to/content/wordpress-web-design-for-dummies.html

What you really need is opportunities to practice. To that end, you should look for ways to make very simple Web sites; that is, sites that are just basic HTML and CSS, so you can master the design and basics first.

The obvious source of these opportunities are friends, charities and small nonprofits. Think wedding sites, youth sports teams, animal shelters, food banks, etc.

You can find nonprofit organizations a number of ways. Search Google for your local United Way and Chamber of Commerce, and ask them for the names of some charities; or search for your state name and “nonprofits.”

Don’t just focus on charities that don’t have a Web site; consider those that do, but have ugly / broken ones. Keep in mind, however, that many nonprofits have Web sites made or maintained by volunteers or donors, who may resent you coming in, or may lack the skills needed to care for a well-made Web site. (For example, a lot of nonprofits will make their Web sites in Microsoft Publisher or Microsoft Word, then simply save that as HTML and upload the site.) Or, if you make a Web site for a charity, they will need you to do everything from getting it online to keeping it online.

Once you have made a few nice Web sites that you feel confident in showing off, start applying for jobs. Don’t try to go out on your own at first, unless you have steady employment elsewhere; when possible, learn your trade on someone else’s dime.

Finally, if you decide to go the traditional, learn through school route, make sure that any art / design school you attend is accredited by a NASAD organization. If you are going the programming route, make sure the school or college is accredited by an ABET organization. Going to an accredited school means you can get federal financial aid, your credits will be largely transferable to other accredited schools, and your degree will likely be recognized if you ever decide to get a higher degree.

Hope this helps.

My correspondent followed up with this:

Thanks so much for the information. I’m interested in programming more than websites. Eventually I would like to get into apps and software. Would the best way to do this be take free classes in programming?
Thanks again

My response to that message:

If you intend to work for someone else, or you are fairly young and just starting out, going to an ABET-accredited college is the wisest path. While that costs money, you should find that as long as your grades are good, you will find a good-paying job immediately after graduation, in any economic situation. As in, go to a state school, borrow the $40,000 or so you’ll need to get your degree, and accept that the first couple years out will be lean while you pay those loans back.

If you intend to self-teach, a few seminar / professional classes wouldn’t hurt. If you want to work for someone else, focus on certifications, such as MCPD http://www.microsoft.com/learning/en/us/certification/mcpd.aspx or Zend certification http://www.zend.com/services/certification/php-5-certification/. You don’t necessarily need the certifications, just the classes they teach for certification. (Although, as a practical matter, a certification and a good portfolio is every bit as good as a bachelor’s degree.) Those classes are going to focus on the practicality of getting something done, rather than the theory.

In other words, if you go to programming college, they will teach you the theory of how to program, and give you some hands-on experience to back that theory up. If you go the certification route, they will show you how to program, and explain why it works as they go along.

The former is better for long-term professional growth; the latter is better for getting a job right now.

Invest In Learning

The bullet points of my advice for getting started in the programming world:

• If you can afford it, go to an accredited school and learn the skills you need.
  • If you can afford to get all the way through to a master’s degree, or even a doctorate, do it. Your long-term earnings will pay back in spades.
  • A bachelor’s degree is better than an associate degree is better than a certification, but all of those are better than nothing, especially when starting out.
• Wherever possible, learn your trade on someone else’s dime. In other words, once you get out of school, don’t go out on your own; go work for someone else.
• If you really need to learn as you go along, the most important thing is to have a strong portfolio of recent work.
  • Learn WordPress, as it’s relatively easy to program / theme / use and it’s wildly popular, meaning you will always be able to find work in it.
  • Look for charities, nonprofits, community organizations and the like to get started on developing your portfolio, but understand that working for broke people has a lot of pitfalls.

All links in this post on delicious: http://delicious.com/dougvdotcom/basic-advice-for-learning-computer-programming

This question came via email earlier today:

Your Article: An Image-Based ‘Checkbox’
http://www.dougv.com/demo/js_imgswap_checked/example2.html

Awesome article and very informative. I was trying to get it to work for what I am trying to accomplish but I am having a hell of a time getting it to work I was hoping you can help me.

I have a selection form based on Check Boxes:

item 1 (box)
item 2 (box)
item 3 (box)
item 4 (box)

I have images that correspond to the check boxes, item1.png, item2.png, item3.png, item4.png that are located elsewhere on the page. I just need the image to change based on whether the check box is checked or not. It doesn’t matter if it is faded or an entirely new image, so long as the user notices which box they checked. My problem is there are a lot of little images so in your example you had the two set variables, on or off. How do I introduce multiple images???

I have early tests and the problems are that the default unchecked images do not show up until after the check box is checked…all of them and two whenever I try to add multiple images and or check boxes it breaks somewhere, perhaps the array or I am approaching this at the wrong end. I need your help.

To rephrase the question:

• We have four checkboxes inside a form.
• On the same page, we have four images. Each image is related to one of those checkboxes. That is, Checkbox 1 is related to Photo 1, Checkbox 2 is related to Image 2, etc.
• When we click a checkbox, we want to change its associated image somehow, to indicate the checkbox’s state. (In this case, I am going to use a faded image if its related checkbox is unchecked, and a full-opacity image if its related checkbox is checked).

Easily enough accomplished.

For this exercise, I will use four pictures of Miss USA 2012 contestants, and ask users to pick which are their favorites. If a contestant is selected, her picture will be at full opacity; if the contestant is not selected, her image will be at reduced opacity (i.e., faded).

Step 1: Link to jQuery

I’ll power this with jQuery, since it has good cross-browser support not only for handling DOM events like clicks, but also because it has good cross-browser support for “fading” DOM objects, including pictures, in and out.

For those unfamiliar with jQuery, it’s a JavaScript library. What that means is, it’s already solved a number of common JavaScript needs into handy functions, which have been tested to work in most Web browsers.

If I have a very small, very straightforward JavaScript need — say, echo out an alert if something is clicked — I don’t bother using jQuery. But the more complicated the task I need to accomplish, and the more reliant I am on ensuring that the client properly process that task, the more likely I am to use jQuery.

I used to advise people to upload a copy of jQuery to your Web site and linking to it from there. Of late, however, I pretty much just link to a version of jQuery hosted by the Google Libraries API.

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>

Step 2: The HTML

Next, we need to prepare our page to handle the effects we will create via jQuery / JavaScript.

The most important part of that process is ensuring we have some way to relate each checkbox to its related image. There are a few ways we can do that:

1. Create a CSS class for each pairing, and assign both elements to that class;
2. Create IDs for each checkbox and image that are closely related (for example, “item_1_checkbox” and “item_1_image”).
3. Associate the value of the checkbox with some attribute of the image (for example, an image with the ID “foobar” and a checkbox with a checked value of “foobar”).

For this example, I am going to use a hybrid of options 2 and 3.

Specifically, I am going to assign each image the ID of “img_{value},” where {value} is the value of its associated checkbox. For example, if I have a checkbox with the value of “miss_maine_usa”, its associated image will have the id “img_miss_maine_usa.”

Like this:

<img id="img_miss_maine_usa" name="img_miss_maine_usa" src="miss_maine_usa.jpg" class="contestant_pic" alt="Rani Williamson, Miss Maine USA" title="Rani Williamson, Miss Maine USA" />
<input type="checkbox" id="cb_miss_maine_usa" name="favorite_contestant" value="miss_maine_usa" />

We’ll lay out the images and form separately, since that’s what the question implies is the case. However, you could put these images inside the form, if you want. It’s not necessary, but it can be done that way.

<div id="container-photos" class="container">
	<div id="column-1" class="column">
		<img id="img_miss_alabama_usa" name="img_miss_alabama_usa" src="miss_alabama_usa.jpg" class="contestant_pic" alt="Katherine Webb, Miss Alabama USA" title="Katherine Webb, Miss Alabama USA" />
		<a href="http://www.missuniverse.com/missusa/members/profile/651291/year:2012/" title="Katherine Webb, Miss Alabama USA" target="profile">
			<p class="caption">Katherine Webb, Miss Alabama USA</p>
		</a>
	</div>
	<div id="column-2" class="column">
		<img id="img_miss_maine_usa" name="img_miss_maine_usa" src="miss_maine_usa.jpg" class="contestant_pic" alt="Rani Williamson, Miss Maine USA" title="Rani Williamson, Miss Maine USA" />
		<a href="http://www.missuniverse.com/missusa/members/profile/651310/year:2012/" target="profile">
			<p class="caption">Rani Williamson, Miss Maine USA</p>
		</a>
	</div>
	<div id="column-3" class="column">
		<img id="img_miss_northcarolina_usa" name="img_miss_northcarolina_usa" src="miss_northcarolina_usa.jpg" class="contestant_pic" alt="Sydney Perry, Miss North Carolina USA" title="Sydney Perry, Miss North Carolina USA" />
		<a href="http://www.missuniverse.com/missusa/members/profile/651324/year:2012/" target="profile">
			<p class="caption">Sydney Perry, Miss North Carolina USA</p>
		</a>
	</div>
	<div id="column-4" class="column">
		<img id="img_miss_southdakota_usa" name="img_miss_southdakota_usa" src="miss_southdakota_usa.jpg" class="contestant_pic" alt="Taylor Neisen, Miss South Dakota USA" title="Taylor Neisen, Miss South Dakota USA" />
		<a href="http://www.missuniverse.com/missusa/members/profile/651332/year:2012/" target="profile">
			<p class="caption">Taylor Neisen, Miss South Dakota USA</p>
		</a>
	</div>
	<div id="column-5" class="column unrelated">
		<img id="img_bill_murray" name="img_bill_murray" src="bill_murray.jpg" class="another_class_altogether" alt="Bill Murray" title="Bill Murray" />
		<a href="http://www.imdb.com/name/nm0000195/" target="profile">
			<p class="caption">Bill Murray</p>
		</a>
	</div>
</div><!-- /photos -->

<br class="clearfix" />

<div id="container-form" class="container">
	<form id="myform" name="myform" method="get">
		<h4>Who are your favorite Miss USA 2012 contestants?</h4>
		<label><input type="checkbox" id="cb_miss_alabama_usa" name="favorite_contestant" value="miss_alabama_usa" />Katherine Webb, Miss Alabama USA</label>
		<br />
		<label><input type="checkbox" id="cb_miss_maine_usa" name="favorite_contestant" value="miss_maine_usa" checked="checked" />Rani Williamson, Miss Maine USA</label>
		<br />
		<label><input type="checkbox" id="cb_miss_northcarolina_usa" name="favorite_contestant" value="miss_northcarolina_usa" />Sydney Perry, Miss North Carolina USA</label>
		<br />
		<label><input type="checkbox" id="cb_miss_southdakota_usa" name="favorite_contestant" value="miss_southdakota_usa" />Taylor Neisen, Miss South Dakota USA</label>
		<br />
		<label><input type="checkbox" id="unrelated_checkbox" name="unrelated_checkbox" value="foobar" />This checkbox is not related to any of the pictures above.</label>
		<br />
		<input type="submit" id="submit" name="submit" value="Submit" />
	</form>
</div><!-- /form -->

Note that in this form, all the checkboxes that have a related image are named “favorite_contestant.” It may be that you need to relate checkboxes and images on a very specific basis. That’s outside the scope of this article; for most intents and purposes, this approach will suffice.

Step 3: The JavaScript

To make this work, we need to incorporate two steps:

• Fade all the unchecked the images on page load. That is, some of our images may be prechecked in the form; we want those to be at full opacity. All unchecked images should be faded down.
• Fade an image up if it is checked, and down if it is not.

Why do we set the initial fade state of images via JavaScript? Simple: Because not all visitors will have JavaScript enabled, or use a browser that understands our code. It’s better to assume this doesn’t work for any given visitor, than to assume it does.

First, we need to set some global variables: constants for full opacity, faded opacity, the rate at which we want the fade to take place and a simple string variable we’ll use to construct the image ID we want, based on the checked value of a box.

Also, note that we will wrap all of this script in $(document).ready(), which is how jQuery wires up DOM event handlers on page load.

//set global constants & variable
var full_opacity = 1;
var faded_opacity = 0.3;
var fade_speed = 'fast';				
var objid;

Next, we want to set the initial state for each checkbox.

I am selecting all the checkboxes with the name “favorite_contestant.”

If all the checkboxes on my page were related to an image, I could have used the selector ‘:checkbox’ to get them all; if the only input elements I had on the page were checkboxes, I could have used ‘input’. But in this case, I have an additional, unrelated checkbox on the page, so I need to be specific about which checkboxes have associated images.

$('input[name="favorite_contestant"]').each(function() {

	//we need the image associated with this checkbox
	//i am assuming the associated image tag has an ID of "img_" and whatever the value of the associated checkbox is
	//e.g., img_miss_alabama_usa is the id for the image associated with the checkbox that has the checked value miss_alabama_usa
	objid = "#img_" + $(this).val();
	
	if($(this).prop('checked')) {
		//full opacity for prechecked images
		$(objid).css('opacity', full_opacity);
	}
	else {
		//partial opacity for unchecked images
		$(objid).css('opacity', faded_opacity);
	}
});

With the initial fade state of each image set, we can now add the handler that will fade images up and down, depending on whether its related checkbox is clicked.

$('input[name="favorite_contestant"]').change(function() {
	//same method as above for associating a checkbox and its image
	objid = "#img_" + $(this).val();
	if($(this).prop('checked')) {
		//full opacity for image if associated checkbox is checked
		$(objid).fadeTo(fade_speed, full_opacity);
	}
	else {
		//partial opacity for image if associated checkbox is unchecked
		$(objid).fadeTo(fade_speed, faded_opacity);
	}
});

And with that, we have a fully functional solution. You can see it in action here: http://www.dougv.com/demo/ajax_checkbox_img_switch/

No code download this time; just save the HTML, since it all runs client-side.

All links in this post on delicious: http://delicious.com/stacks/view/DdfDOo

Received in my email today:

Hi

say your blog and thought you might help.

strsql = “SELECT StaffID, DesignationID, StaffName, Password, ShopID from staffT where StaffName =” & UserName.Text & ” AND Password =” & Password.Text & “”

from the string, the username.text and password.text are form controls. what is happening is there are passing null values regardless of what you input in the text boxes resulting in a system error.

“System Error Object reference not set to an instance of an object”

Am using Mysql as the database.

I’m always glad to answer such questions, especially when the questioner is flirting with disaster, as much as this questioner is.

A trained eye can immediately spot the problem with the SQL statement above, aside from the problem of NULL values tossing errors. Namely, it’s wide-open to SQL injection. (And an even keener eye will note that the values for user name and password aren’t delimited with single-quotes.)

So here’s my reply email to the questioner:

Your SQL statement has three problems.

1. It is wide open to injection attack. See http://www.unixwiz.net/techtips/sql-injection.html for examples.
2. As you noted, when nulls are passed in, the expression fails.
3. It appears you have not delimited your text inputs with single quotes.

Assuming you are using ASP.NET, and that your user names and passwords are only alphanumeric, the direct fix to your problem is this:

Dim strUser As String
If String.IsNullOrEmpty(UserName.Text) Then
	strUser = String.Empty
Else
	strUser = UserName.Text.Replace("'", "''")
End If

Dim strPass As String
If String.IsNullOrEmpty(Password.Text) Then
	strPass = String.Empty
Else
	strPass = Password.Text.Replace("'", "''")
End If

strsql = "SELECT StaffID, DesignationID, StaffName, Password, ShopID from staffT where StaffName = '" & strUser & "' AND Password = '" & strPass & "'"

That should get you going, but you should employ the following best practices fixes:

1. Use stored procedures with paramerterized input values. This serves to automatically sanitize your queries for data type.
2. Use RequiredFieldValidators and RegularExpressionValidators to ensure that you receive some sort of input for each field, and that each does not contain an effort to inject your code with SQL.
3. Impose some sort of control that limits multiple login attempts.
4. Better yet, use the built-in membership provider in ASP.NET, which can be used with MySQL, and has already resolved many potential attack vectors.

Hope this helps.

Additional Thoughts And Clarification

The guidance I gave the emailer, instructing him to escape single quotes, is a bare-minimum escaping sequence. As a practical matter, he should sanitize his inputs against additional MySQL sequences, such as double-dashes and semicolons, as well as reserved SQL statement words, such as DROP, ALTER, DELETE, etc. There’s an HTTPModule example over at the ASP.NET forums that does this automatically for an application.

Using parameterized queries / stored procedures to combat SQL injection is a primary recommendation from both Microsoft and MySQL (pdf).

In addition to securing data type, query parameters limit the ability of an attacker to inject SQL by fixing the form of the query implicitly. In other words, it’s harder for him to mangle a parameter than it is to mangle a string.

All links in this post on delicious: http://delicious.com/stacks/view/Kp3pOU

Last weekend I was in Cambridge, Mass. for New England GiveCamp 2012, the third of annual meet-ups that match technical and design people with nonprofit organizations that need their help.

My cause was The Esplanade Association, an organization that cares for the Charles River Esplanade Park.

The Charles River Esplanade Park is the Boston-side green space along the river, from the Museum of Science to the Boston University Bridge. While it’s owned and managed by the state of Massachusetts, TEA (which has to be the coolest acronym possible for a Boston-based group) exists to organize people to help protect and care for the park.

Much of their work involves organizing volunteers to clean up the park several times each year. TEA also holds a number of programs in the park — yoga, Zumba, dances and the like — and runs several fund raising projects.

They came to GiveCamp, initially, looking for a way to better coordinate singing up groups and individuals for cleanup days.

Ticketing Turns To Overhaul

Currently, the way Jessica B. Pederson, TEA’s project manager, handles such reservations is by posting a schedule of cleanup days on her Web site, and asking people to email her if they are interested in participating. Then, via email exchanges back and forth, the details get worked out.

Even though the majority of these events are done through groups — so Jessica is in contact with a few group representatives, rather than scores of individuals — this proves an understandably arduous process that consumes a great deal of Jessica’s time, and it was something that she knew just screamed out to be automated.

You can’t swing a cat without hitting sample booking / ticketing system code on the Web, and there’s little practical difference between booking, say, a hotel room or buying a ticket to a play, and reserving a volunteer opportunity slot. Just provide the date, the number and types of tickets available, and a way for people to provide their contact information, and you’re done.

So I started by looking for ASP.NET-based ticketing solutions on the Web, since most of the Web developers who participate in GiveCamp are .NET folks. (Actually, I was side-hoping I could request Saurabh Moondhra and William Wade, the two ASP.NET developers I worked with during GiveCamp 2011, whom I knew would shine on such a project.)

And I was also thinking, in the back of my mind, it would be nice to address TEA’s Web site.

Don’t get me wrong; the site they had worked fine and contains a lot of good information presented in a sensible way. But it’s also stuck in 2005, and could use a cosmetic and functional overhaul.

And coincidentally, I am working on a WordPress site for a client right now that included the need for a basic calendaring and ticketing system. I had identified a plugin, Events Manager, for them that wasn’t quite right for that project; but it almost perfectly fit Jessica’s needs, especially if she was willing to upgrade to the “pro” version.

And then, by coincidence again, Kelley Muir — who coordinates the projects at GiveCamp — emailed me to say that she had asked this year’s nonprofits to identify possible second projects, and TEA said they’d like to have a content management system and general site redesign, too.

Jackpot! No need to reinvent the wheel; no need to even resize the wheel. We’re gonna put them in the Honda of the Web — good ol’ WordPress — and slap some 20-inch rims (Events Manager) on it.

Meet TEA

I talked with Jessica and Christopher Timmel, TEA’s communications director, on the phone on Wednesday, to go over their needs and expectations, and knew instantly that I had lucked out a second time.

I’m a big fan of green space. You can’t live in Augusta, Maine and not like trees and rivers, after all.

But as a simple matter of public policy, I don’t think parks and open spaces get enough credit or resources, in spite of being a huge factor in quality of life. Understandably, because trees and grass pretty much grow on their own, it’s easy to put government dollars and effort elsewhere.

So when I see a group like TEA, I’m already a huge fan of their work.

Even better was that Jessica and Chris had a very clear picture of what they actually wanted. Even more rarely, what they wanted was actually what they needed. (It’s strange, but often, clients pitch requirements that don’t resolve their problems, or do so in ways that only make the problems worse or create new problems. Not so with Chris and Jessica.)

Plus, they’re personable, sincere and nice people. No coddling, wrangling or placating was in my weekend forecast. Not that it’s been a problem for me in past GiveCamps; just that any time you can get a client that’s positive about its problems, you need to celebrate.

Meet The Team

So I asked Kelley to assign a designer and a couple PHP people (since WordPress skinning requires a little knowledge of PHP, and I figured we might need to adjust some of the Events Manager code via overrides) to the project.

That’s how Christina Yung and Jason Dufour were assigned to the project, and they were my third stroke of luck.

An even better perk is that Jason and Christina work together at their day jobs, which means there would be no need to worry about chemistry or communication. And the weekend bore out that both were take-initiative-and-ownership types, which fits my management style perfectly: I like to provide an area of responsibility and a goal, then let people figure out, and do, what needs to get done to achieve it.

So two times I was a GiveCamp project lead, and two times Kelley assigned my team members who were skilled, friendly people who knew what to do and got done what needed to get done.

The Project

Even when you have a superior platform (WordPress); the right tools inside that platform (Events Manager); a client with full buy-in, all collateral organized and ready, and authority to make decisions on the fly (Chris and Jessica); and a team that’s got the skills and drive to get the job done (Jason and Christina), putting a Web site together over a weekend is a frenzy.

There were a few points where I thought we wouldn’t make it.

In addition to the usual bumps in any project road — misunderstandings, mistakes, unforeseen complications and unintended consequences are the hallmarks of every Web project — there were many, many steps in getting the content right.

Because of the kinds of information TEA needs to communicate, this WordPress site wasn’t header-footer-sidebar-index-css-and-we’re-done.

They needed fairly complicated page flow, and the calendar itself mutated a couple times as it became clearer what events would need a registration component, and which events they simply wanted to let people know about. And their information required a few different page templates and a couple extra sidebars, to ensure it showed up properly.

Additionally, I had the objective to use as few plugins (aside from Events Manager) and as little code as possible (especially in the templates themselves), because I wanted to ensure whomever helps them with technical needs going forward can make heads and tails out of what we did. So that meant every error we made in terms of approach, taxonomy and content was magnified, since we couldn’t simply find a plugin or write some code to fix the issue.

In the end, we would up with only eight plugins, three of which are standard in all my WordPress installs:

• Bluetrait Event Viewer (BTEV), which logs system events and is very useful for debugging, intrusion detection and the like;
• Category Posts Widget, which — as its name suggests — allows you to show posts in a specific category as a sidebar widget;
• Events Manager, a calendar and booking system I simply cannot flog hard enough;
• Google XML Sitemaps, to help with site indexing;
• HTML Page Sitemap, which automatically generates a sitemap based on wp_list_pages();
• Simple Lightbox, for slideshow functionality (TEA has lots and lots of pictures);
• Single Post Widget, which — as its name suggests — allows you to show a single post in a sidebar; and
• WP Super Cache, to reduce server load and improve site responsiveness.

The Results

Jessica and Chris are happy with their new site. They’re going to take a week to finish migrating content, come up with a list of questions and tweaks, then present it to their board of directors for feedback.

But you don’t have to wait. Check out the before and after shots:

As I quipped at the GiveCamp closing presentations, it looks good because Christina made it that way; it works because Jason made it that way; and it’s not done because I made it that way.

The site needs to go live still; a fair amount of content remains to be migrated in, there are a few cosmetic tweaks needed, and we didn’t run a complete walkthrough / debugging of the site because all the elements are not in place.

But that is not to detract at all from what was accomplished. While I intend to help TEA go live with this site, really it can be finished by anyone with basic HTML / CSS knowledge. Anything short of complete disapproval of the skin won’t be difficult to accommodate, and that skin is not going to be rejected. Not by sane people, anyway.

So I hit for the cycle: Great project; great client; great team; great results. GiveCamp 2012 was a complete success.

A Round Of Applause

I’d be remiss if I didn’t note, once again, what a great experience GiveCamp is on the whole.

There’s just something reaffirming and enjoyable in being around capable, motivated, positive people who are in it for the love. Sincerely, you meet the nicest and best people at GiveCamp.

You get fed well. (Thank you Green Mountain Coffee Roasters, Naked Pizza, b.good catering, Whole Foods, Cape Cod Chips, Rip van Wafels and Cakes By Kelli.) You get all kinds of great swag (that’s generally true of any event hosted by Jim O’Neil, New England GiveCamp’s host and driving force, but is certainly true of GiveCamp). And you get to be in Cambridge, which is an amazing place.

I should also mention, after the disappointment of the Royal Sonesta hotel last year, that the best deal in town for a stay near MIT has to be the Hampton Inn on Monsignor O’Brien Highway. Even though I figure I paid about $30 per hour to sleep there (11 hours of sleep @ $340 for two nights), it’s very clean, the bed is awesome, the staff is friendly and efficient, it’s 100 percent geared toward serving business travelers (free WiFi, lightning-fast checkin and automatic checkout), the hot breakfast is actually quite palatable and it’s only a one-mile walk up Third Street from NERD, where GiveCamp is hosted.

So, everybody who’s taken part or helped make it happen, thanks. You done good. Real good. And if you couldn’t make it this year, GiveCamp 2013 is April 26-28; mark your calendars now!

All links in this post on delicious: http://delicious.com/dougvdotcom/tea-time-new-england-givecamp-2012-recap

I’ve reopened comments on all posts, regardless of post date.

Two years ago, I closed comments on posts older than 60 days because generally speaking, they only attract spam.

That’s still true, for the most part. But between WordPress Hashcash (an anti-spam plugin I can’t flog hard enough; seriously, get it) and Akismet, there’s no real additional work load; it’s not half the hassle of dealing with the script kiddies trying to log in to the admin section.

(BTW, I deal with hack attempts via Bluetrait Event Viewer, which lets me know about (among many other things) failed logins; and WordPress SEO by Yoast, which lets me easily edit my .htaccess file and put the banhammer down on offending IP addresses.)

My most popular posts are well over 60 days old, and the majority of questions I get are about those posts. Also, I have a number of very old (3+ years) that need cleaning up; so it would be helpful to enable comments on them, for people to tell me when things are broken / don’t look right on the screen. Thus, it makes sense to open comments back up on all posts.

FYI, all first-time comments are moderated. In other words, if you’re making your first comment here, I will need to approve it before it appears for others; but once that happens, all your subsequent comments will be published immediately. (Also FYI: Your email address determines if you are a first-time comment. So if you’ve commented here before but used a different email address, you’ll once again be moderated. All subsequent comments using the same email address will go live instantly.)

Finally, if you aren’t trolling or a moron, I’ll approve / retain your comment. I may edit a comment, sometimes to clean up its formatting, possibly to clarify something it says, maybe to remove something offensive from an otherwise worthwhile comment.

I reserve the right to act capriciously. You’ve been warned.

All links in this post on delicious: http://delicious.com/dougvdotcom/comments-reopened-on-all-posts

Some time ago I wrote a PHP-MySQL based solution to getting all ZIP Codes in a given radius from a known point / ZIP Code. I’ve long intended to do an ASP.NET version of that post, and here it is.

I won’t bother revisiting the mechanics in detail. I do urge you to read the post on the PHP version of this solution, at least to familiarize yourself with the mechanics of what I am doing and the compromises I’ve taken in coming up with this solution.

I will note the following for the “get to the point” types:

• The first thing we need is to procure a geocoded database table of ZIP Codes. There are several out there; the one I am using is the ZIP Code Database project, available at Sourceforge. You’ll need to figure out how to get their CSV file into your SQL Server database; BULK INSERT is an option, or you can script it.
• The basic method I am going to use is to create a square. Specifically, I am going to:
  • ask the end user for a starting ZIP Code, and a radius from that point from which he would like other ZIP Codes to come;
  • create a square by selecting points North, South, East and West at the given distance from the starting point; then
  • query the database for all points that fall within that square (or, in other words, all points with latitudes less than North, greater than South, less than East and greater than West).
• I’m going to put my results in a GridView. However, you could easily just use a DataReader or DataTable to get the relevant records and do with them as you like.
• The formulas I am using to compute longitude and latitude coordinates come from moveabletype.co.uk.

Step 1: Helper Functions

Before we get into the nitty-gritty, we need to create two helper functions: One that converts degrees into radians, and another that reverses the process. (In trigonometry, angles are calculated in radians; a radian is the ratio between the length of an arc and its radius. If an arc is as long as its radius, that’s 1^c).

First, to convert from degrees to radians:

Function Deg2Rad(ByVal sglDegrees As Single) As Single
	Return sglDegrees * (Math.PI / 180.0)
End Function

And to go from radians to degrees:

Function Rad2Deg(ByVal sglRadians As Single) As Single
	Return sglRadians * (180.0 / Math.PI)
End Function

Step 2: Functions To Plot Latitude And Longitude

Now that we can move back and forth between degrees and radians, we can calculate the latitude and longitude of points at a known distance and bearing from an initial geocoordinate.

Given a known starting point, expressed as lat1 and lon1; a known distance from that point, d; a known bearing from that point, b; and a known radius of the sphere over which we are travelling, r; we calculate a new geocoordinate, expressed as lat2 and lon2, thus:

lat2 = asin(sin(lat1) * cos(d/r) + cos(lat1) * sin(d/r) * cos(b))
lon2 = lon1 + atan2(sin(b) * sin(d/r) * cos(lat1), cos(d/r) - sin(lat1)*sin(lat2))

So here’s a function that accepts, as arguments, a starting latitude, radius, bearing and distance; and returns the latitude at the provided bearing and distance from that starting latitude.

Function CalculateLatitudeCoordinate(ByVal sglLat1 As Single, ByVal intRadius As Integer, ByVal intBearing As Integer, ByVal intDistance As Integer) As Single
	Return Math.Asin(Math.Sin(sglLat1) * Math.Cos(intDistance / intRadius) + Math.Cos(sglLat1) * Math.Sin(intDistance / intRadius) * Math.Cos(intBearing))
End Function

And here’s a function that accepts, as arguments, a starting latitude and longitude, and ending latitude, radius, bearing and distance; and returns a longitude at the provided bearing and distance from the starting coordinates.

Function CalculateLongitudeCoordinate(ByVal sglLon1 As Single, ByVal sglLat1 As Single, ByVal sglLat2 As Single, intRadius As Integer, ByVal intBearing As Integer, ByVal intDistance As Integer) As Single
	Return sglLon1 + Math.Atan2(Math.Sin(intBearing) * Math.Sin(intDistance / intRadius) * Math.Cos(sglLat1), Math.Cos(intDistance / intRadius) - Math.Sin(sglLat1) * Math.Sin(sglLat2))
End Function

Step 3: Create The Form

To implement, we need to create a form with a few elements:

• a TextBox to accept an initial ZIP Code;
• a DropDownList to provide the desired distance from that point, which will serve to create our search area;
• a RequiredFieldValidator;
• a RegularExpressionValidator, to ensure we get a five-digit number as our input;
• a Button to submit;
• a Label, to display messages;
• a GridView, to display the ZIP Codes we get from the database; and
• a SqlDataSource, against which we will bind the ZIP Codes

<p>
	Select all ZIP Codes within 

	<asp:DropDownList runat="server" ID="ddlDistance">
		<asp:ListItem Selected="True">5</asp:ListItem>
		<asp:ListItem>10</asp:ListItem>
		<asp:ListItem>25</asp:ListItem>
		<asp:ListItem>50</asp:ListItem>
		<asp:ListItem>100</asp:ListItem>
	</asp:DropDownList>

	miles of ZIP Code

	<asp:TextBox runat="server" ID="tbZip" Columns="5" />
	<asp:RequiredFieldValidator 
		runat="server" 
		ID="rfvZip" 
		ControlToValidate="tbZip" 
		ErrorMessage="Please provide a ZIP Code" 
		CssClass="warning" 
		Display="Dynamic" 
	/>
	<asp:RegularExpressionValidator 
		runat="server" 
		ID="revZip" 
		ControlToValidate="tbZip" 
		ValidationExpression="^[0-9]{5}$" 
		ErrorMessage="Please enter a valid five-digit ZIP Code" 
		CssClass="warning" 
		Display="Dynamic" 
	/>

	<asp:Button runat="server" ID="btnZip" Text="Get ZIP Codes" />
</p>

<p><asp:Label runat="server" ID="lblStatus" Text="Status messages will appear here" /></p>

<asp:GridView 
	runat="server" 
	ID="gvZIP"
	DataSourceID = "sqlZip" 
	AutoGenerateColumns="false" 
	AllowSorting="true"
	AllowPaging = "true"
	PageSize = "20"
	HeaderStyle-BackColor="Yellow" 
	HeaderStyle-Font-Bold="true" 
	HeaderStyle-HorizontalAlign="Center"
	AlternatingRowStyle-BackColor="WhiteSmoke" 
	CellPadding="5"
>
	<Columns>
		<asp:BoundField HeaderText="City" DataField="cityname" SortExpression="cityname" />
		<asp:BoundField HeaderText="State" DataField="statecode" SortExpression="statecode" />
		<asp:BoundField HeaderText="ZIP Code" DataField="zip_code" SortExpression="zip_code" />
		<asp:BoundField HeaderText="Latitude" DataField="latitude" SortExpression="latitude" />
		<asp:BoundField HeaderText="Longitude" DataField="longitude" SortExpression="longitude" />
		<asp:BoundField HeaderText="Distance" DataField="distance" SortExpression="distance" />
	</Columns>
</asp:GridView>

<asp:SqlDataSource 
	runat="server" 
	ID="sqlZip" 
	SelectCommand="sp_get_zips_in_radius" 
	SelectCommandType="StoredProcedure" 
	ConnectionString="YOUR CONNECTION STRING"
>
	<SelectParameters>
		<asp:Parameter Name="maxlat" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="minlat" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="maxlon" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="minlon" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="startlat" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="startlon" DbType="Decimal" DefaultValue="0.0" />
		<asp:Parameter Name="radius" DbType="Int16" DefaultValue="3959" />
	</SelectParameters>
</asp:SqlDataSource>

Step 4: Get The Initial Point

We need to get, from the database, the initial geocoordinates for the user-supplied ZIP Code. We do that with a stored procedure:

CREATE PROCEDURE [dbo].[sp_get_zip_code]
	@zip_code CHAR(5)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT *
	FROM zip_codes
	WHERE zipcode = @zip_code
END

This query should return to us the city name, state, latitude and longitude for the specified ZIP Code. As always, we want to catch any exceptions in making the query, and we want to make sure we get a record from the database (that is, we can find the starting ZIP Code in the database).

If we can’t get starting coordinates, we’ll report that.

Otherwise, we’ll output details about the starting point to our Label control, and invoke a (yet-to-be-written) subroutine to populate the GridView.

Sub GetInitialCoordinates() Handles btnZip.Click
	'This subroutine requires a Label control named lblStatus
	
	'Prepare to connect to db and execute stored procedure
	Dim objConn As New SqlConnection("YOUR CONNECTION STRING")
	Dim objCmd As New SqlCommand("sp_get_zip_code", objConn)
	objCmd.CommandType = CommandType.StoredProcedure

	'we need to supply the ZIP code as an input parameter to our stored procedure
	objCmd.Parameters.Add(New SqlParameter("zip_code", SqlDbType.Char, 5))
	objCmd.Parameters("zip_code").Value = tbZip.Text

	'sglMinLat = south, sglMaxLat = north, sglMinLon = west, sglMaxLon = east
	Dim sglMinLat As Single
	Dim sglMaxLat As Single
	Dim sglMinLon As Single
	Dim sglMaxLon As Single

	Try
		'open connection
		objConn.Open()
		'put results into datareader
		Dim objReader As SqlDataReader
		objReader = objCmd.ExecuteReader()
		If objReader.HasRows Then
			'if starting point found, calculate box points
			objReader.Read()
			sglMinLat = Rad2Deg(CalculateLatitudeCoordinate(Deg2Rad(objReader("latitude")), 3959, Deg2Rad(180), ddlDistance.SelectedValue))
			sglMaxLat = Rad2Deg(CalculateLatitudeCoordinate(Deg2Rad(objReader("latitude")), 3959, Deg2Rad(0), ddlDistance.SelectedValue))
			sglMinLon = Rad2Deg(CalculateLongitudeCoordinate(Deg2Rad(objReader("longitude")), Deg2Rad(objReader("latitude")), Deg2Rad(sglMinLat), 3959, Deg2Rad(270), ddlDistance.SelectedValue))
			sglMaxLon = Rad2Deg(CalculateLongitudeCoordinate(Deg2Rad(objReader("longitude")), Deg2Rad(objReader("latitude")), Deg2Rad(sglMinLat), 3959, Deg2Rad(90), ddlDistance.SelectedValue))
			
			'report starting point details to lblStatus
			Dim strOut As String
			strOut = "ZIP Code " & tbZip.Text & " is assigned to " & objReader("cityname") & ", " & objReader("statecode") & ".<br />"
			strOut &= "It is located at latitude " & objReader("latitude") & ", longitude " & objReader("longitude") & ".<br /><br />"
			strOut &= "At a distance of " & ddlDistance.SelectedValue & " miles, the search box coordinates are:<br />"
			strOut &= "Maximum latitude (North): " & sglMaxLat & "<br />"
			strOut &= "Miniumum latitude (South): " & sglMinLat & "<br />"
			strOut &= "Maximum longitude (East): " & sglMaxLon & "<br />"
			strOut &= "Minimum longitude (West): " & sglMinLon & "<br />"
			lblStatus.Text = strOut

			'populate gridview
			PopulateGridView(sglMinLat, sglMaxLat, sglMinLon, sglMaxLon, objReader("latitude"), objReader("longitude"))
		Else
			'starting point not found
			lblStatus.Text = "Error retrieving initial ZIP Code coordinates: No record found for " & tbZip.Text & "."
		End If
		objConn.Close()
		objCmd.Dispose()
		objConn.Dispose()
	Catch ex As Exception
		'technical problem running the query
		lblStatus.Text = "Error executing database query for initial coordinates: " & ex.Message
	End Try
End Sub

Step 5: Bind The GridView

Lastly, we need to create a stored procedure that will accept the coordinates for our search box, and return all coordinates that fall within that box.

distance = acos(sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1)) * r

CREATE PROCEDURE [dbo].[sp_get_zips_in_radius]

@maxlat DECIMAL(9, 6),
@minlat DECIMAL(9, 6),
@maxlon DECIMAL(9, 6),
@minlon DECIMAL(9, 6),
@startlat DECIMAL(9, 6),
@startlon DECIMAL(9, 6),
@radius INT

AS
SELECT zipcode, statecode, latitude, longitude, cityname, 
ROUND(ACOS(SIN(RADIANS(@startlat)) * SIN(RADIANS(latitude)) + COS(RADIANS(@startlat)) * COS(RADIANS(latitude)) * COS(RADIANS(longitude) - RADIANS(@startlon))) * @radius, 2) AS distance
FROM zip_codes
WHERE latitude < @maxlat AND latitude > @minlat AND longitude < @maxlon AND longitude > @minlon 
ORDER BY distance, zipcode, statecode, cityname

Now that we have the stored procedure ready to go, we just need to update the values of our SqlDataSource’s SelectParameters, then bind the GridView:

Sub PopulateGridView(ByVal sglMinLat As Single, ByVal sglMaxLat As Single, ByVal sglMinLon As Single, ByVal sglMaxLon As Single, ByVal sglStartLat As Single, ByVal sglStartLon As Single)
	sqlZip.SelectParameters("minlat").DefaultValue = sglMinLat
	sqlZip.SelectParameters("maxlat").DefaultValue = sglMaxLat
	sqlZip.SelectParameters("minlon").DefaultValue = sglMinLon
	sqlZip.SelectParameters("maxlon").DefaultValue = sglMaxLon
	sqlZip.SelectParameters("startlat").DefaultValue = sglStartLat
	sqlZip.SelectParameters("startlon").DefaultValue = sglStartLon

	gvZIP.DataBind()
End Sub

And that’s all there is to it. You can see a working demo here: http://dougv.net/demos/zip_code_distance

You can download the code here: https://www.dougv.com/wp-content/uploads/2012/04/zip_code_distance.zip

All links in this post on delicious: http://delicious.com/dougvdotcom/getting-all-zip-codes-in-a-given-radius-from-a-known-point-zip-code-via-asp-net

Asked recently on Formspring:

how to increment count in database on clicking radio button

There are a few ways to go about this. I’ll demonstrate two: a traditional, PHP / MySQL only, postback approach, and a jQuery version that uses AJAX to asynchronously record and update the counts.

Just to be clear: In order to complete this solution, we have to use both JavaScript and a server-side scripting language. We use JavaScript to intercept the user clicking the radio button, but process the fact that the button was clicked on the server.

Also, for the purpose of this tutorial, I’ll assume that the radio button involved is part of a group. That is, we have several radio buttons, all with the same name, but different values, e.g.:

<form id="myform" name="myform" method="post">
	<p>Select a color:</p>
	<label id="l_red"><input type="radio" id="r_red" name="color_name" value="red" />Red</label> (<label id="c_red">0</label>) | 
	<label id="l_green"><input type="radio" id="r_green" name="color_name" value="green" />Green</label> (<label id="c_green">0</label>) | 
	<label id="l_blue"><input type="radio" id="r_blue" name="color_name" value="blue" />Blue</label> (<label id="c_blue">0</label>) | 
	<label id="l_black"><input type="radio" id="r_black" name="color_name" value="black" />Black</label> (<label id="c_black">0</label>)
</form>

Example 1: PHP / MySQL Postback

The easiest way to process a click is right on the page that contains the radio button, via a simple postback.

We begin with a MySQL table that will hold our count. It has two columns: color_name, which will contain unique values and thus can act as our table’s primary key; and color_count, the number of times that color has been clicked.

CREATE TABLE IF NOT EXISTS `colorcounter` (
  `colorname` varchar(5) NOT NULL,
  `colorcount` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`colorname`)
)

Next, we query the database for the current counts, which we’ll display in our form, later. We’ll dump the results set into an associative array, which will make outputting the counts in our form a little easier.

$link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server');
mysql_select_db('database') or die('Cannot select database');

// click-count increment routine will go here; more on that shortly ...

// get current color click counts
$rs = mysql_query("SELECT * FROM colorcounter") or die ('Cannot process SQL count totals query');
if(mysql_num_rows($rs) > 0) {
	while($row = mysql_fetch_array($rs)) {
		$count[$row['colorname']] = $row['colorcount'];
	}
}

We now need to set up our form to be ready to process the user click, and to display the click count records we previously received.

• We set the form to submit to itself.
• We add an onclick event to each radio button, instructing it to submit the form. (The logic that will process the click follows shortly; we’re just assuming that every time the form is submitted, we need to increment a counter.)
• We display the current click counts after each radio button.

<form id="myform" name="myform" method="post" action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']); ?>">
	<p>Select a color:</p>
	<label id="l_red"><input type="radio" id="r_red" name="color_name" value="red" onclick="this.form.submit();" />Red</label> (<label id="c_red"><?php echo $count['red']; ?></label>) | 
	<label id="l_green"><input type="radio" id="r_green" name="color_name" value="green" onclick="this.form.submit();" />Green</label> (<label id="c_green"><?php echo $count['green']; ?></label>) | 
	<label id="l_blue"><input type="radio" id="r_blue" name="color_name" value="blue" onclick="this.form.submit();" />Blue</label> (<label id="c_blue"><?php echo $count['blue']; ?></label>) | 
	<label id="l_black"><input type="radio" id="r_black" name="color_name" value="black" onclick="this.form.submit();" />Black</label> (<label id="c_black"><?php echo $count['black']; ?></label>)
</form>

Finally, we need to add some code to the page that will see if the form has been submitted, and increment the appropriate count.

// if this is a postback ...
if(isset($_POST['color_name'])) {
	// create array of acceptable values
	$ok = array('red', 'green', 'blue', 'black');
	// if we have an acceptable value for color_name ...
	if(in_array($_POST['colorname'], $ok)) {
		// update the counter for that color
		$q = mysql_query("UPDATE colorcounter SET colorcount = colorcount + 1 WHERE colorname = '" . $_POST['color_name'] . "'") or die ("Error updating count for " . $_POST['color_name']);
	}
}

Note that this code block should appear before the code block that gets the current click counts (where I included the comment in the first block of PHP code), in order to ensure the counts are up-to-the-minute.

You can see a working demo here: http://www.dougv.com/demo/ajax_radio_clickcount/index.php

Example 2: Using AJAX

The previous example has a number of drawbacks, not the least of them being that each time you click on a radio button, the entire form posts back to the server.

That limits its usefulness quite a bit. We could work around this problem a few ways; perhaps making sure we repopulate our form fields following the postback, and only performing final processing when the submit button is clicked (by adding an onclick event to that button, which hands off the results to a JavaScript function or a different processing page).

That’s an awful lot of work for very little benefit. It makes way more sense to leverage AJAX, and update our click counts without having to post the form itself back to the server.

JavaScript — more specifically, jQuery — to the rescue!

To get our jQuery solution to work, we need to create two PHP “helper” pages: one that will update our click counts whenever a radio button is selected, and the other that will get the current click counts, both on initial page load and following a click.

The get_counts.php Helper Page

Let’s start by calling the helper page that gets our click counts get_counts.php. It works almost exactly like the code block that gets the counts in our PHP postback example, except this page is going to output our results in JSON.

We’re putting the results out that way because it’s very easy for jQuery to process data in JSON format.

Let’s look page will expect to see a querystring variable named “color,” which indicates which value needs to be incremented. The page code itself will be very similar to the processing code in our postback version.

$link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server');
mysql_select_db('database') or die('Cannot select database');

// get new count totals, pass as JSON
$rs = mysql_query("SELECT * FROM colorcounter") or die('Cannot get updated click counts');
if(mysql_num_rows($rs) > 0) {
	$out = "{ ";
	while($row = mysql_fetch_array($rs)) {
		$out .= "\"$row[colorname]\" : $row[colorcount], ";
	}
	$out = substr($out, 0, strlen($out) - 2);
	$out .= " }";
	
	header("Content-type: application/json");
	echo $out;
}

The increment_counter.php Helper Page

Now that we have a way to get the counts, we need a way to increment them. Once again, we make a PHP helper page — increment_counter.php — that works fundamentally the same as the code block that increments the click counts in our PHP postback page, except that it calls upon GET, instead of POST.

(Yes, you can use jQuery to make POST and GET AJAX requests. When I’m sending a single variable that just needs to meet a range, I prefer to use GET.)

$link = mysql_connect('server', 'user', 'password') or die('Cannot connect to database server');
mysql_select_db('database') or die('Cannot select database');

// if this is a postback ...
if(isset($_GET['color'])) {
	// create array of acceptable values
	$ok = array('red', 'green', 'blue', 'black');
	// if we have an acceptable value for color_name ...
	if(in_array($_GET['color'], $ok)) {
		// update the counter for that color
		$q = mysql_query("UPDATE colorcounter SET colorcount = colorcount + 1 WHERE colorname = '" . $_GET['color'] . "'") or die ("Error updating count for " . $_GET['color']);
	}
}

The Form And JavaScript

With our helper pages out of the way, we can proceed to work on the form and the jQuery JavaScript needed to make this work.

As usual, our first step is to procure and include a copy of jQuery on our page. Lately, I prefer to simply link to the version hosted by the Google Libraries API, but you can download jQuery directly and run it off your server if you prefer. Either way, call it somewhere in your page’s head section:

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>

Next, I want to strip the form back to its basic components.

<form id="myform" name="myform" method="post">
	<p>Select a color:</p>
	<label id="l_red"><input type="radio" id="r_red" name="color_name" value="red" />Red</label> (<label id="c_red">0</label>) | 
	<label id="l_green"><input type="radio" id="r_green" name="color_name" value="green" />Green</label> (<label id="c_green">0</label>) | 
	<label id="l_blue"><input type="radio" id="r_blue" name="color_name" value="blue" />Blue</label> (<label id="c_blue">0</label>) | 
	<label id="l_black"><input type="radio" id="r_black" name="color_name" value="black" />Black</label> (<label id="c_black">0</label>)
</form>

Notice that I no longer have onclick events assigned to each radio button. That’s because I’ll wire up that event via $(document).ready.

More on that shortly. First, I want to declare a global variable, lastChecked. It will keep track of the value of the last radio button that was checked.

This will keep our page from recording multiple clicks on the same radio button, over and over again; clicks won’t be recorded unless, and until, a new value is clicked. In other words, if I just keep clicking Red, it will only be recorded once; if I then click Blue, that will be incremented, and clicking Red again will increment its value, once again.

var lastClicked = '';

I next want to write JavaScript functions for each of my helper pages. The first, getTotals(), will — as its name suggests — send an AJAX request to get the current click totals. It will then assign those totals to the labels after each radio button.

function getTotals() {
	// function to get click counts as JSON from helper page
	// expects get_count.php to be in same directory level
	
	$.ajax({
		type: "GET",
		url: "get_count.php",
		dataType: "json",
		error: function(xhr, status, msg) {
			alert("Failed to get click counts: " + msg);
		}
	})
	.done(function(data) {
		// loop through JSON variables, assign to count labels
		$.each(data, function(key, value) {
			var tmp = "#c_" + key;
			$(tmp).text(value);
		});
	});
}

I can now write the JavaScript function that will increment click counts. It’s named, quite cleverly, processClick, and takes as its argument the radio button (obj, for “object”) that was clicked.

function processClick(obj) {
	// function to increment click count via ajax
	// expects increment_count.php to be in same directory level

	if(lastClicked != obj.val()) { // don't count clicks on currently active radio button
		lastClicked = obj.val(); // set currently clicked radio button to this one
		
		var qs = "color=" + obj.val(); // set query string value
		
		$.ajax({
			type: "GET",
			url: "increment_count.php",
			data: qs,
			error: function(xhr, status, msg) {
				alert("Failed to process click count: " + msg);
			}
		})
		.done(function() {
			getTotals(); // update totals on successful processing
		});
	}
}

Finally, we want to use $(document).ready to initially populate the click total labels, and to assign processClick as the event handler for clicks on all the radio buttons on the page.

$(document).ready(function() {
	getTotals(); // get click totals on initial page load
	
	$(document).ready(function() {
		// add click incrementing event handler to all radio buttons on page
		$('input:radio').click(function() {
			processClick($(this));
		});
	});
});

You can see a working example here: http://www.dougv.com/demo/ajax_radio_clickcount/example2.htm

Notes And Code

A few notes about this project:

It’s not overly difficult for someone to click spam what I have here, either by simply clicking a radio button multiple times, or by calling the increment_count.php helper page multiple times.

A way around that would be to create single-use tokens for each iteration of the form, so that only one click per rendering of the form would be allowed.

You could do that by creating a session variable or cookie, if you’re using the PHP postback example; setting a Boolean in that session / cookie to be true the first time the form is submitted; and refusing to process the click if that session / cookie Boolean is true.

Or, for more security, you could add another MySQL table, which counts the number of times your form has been rendered. It would consist of an autoincremented primary key, which would act as an identifier for this instance of the form; and an tinyint column for recording a Boolean, which would indicate if the form had been submitted.

Every time your form is rendered, you would insert a new record in your form-rendering table, with a Boolean value of false indicating that the form has not yet been processed, then use mysql_insert_id to get the primary key value of the record you just created.

Using mcrypt, you would then create a single-use token by encrypting that record ID with a secret key only you know, and pass that to whatever method you are using to increment the counter.

That processing code would decrypt the token, make sure the record with that ID has a Boolean false as its “form has been submitted” value. If not, the code would not process the click; but if so, the code would process the click, then mark the form’s Boolean value as true.

An added benefit of this method is that it also inhibits forgeries and cross-site scripting attacks, since the only way to create legitimate tokens is via your secret key. (Theoretically, a hacker could crack your key, but use a sufficient passphrase and a good crypto algorithm, and the chances of anyone even taking a crack at it, nonetheless succeeding, are infinitesimal.)

Sounds more complex than it is. I’ve got demonstrating how to make a single-use token like this in Evernote blog column ideas. If significant interest is expressed, I’ll move it to the top of the list.

You can download the code in this post here: https://www.dougv.com/wp-content/uploads/2012/04/ajax_radio_clickcount.zip

All links in this post on delicious: http://delicious.com/dougvdotcom/how-to-increment-a-counter-in-mysql-based-on-a-radio-button-click