Category Archives: SQL

Discussions about SQL go here.

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

Getting Pseudo Random Records From A MySQL Database Table

Here’s a common programming problem: You have some records in a MySQL table. You want to randomly select a record, or just a few records, from that database; maybe, you want to pass some additional, specific criteria to narrow the candidate records somewhat.

For example, maybe you run a music Web site. You have a database table full of albums, and you want to randomly pick one as “album of the moment.” Perhaps you want to select from a category — rock, country, jazz, etc. Or maybe you want to get an assortment of albums from a specific artist, and display many records on a Web page.

Getting such “random” database records is easily done in MySQL (versions 3.23 and up) via a fairly standard SELECT statement, using ORDER BY, RAND and LIMIT.

(For the purpose of this tutorial, I’ll use the ZIP Code database I got for Getting All ZIP Codes In A Given Radius From A Known Point / ZIP Code Via PHP And MySQL, which I wrote some time ago.)

First, let’s simply select any old record from the table; that is, every record in the table is equally eligible for selection.

SELECT * FROM table_name ORDER BY RAND() LIMIT 1;

What this does is tell MySQL, “Toss all the records together in a pile [ORDER BY RAND()], then give me just one record from that pile [LIMIT 1].”

You can see this in action here: http://www.dougv.com/demo/mysql_random_records/index.php

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