Category Archives: Transact-SQL

Microsoft’s Advice On Avoiding SQL Injection Attacks

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.

Continue reading

Getting All ZIP Codes In A Given Radius From A Known Point / ZIP Code Via ASP.NET

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.

Continue reading

Parent-Child DropDownList Controls In ASP.NET Web Forms (VB.NET)

Some time ago I promised a formspring anon I would do a tutorial on parent / child DropDownList controls in ASP.NET Web Forms. At long last, I’m delivering. (From here on out, I will use “DropDownList” and “select list” interchangeably.)

Parent-child DropDownList controls means you have a parent, or main / master select list. Based on whatever selection is made in that first DropDownList, a second, “child” or “detail” select list is populated with relevant results.For example, if we had a database of cars, we might have a parent (master) DropDownList of manufacturers — Ford, Chevy, Toyota, etc. — and populate the child DropDownList with models from the selected manufacturer. So, if someone chose Ford in the parent select list, the child select list would automatically populate with choices such as Fusion, Mustang, Explorer, F-150, etc.

I’ll be demonstrating two different ways to accomplish this task, as well as a few variations on the first: First, binding with only SqlDataSource controls, which is by far the easiest way to proceed and will suffice for about 90 percent of applications; second, using code behind and CompareValidator controls, to cover all possible roadblocks.

Some notes before we proceed:

  • I’ll use the ZIP Code database I’ve been using for a while as the back-end data. I also have a separate table of USPS state codes I’m using as the parent data, to help illustrate that you can use several tables / data sources to fuel this solution, provided the keys that relate the data make sense to each data store. In other words, you can use a lot of different sources for your data, so long as the data is relational.
  • I’m going to use SQL Server, stored procedures and a connection string stored in my web.config file to access my data. I recognize that some people prefer to use SQL statements / LINQ and the like, or can only use Access / MySQL or other data stores. I really urge you to always use stored procedures, as they are way safer than inline SQL statements. As far as alternative data stores go, that should be a easy a fix as switching your code to use to the relevant ADO.NET ODBC classes, rather than the SQL Client classes.
  • This solution should work for ASP.NET versions 2.0 forward.

Continue reading

New England GiveCamp 2011: What A Weekend!

Last weekend was New England GiveCamp 2011, in which 100+ developers, designers and other volunteers gathered to donate time and skills to some 30 charities who needed IT help.

This year, I was project lead for Alex’s Team Foundation, based in Andover, Mass. Our team was Saurabh Moondhra and William Wade, both experienced ASP.NET developers.

Alex’s Team Foundation, named after 16-year-old Alex Miliotis, who passed away from leukemia in 2002, raises money to support nurses and other oncology professionals, and supports youth sports. The foundation is largely the labors of Patti Rae Miliotis, Alex’s mother, and a handful of reliable volunteers. Like every small nonprofit, Alex’s Team doesn’t have a lot of money.

Like every other leader of a small nonprofit, Patti is pulled in a lot of different directions and has all she can do to keep track of the people with whom she comes in contact, nonetheless all the donations she gets. Patti also hosts a few events every year. She basically needs a way to keep track of who attends those events or otherwise supports her organization, and to mail merge thank-you notes.

So that was the project I led this weekend: Converting a bunch of data stored in (of course!) Excel spreadsheets into a more relational database, with the ability to export that data in order to mail merge thank-you and fundraising letters.

Continue reading

Automatically Hash Tagging Text With ASP.NET Web Forms (VB.NET)

I had previously blogged a solution in PHP to automatically hash tag an input string with various terms stored in a database. Here’s an ASP.NET Web Forms version of the same solution (this one should work for ASP.NET 2, 3.5 and 4).

To review, a hash tag is a bit of text, led with a hash mark (#), that serves to indicate to some Web sites / services — notably, Twitter — that the word thus marked should be treated as a tag. This code will take some piece of input text, search it for terms we generally want to tag, and mark the instances in that input string with hash tags.

As in the previous solution, we’ll define a “word” for the purposes of this demo to be any alphanumeric character sequence that is followed by a space or a newline. Also, this demo will only tag text; it won’t automatically add new terms to the database. That will be the subject of an upcoming post.

I’m going to use three ArrayLists as the workhorses for this solution. One will hold the terms from the database; the second will contain all the distinct words in the input string; and the third, the words from the input string that are hashtag terms.
Continue reading