Coffee on the Keyboard

Most SQL-injection articles set a horrible example for young programmers.

Here is a very typical “bad example” of why you need to escape user data before it goes into SQL queries:

(ed. The symbol « is a line break that’s not in the real code.)

The point, of course, is that you must sanitize your user input, or else this person would run this query:

Which grants the sneaky user all your admin privileges. Other versions have nefarious users dropping your users or articles tables.

The problem is: this is the wrong way to authenticate users. These examples are written for beginners to understand the importance of sanitizing input, but they also provide a model to those beginners for how user authentication works. And it’s a very bad model.

This is a long one, more after the break. Continue reading

I know I will be accosted for this, but at work we needed to run PHP on IIS 6 (fairly simple) and connect it to a remote database server running SQL Server 2005 (not terrible, once I gave up the Microsoft way).

Yeah yeah, do it in ASP.NET, I know. While I like C# as a language, I kind of hate ASP.NET as a framework, so what are you gonna do? Java was an option but the start-up time was too long for this project.

My first Google search for “PHP SQL Server 2005″ turned up the Microsoft SQL Server 2005 Driver for PHP. “Well great!” I thought. It’s just a PHP extension, very easy to install on Windows. But I didn’t know the horrid depths into which I was about to sink.

The Microsoft driver comes with an example application and database. The application assumes you are connecting to a local database. There is scant information about remote databases.

The driver defines this function:

sqlsrv_connect($host[, $connectionOptions[, ...]]);

The example application tells you to set $host to (local). Supposedly this works. However, after scouring the internet for several days, and trying every permutation of hostname, Windows networking name, port, IP address, white space, and several other variables that shouldn’t have been in there, I’ve decided it doesn’t talk to remote servers nicely.

PDO’s ODBC driver, on the other hand, and a quick visit to www.connectionstrings.com, worked wonderfully.

Here is how I needed to create the PDO object. I hope this is useful for someone else:

(ed. The symbol « is a line break that’s not in the real code.)

$host     = '1.2.3.4';
$port     = '1433';
$database = 'MyDatabase';
$user     = 'MyDatabaseUser';
$password = 'MyDatabasePassword';

$dsn = "odbc:DRIVER={SQL Server}; «
 SERVER=$server,$port;DATABASE=$database";

try {
  // connect
  $conn = new PDO($dsn,$user,$password);
} catch (PDOException $e) {
  // fancy error handling
}

Today I upgraded from WordPress 2.3.3 to 2.6.1. I’m such a late adopter sometimes.

I had to go through and repeat a few hacks. For example, 2.3.x didn’t allow you to do get_sidebar($name), so I’d hacked the “get_sidebar()” function. And I replaced the still-broken Atom feed reading widget with James Wilson’s Google Reader Widget.

Then I finally got fed up with the default “Search” widget, which doesn’t look like the other widgets at all (no title), so I started hacking into that one. Then I realized “why hack, when I can extend?”

So, here it is, Better Search Widget.

All it does is add a search widget with a customizable title, submit button, and field size. Quick-and-useful. You can see the results in the sidebar.

If you decide to use it, leave a comment and I’ll check out your blog.

I’ve been reading Eran Hammer-Lahav’s intelligent posts on microblog scalability, and now I’m concerned about my own “microblog” site, Picofiction.

Similar to social networks, social updates, social messaging, social… Like many social web sites—amongst our weaponry…—Picofiction lets you “follow” your favorite authors, displaying all their posts along with yours.

I handle this very naïvely: everything is offloaded to the database. There are three tables involved here, one of users, one of posts, and one of follower/followee bindings.

Here’s the basic structure of this query:

SELECT post_id, post_body, post_date, post_type,
  user_name AS author_name, user_id AS author_id
FROM posts
LEFT JOIN users
ON posts.author_id = users.user_id
WHERE author_id = 'CURRENT_USER'
OR author_id IN (
  (SELECT followed_id
   FROM followers
   WHERE following_id = 'CURRENT_USER')
  )
ORDER BY post_date DESC
LIMIT PAGE_START,20;

Here’s where I need help: this works great on a single database, but it does not scale horizontally.

Since this horizontal scalability is such a hot topic right now, I’m asking for ideas. I’d like to put in the infrastructure before there is a need for it.

Eran points out that caching is not as simple a solution as we’d like to think. What do you cache? How do you keep caches in sync?

Does anyone have experience with MySQL Cluster Servers? It seems like the best way of scaling is to make the process as parallelizable as possible. The database then handles the parallelization, so the less I can do in the program the better, right?